Show pagesource

Old revisions

This is an old revision of the document!

QuickLinks	Statements	Functions	OdxSQL

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

$DISTANCE( origin_latitude, origin_longitude, 
           destination_latitude, destination_longitude 
           [, options])

$DISTANCE( (origin_select_statement),
           (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 C 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 C 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 C 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 C 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

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

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.

Example

Example 1: Distance between two sets of coordinates

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

Example 2: Distance between two sets of coordinates using columns

select      $distance(40.0, -105.0, latitude, longitude), 
            latitude, longitude,
  from      geo
 where      $distance(40.0, -105.0, latitude, longitude) < 10

Example 3: Distance between two sets of coordinates using a subquery

select      latitude, longitude,
  from      geo
  where     $distance((select      latitude, longitude 
                         from      zipcodes 
                         where     zip = ‘80301’),
                      latitude, longitude) < 10

Example 4: Distance between multiple sets of coordinates using a subquery

select      latitude, longitude,
  from      geo
 where      $distance((select      latitude, longitude 
                         from      zipcodes 
                        where      region = 'MT'),
                      latitude, longitude) < 10