Omnidex SQL Function: $DISTANCE

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 article provides more detail on performing geographic searches in Omnidex.

This function returns a DOUBLE datatype.

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, 
           destination_latitude, destination_longitude 
           [, options])
$DISTANCE( origin_latitude, origin_longitude, 
           (destination_select_statement)
           [, options])
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 STRING datatype.

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 STRING datatype.

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 STRING datatype.

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

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
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 1: Distance between two sets of coordinates

> select      $distance(40.0, -105.0, 41.0, -106.0) distance
>>  from      $omnidex;

DISTANCE
--------------------------------
                       86.798584

Example 2: Distance between two sets of coordinates using columns

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

Example 3: Distance between an origin and multiple sets of destination coordinates

> 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
...

Additional Resources

 
Back to top
dev/sql/functions/distance.txt ยท Last modified: 2016/06/28 22:38 (external edit)
 
 
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0