This is an old revision of the document!
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 article provides more detail on performing geographic searches in Omnidex.
This function returns a DOUBLE datatype.
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,
destination_latitude, destination_longitude
[, options])
$DISTANCE( origin_latitude, origin_longitude,
(destination_select_statement)
[, options])
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.
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.
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.
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.
Optional. One of the options listed below:
| Option | Description |
|---|---|
| MILES | Return the distance measured in miles (default) |
| NAUTICALMILES | Return the distance measured in nautical miles |
| KILOMETERS | Return the distance measured in kilometers |
| YARDS | Return the distance measured in yards |
| FEET | Return the distance measured in feet |
| METERS | Return the distance measured in meters |
| LAT_HEMISPHERE=<N | S> | Treat latitudes as in the named hemisphere |
| LONG_HEMISPHERE=<E | W> | Treat longitudes as in the named hemisphere |
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.
> select $distance(40.0, -105.0, 41.0, -106.0) distance
>> from $omnidex;
DISTANCE
--------------------------------
86.798584
select zip,
$distance(40.0, -105.0, latitude, longitude) distance,
latitude, longitude,
from zipcodes
where $distance(40.0, -105.0, latitude, longitude) < 10;
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
> select state, zip, latitude, longitude >> from zipcodes >> where $distance(latitude, longitude, >> (select latitude, longitude >> from zipcodes >> 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 ...
See also: