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.

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:

Datatype	Class	Description
CHARACTER(n)	STRING	Space-filled character string of n characters
CHAR(n)	STRING	Synonym for CHARACTER(n)
STRING(n)	STRING	Null-terminated character string of n characters
VARCHAR(n)*	STRING	String of max n characters, with length variable
CLOB(n)*	STRING	Character large object of max n characters, with length variable
[SIGNED] TINYINT	NUMERIC	1-byte, signed integer ( -128 to 127 )
UNSIGNED TINYINT	NUMERIC	1-byte, unsigned integer ( 0 to 255 )
[SIGNED] SMALLINT	NUMERIC	2-byte, signed integer ( -32768 to 32767 )
UNSIGNED SMALLINT	NUMERIC	2-byte, unsigned integer ( 0 to 65535 )
[SIGNED] INTEGER	NUMERIC	4-byte, signed integer ( 2147483648 to 2147483647 )
UNSIGNED INTEGER	NUMERIC	4-byte, unsigned integer ( 0 to 4294967295 )
[SIGNED] BIGINT	NUMERIC	8-byte, signed integer ( -2^63 to 2^63-1 )
UNSIGNED BIGINT	NUMERIC	8-byte, unsigned integer ( 0 to 2^64-1 )
FLOAT	NUMERIC	IEEE four-byte, single-precision floating point
DOUBLE	NUMERIC	IEEE eight-byte, double-precision floating point
ANSI DATE	DATE	10-byte ANSI date
ANSI TIME	DATE	11-byte ANSI time
ANSI DATETIME	DATE	24-byte ANSI datetime
ASCII DATE	DATE	8-byte ASCII date ( YYYYMMDD )
ASCII DATE(6)	DATE	6-byte ASCII date ( YYMMDD )
C DATETIME	DATE	8-byte integer based on the C time() routine
ODBC DATE	DATE	6-byte ODBC proprietary date
ODBC TIME	DATE	6-byte ODBC proprietary time
ODBC DATETIME	DATE	6-byte ODBC proprietary datetime
ORACLE DATE	DATE	7-byte Oracle proprietary date
ORACLE TIME	DATE	7-byte Oracle proprietary time
ORACLE DATETIME	DATE	7-byte Oracle proprietary datetime
OMNIDEX DATE(n)	DATE	Omnidex proprietary date supporting n digits of YYYYMMDD
OMNIDEX TIME(n)	DATE	Omnidex proprietary time supporting n digits of HHMMSSNN
OMNIDEX DATETIME(n)	DATE	Omnidex proprietary datetime supporting n digits of YYYYMMDDHHMMSSNN
* Omnidex recommends use of the CHARACTER and STRING datatypes rather than VARCHAR and CLOB due to the requirement of a length variable.

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.

Datatype	Class	Description
NATIONAL CHARACTER(n)	STRING	Space-filled character string of n characters
NATIONAL CHAR(n)	STRING	Synonym for CHARACTER(n)
NATIONAL STRING(n)	STRING	Null-terminated character string of n characters
NATIONAL VARCHAR(n)*	STRING	String of max n characters, with length variable
NATIONAL CLOB(n)*	STRING	Character large object of max n characters, with length variable
* Omnidex recommends use of the NATIONAL CHARACTER and NATIONAL STRING datatypes rather than NATIONAL VARCHAR and NATIONAL CLOB due to the requirement of a length variable.

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

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