Differences

This shows you the differences between two versions of the page.

--- dev:sql:functions:distance [2009/12/09 14:33]
127.0.0.1 external edit
+++ dev:sql:functions:distance [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
 {{page>:top_add&nofooter&noeditbtn}}
-<html><div align="center"><span style="color:red">DRAFT</span></div></html>
-====== Omnidex SQL Function: $DISTANCE ======
 {{page>:sql_bar&nofooter&noeditbtn}}
+====== Omnidex SQL Function: $DISTANCE ======
 ===== Description =====
-$DISTANCE calculates the distance between two geographical points.
+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 =====
+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.
-===== Syntax =====
+  $DISTANCE( origin_latitude, origin_longitude,
+             destination_latitude, destination_longitude
+             [, options])
-  $distance(lat1, long1, lat2, long2, options)
+  $DISTANCE( origin_latitude, origin_longitude,
+             (destination_select_statement)
+             [, options])
-== Options: ==
-  * Units of measure
-  * MILES (default)
-  * NAUTICALMILES
-  * KILOMETERS
-  * YARDS
-  * FEET
-  * METERS
-== Default hemisphere ==
+== origin_latitude ==
-  * LAT_HEMISPHERE=[N | S]
-  * LONG_HEMISPHERE=[E | W]
-===== Examples =====
+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.
-==== Simple distance between two longitude and latitude points: ====
+== origin_longitude ==
-  select $distance(40.0, -105.0, 41.0, -106.0)
+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.
-      from $omnidex
-==== Compare latitudes and longitudes in a table against constant latitude and longitude: ====
-  select $distance(40.0, -105.0, latitude, longitude), latitude, longitude,
+== destination_latitude ==
-      from geo
-      where $distance(40.0, -105.0, latitude, longitude) < 10
-==== Compare latitudes and longitudes in a table against single latitude and longitude derived from a nested query: ====
+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.
-  select latitude, longitude,
+== destination_longitude ==
-      from geo
-      where $distance((select latitude, longitude from zipcodes
-      where zip = ‘80301’),
-      latitude, longitude) < 10
-==== Compare latitudes and longitudes in a table against multiple latitudes and longitudes derived from a nested query: ====
+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 ====
+<code>
+> select      $distance(40.0, -105.0, 41.0, -106.0) distance
+>>  from      $omnidex;
+DISTANCE
+--------------------------------
+.798584
+</code>
+==== Example 2: Distance between two sets of coordinates using columns ====
+<code>
+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
+---------  --------------------------------  ---------  ---------
+                              6.152414    39.9263  -105.0652
+                              5.205616    39.9976  -105.0983
+                              7.884606    39.9782  -105.1462
+                              7.123061    39.9778  -105.1314
+                              7.133593    39.9206  -105.0861
+                              7.277603    39.9004  -104.9553
+                              6.909988    39.9001  -105.0061
+                              5.813214    39.9275  -104.9443
+                              5.813214    39.9275  -104.9443
+                              6.954636    40.0853  -104.9302
+                              8.475827    40.1125  -104.9361
+                              7.628036    40.0992  -104.9367
+                              9.650129    39.9886  -104.8183
+                              9.650129    39.9886  -104.8183
+                              5.652985    39.9239  -104.9608
+                              9.788716    39.8830  -104.8958
+</code>
+==== Example 3: Distance between an origin and multiple sets of destination coordinates ====
+<code>
+> 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
+...
+</code>
+====== Additional Resources ======
+See also:
+  * [[admin:optimization:geo:home|Article: Geographic Searches]]
+{{page>dev:sql:functions:see_also_utility&noheader&nofooter&noeditbtn}}
-  select latitude, longitude,
-      from geo
-      where $distance((select 	latitude, longitude
-      from zipcodes
-      where region = ‘MT’),
-      latitude, longitude) < 10
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

dev/sql/functions/distance.1260369214.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes