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:09] els |
dev:sql:functions:distance [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 3: | Line 3: | ||
| ====== Omnidex SQL Function: $DISTANCE ====== | ====== Omnidex SQL Function: $DISTANCE ====== | ||
| ===== Description ===== | ===== Description ===== | ||
| - | The $DISTANCE function calculates the distance between one set of geographic coordinates and one or more other geographic coordinates. This is used for performing geographic radius searches, such as finding all rows within five miles of a given location. This can also be used to simply calculate the distance between two points. The following [[admin:optimization:geo:home|article]] provides more detail on performing geographic searches in Omnidex. | ||
| + | The $DISTANCE function calculates the distance between one set of geographic coordinates and one or more other geographic coordinates. This is used for performing geographic radius searches, such as finding all rows within five miles of a given location. This can also be used to simply calculate the distance between two points. The following [[admin:features:geo:home|article]] provides more detail on performing geographic searches in Omnidex. | ||
| + | |||
| + | This function returns a DOUBLE datatype. | ||
| ===== Syntax ===== | ===== Syntax ===== | ||
| - | $DISTANCE(origin_latitude, orgin_longitude, destination_latitude, destination_longitude [, options]) | + | 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_select_statement), (destination_select_statement) [, options]) | + | $DISTANCE( origin_latitude, origin_longitude, |
| + | destination_latitude, destination_longitude | ||
| + | [, options]) | ||
| + | $DISTANCE( origin_latitude, origin_longitude, | ||
| + | (destination_select_statement) | ||
| + | [, options]) | ||
| - | == expression == | + | == origin_latitude == |
| - | Required. An expression using SQL language that produces a value. A scalar expression is generally equivalent to a select item in the SQL SELECT statement, and can be a character expression, numeric expression or date expression. | + | 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. |
| - | == datatype == | + | == origin_longitude == |
| - | Required. One of the datatypes listed below: | + | |
| - | {{page>dev:appendix:reference:datatypes_insert&noheader&nofooter&noeditbtn}} | + | 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. |
| - | Omnidex also provides partial support for National Character Datatypes (sometimes called Unicode datatypes or wide characters). The Omnidex SQL Engine will process these datatypes, but the Omnidex Indexing Engine will only index the lower 8 bits of these datatypes. | ||
| - | {{page>dev:appendix:reference:natl_datatypes_insert&noheader&nofooter&noeditbtn}} | + | == destination_latitude == |
| - | == length == | + | 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. |
| - | Optional. The length of the datatype is usually specified in the //datatype// parameter; however, if it has not been specified, it may be specified here. If the length is specified in the //datatype// parameter, this parameter is left empty. | + | |
| + | == 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 STRING datatype. | ||
| == options == | == options == | ||
| - | Optional. Options can be applied to the data conversion to affect the output. The supported options are: | + | Optional. One of the options listed below: |
| - | ^Option ^Description ^ | + | ^Option ^Description ^ |
| - | |LJ |Left-justify | | + | |MILES |Return the distance measured in miles (default) | |
| - | |RJ |Right-justify | | + | |NAUTICALMILES |Return the distance measured in nautical miles | |
| - | |UPS |Upshift | | + | |KILOMETERS |Return the distance measured in kilometers | |
| - | |DNS |Downshift | | + | |YARDS |Return the distance measured in yards | |
| - | |SF |Space-fill | | + | |FEET |Return the distance measured in feet | |
| - | |ZF |Zero-fill | | + | |METERS |Return the distance measured in meters | |
| - | |BWZ |Blank when zero | | + | |LAT_HEMISPHERE=<N %%|%% S> |Treat latitudes as in the named hemisphere | |
| + | |LONG_HEMISPHERE=<E %%|%% W> |Treat longitudes as in the named hemisphere | | ||
| + | == destination_select_statement == | ||
| + | |||
| + | Optional. A select statement that returns one or more rows containing first the latitude and then the longitude in two consecutive select-items. These pairs of coordinates represent the destination(s) of the search. Since //destination_select_statement// can return multiple rows, this allows geographic radius searches requesting rows within a certain distance from a number of locations, such as stores or branch offices. Note that for performance reasons, this select statement may not return more than 1,000 rows. | ||
| ===== Example ===== | ===== Example ===== | ||
| Line 48: | 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 55: | 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 86: | 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}} | ||