This shows you the differences between two versions of the page.
dev:sql:functions:distance [2010/07/28 18:09] els |
dev:sql:functions:distance [2016/06/28 22:38] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | {{page>:top_add&nofooter&noeditbtn}} | ||
- | {{page>:sql_bar&nofooter&noeditbtn}} | ||
- | ====== 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 [[admin:optimization:geo:home|article]] provides more detail on performing geographic searches in Omnidex. | ||
- | |||
- | |||
- | ===== Syntax ===== | ||
- | |||
- | $DISTANCE(origin_latitude, orgin_longitude, destination_latitude, destination_longitude [, options]) | ||
- | |||
- | $DISTANCE( (origin_select_statement), (destination_select_statement) [, options]) | ||
- | |||
- | |||
- | |||
- | == expression == | ||
- | |||
- | 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. | ||
- | |||
- | == datatype == | ||
- | Required. One of the datatypes listed below: | ||
- | |||
- | {{page>dev:appendix:reference:datatypes_insert&noheader&nofooter&noeditbtn}} | ||
- | |||
- | 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}} | ||
- | |||
- | == length == | ||
- | 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. | ||
- | |||
- | == options == | ||
- | |||
- | Optional. Options can be applied to the data conversion to affect the output. The supported options are: | ||
- | |||
- | ^Option ^Description ^ | ||
- | |LJ |Left-justify | | ||
- | |RJ |Right-justify | | ||
- | |UPS |Upshift | | ||
- | |DNS |Downshift | | ||
- | |SF |Space-fill | | ||
- | |ZF |Zero-fill | | ||
- | |BWZ |Blank when zero | | ||
- | |||
- | |||
- | ===== Example ===== | ||
- | ==== Example 1: Distance between two sets of coordinates ==== | ||
- | |||
- | <code> | ||
- | select $distance(40.0, -105.0, 41.0, -106.0) | ||
- | from $omnidex | ||
- | </code> | ||
- | |||
- | ==== Example 2: Distance between two sets of coordinates using columns ==== | ||
- | |||
- | <code> | ||
- | select $distance(40.0, -105.0, latitude, longitude), | ||
- | latitude, longitude, | ||
- | from geo | ||
- | where $distance(40.0, -105.0, latitude, longitude) < 10 | ||
- | </code> | ||
- | |||
- | ==== Example 3: Distance between two sets of coordinates using a subquery ==== | ||
- | |||
- | <code> | ||
- | select latitude, longitude, | ||
- | from geo | ||
- | where $distance((select latitude, longitude | ||
- | from zipcodes | ||
- | where zip = ‘80301’), | ||
- | latitude, longitude) < 10 | ||
- | </code> | ||
- | |||
- | ==== Example 4: Distance between multiple sets of coordinates using a subquery ==== | ||
- | |||
- | <code> | ||
- | select latitude, longitude, | ||
- | from geo | ||
- | where $distance((select latitude, longitude | ||
- | from zipcodes | ||
- | where region = 'MT'), | ||
- | latitude, longitude) < 10 | ||
- | </code> | ||
- | |||
- | ====== Additional Resources ====== | ||
- | |||
- | See also: | ||
- | {{page>dev:sql:functions:see_also_utility&noheader&nofooter&noeditbtn}} | ||
- | |||
- | |||
- | {{page>:bottom_add&nofooter&noeditbtn}} | ||