This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
dev:sql:functions:distance [2010/07/28 18:35] els |
dev:sql:functions:distance [2012/10/26 14:57] (current) |
||
|---|---|---|---|
| Line 9: | Line 9: | ||
| ===== Syntax ===== | ===== Syntax ===== | ||
| + | |||
| + | The $DISTANCE function supports two syntaxes. The first syntax allows latitudes and longitudes to be passed independently, and the second syntax allows one set of coordinates to be passed from a SQL statement. | ||
| $DISTANCE( origin_latitude, origin_longitude, | $DISTANCE( origin_latitude, origin_longitude, | ||
| Line 14: | Line 16: | ||
| [, options]) | [, options]) | ||
| - | $DISTANCE( (origin_select_statement), | + | $DISTANCE( origin_latitude, origin_longitude, |
| (destination_select_statement) | (destination_select_statement) | ||
| [, options]) | [, options]) | ||
| Line 21: | Line 23: | ||
| == origin_latitude == | == origin_latitude == | ||
| - | Required. An expression containing the latitude used as the origin of this geographic search. If //origin_latitude// produces a literal string, then that origin will be used for all rows. If //origin_latitude// is a column, then the origin latitude will be treated as that value for each row processed. //Origin_latitude// can also be a select statement, enclosed in parentheses, that retrieves a latitude in a single column and a single row. Latitudes can be expressed using a FLOAT, DOUBLE, CHARACTER or C STRING datatype. | + | Required. An expression containing the latitude used as the origin of this geographic search. If //origin_latitude// produces a literal string, then that origin will be used for all rows. If //origin_latitude// is a column, then the origin latitude will be treated as that value for each row processed. //Origin_latitude// can also be a select statement, enclosed in parentheses, that retrieves a latitude in a single column and a single row. Latitudes can be expressed using a FLOAT, DOUBLE, CHARACTER or STRING datatype. |
| == origin_longitude == | == origin_longitude == | ||
| - | Required. An expression containing the longitude used as the origin of this geographic search. If //origin_longitude// produces a literal string, then that origin will be used for all rows. If //origin_longitude// is a column, then the origin longitude will be treated as that value for each row processed. //Origin_longitude// can also be a select statement, enclosed in parentheses, that retrieves a longitude in a single column and a single row. Longitudes can be expressed using a FLOAT, DOUBLE, CHARACTER or C STRING datatype. | + | Required. An expression containing the longitude used as the origin of this geographic search. If //origin_longitude// produces a literal string, then that origin will be used for all rows. If //origin_longitude// is a column, then the origin longitude will be treated as that value for each row processed. //Origin_longitude// can also be a select statement, enclosed in parentheses, that retrieves a longitude in a single column and a single row. Longitudes can be expressed using a FLOAT, DOUBLE, CHARACTER or STRING datatype. |
| == destination_latitude == | == destination_latitude == | ||
| - | Required. An expression containing the latitude used as the destination of this geographic search. If //destination_latitude// produces a literal string, then that destination will be used for all rows. If //destination_latitude// is a column, then the destination latitude will be treated as that value for each row processed. //Origin_latitude// can also be a select statement, enclosed in parentheses, that retrieves a latitude in a single column and a single row. Latitudes can be expressed using a FLOAT, DOUBLE, CHARACTER or C STRING datatype. | + | Required. An expression containing the latitude used as the destination of this geographic search. If //destination_latitude// produces a literal string, then that destination will be used for all rows. If //destination_latitude// is a column, then the destination latitude will be treated as that value for each row processed. //Origin_latitude// can also be a select statement, enclosed in parentheses, that retrieves a latitude in a single column and a single row. Latitudes can be expressed using a FLOAT, DOUBLE, CHARACTER or STRING datatype. |
| == destination_longitude == | == destination_longitude == | ||
| - | Required. An expression containing the longitude used as the destination of this geographic search. If //destination_longitude// produces a literal string, then that destination will be used for all rows. If //destination_longitude// is a column, then the destination longitude will be treated as that value for each row processed. //Origin_longitude// can also be a select statement, enclosed in parentheses, that retrieves a longitude in a single column and a single row. Longitudes can be expressed using a FLOAT, DOUBLE, CHARACTER or C STRING datatype. | + | Required. An expression containing the longitude used as the destination of this geographic search. If //destination_longitude// produces a literal string, then that destination will be used for all rows. If //destination_longitude// is a column, then the destination longitude will be treated as that value for each row processed. //Origin_longitude// can also be a select statement, enclosed in parentheses, that retrieves a longitude in a single column and a single row. Longitudes can be expressed using a FLOAT, DOUBLE, CHARACTER or STRING datatype. |
| == options == | == options == | ||
| Line 47: | Line 49: | ||
| |FEET |Return the distance measured in feet | | |FEET |Return the distance measured in feet | | ||
| |METERS |Return the distance measured in meters | | |METERS |Return the distance measured in meters | | ||
| - | |LAT_HEMISPHERE=<N | S> |Treat latitudes as in the named hemisphere | | + | |LAT_HEMISPHERE=<N %%|%% S> |Treat latitudes as in the named hemisphere | |
| - | |LONG_HEMISPHERE=<E | W> |Treat longitudes as in the named hemisphere | | + | |LONG_HEMISPHERE=<E %%|%% W> |Treat longitudes as in the named hemisphere | |
| - | + | ||
| - | == origin_select_statement == | + | |
| - | + | ||
| - | Optional. A select statement that returns a single row containing first the latitude and then the longitude in two consecutive select-items. These pairs of coordinates represent the origin on the search. | + | |
| == destination_select_statement == | == destination_select_statement == | ||
| Line 62: | Line 60: | ||
| <code> | <code> | ||
| - | select $distance(40.0, -105.0, 41.0, -106.0) | + | > select $distance(40.0, -105.0, 41.0, -106.0) distance |
| - | from $omnidex | + | >> from $omnidex; |
| + | |||
| + | DISTANCE | ||
| + | -------------------------------- | ||
| + | 86.798584 | ||
| </code> | </code> | ||
| Line 69: | Line 71: | ||
| <code> | <code> | ||
| - | select $distance(40.0, -105.0, latitude, longitude), | + | select zip, |
| + | $distance(40.0, -105.0, latitude, longitude) distance, | ||
| latitude, longitude, | latitude, longitude, | ||
| - | from geo | + | from zipcodes |
| - | where $distance(40.0, -105.0, latitude, longitude) < 10 | + | where $distance(40.0, -105.0, latitude, longitude) < 10; |
| - | </code> | + | |
| - | ==== Example 3: Distance between two sets of coordinates using a subquery ==== | + | ZIP DISTANCE LATITUDE LONGITUDE |
| + | --------- -------------------------------- --------- --------- | ||
| + | 80020 6.152414 39.9263 -105.0652 | ||
| + | 80026 5.205616 39.9976 -105.0983 | ||
| + | 80027 7.884606 39.9782 -105.1462 | ||
| + | 80028 7.123061 39.9778 -105.1314 | ||
| + | 80038 7.133593 39.9206 -105.0861 | ||
| + | 80233 7.277603 39.9004 -104.9553 | ||
| + | 80234 6.909988 39.9001 -105.0061 | ||
| + | 80241 5.813214 39.9275 -104.9443 | ||
| + | 80260 5.813214 39.9275 -104.9443 | ||
| + | 80514 6.954636 40.0853 -104.9302 | ||
| + | 80520 8.475827 40.1125 -104.9361 | ||
| + | 80530 7.628036 40.0992 -104.9367 | ||
| + | 80602 9.650129 39.9886 -104.8183 | ||
| + | 80603 9.650129 39.9886 -104.8183 | ||
| + | 80614 5.652985 39.9239 -104.9608 | ||
| + | 80640 9.788716 39.8830 -104.8958 | ||
| - | <code> | ||
| - | select latitude, longitude, | ||
| - | from geo | ||
| - | where $distance((select latitude, longitude | ||
| - | from zipcodes | ||
| - | where zip = ‘80301’), | ||
| - | latitude, longitude) < 10 | ||
| </code> | </code> | ||
| - | ==== Example 4: Distance between multiple sets of coordinates using a subquery ==== | + | ==== Example 3: Distance between an origin and multiple sets of destination coordinates ==== |
| <code> | <code> | ||
| - | select latitude, longitude, | + | > select state, zip, latitude, longitude |
| - | from geo | + | >> from zipcodes |
| - | where $distance((select latitude, longitude | + | >> where $distance(latitude, longitude, |
| - | from zipcodes | + | >> (select latitude, longitude |
| - | where region = 'MT'), | + | >> from zipcodes |
| - | latitude, longitude) < 10 | + | >> where state = 'CO')) < 5; |
| + | |||
| + | ST ZIP LATITUDE LONGITUDE | ||
| + | -- --------- --------- --------- | ||
| + | CO 80001 39.8028 -105.0869 | ||
| + | CO 80002 39.7959 -105.0982 | ||
| + | CO 80003 39.8280 -105.0653 | ||
| + | CO 80004 39.8136 -105.1178 | ||
| + | CO 80005 39.8409 -105.1108 | ||
| + | CO 80006 39.8028 -105.0869 | ||
| + | CO 80007 39.8029 -105.0876 | ||
| + | CO 80010 39.7373 -104.8638 | ||
| + | CO 80011 39.7377 -104.8121 | ||
| + | ... | ||
| </code> | </code> | ||
| Line 100: | Line 125: | ||
| See also: | See also: | ||
| + | * [[admin:optimization:geo:home|Article: Geographic Searches]] | ||
| {{page>dev:sql:functions:see_also_utility&noheader&nofooter&noeditbtn}} | {{page>dev:sql:functions:see_also_utility&noheader&nofooter&noeditbtn}} | ||