Geographical Searches with BETWEEN

A second form of BETWEEN is used for doing geographical searches. In this form the operator is used as:

location [NOT] BETWEEN (corner1, corner2)

(The parentheses are significant, and distinguish the special two-dimensional geographical form of BETWEEN from the normal one-dimensional range search.) The location, corner1 and corner2 values all represent single geographical (latitude/longitude) points - "geocode" values. This form of the BETWEEN operator will be true for all location points that are within (or on) the rectangular box defined by diagonally-opposite corners corner1 and corner2.

The left-side location must be a long value. It is a geographically-encoded ("geocode") value, returned from the SQL function latlon2geocode() or the Vortex function <geo2code>. Typically location is a long geocode column in a table representing the physical location of a row's data.

The right-side corner1 and corner2 points define diagonally-opposite corners of the bounding box. They are typically also long geocode values. However, in version 5.01.1194651000 20071109 and later, they may each be a single varchar (text) value containing a space- or comma-separated latitude/longitude pair, which will automatically be converted to geocode format. E.g.:

location BETWEEN ('40N 80W', '41N 81W')

In version 6.00.1298946000 20110228 and later, the bounding box may be computed inline from coordinates with latlon2geocodebox(); e.g. for a 0.5-degree "radius" bounding box centered on 40.5N, 80.5W:

location BETWEEN (select latlon2geocodebox(40.5, -80.5, 0.5))

When used in conjunction with a regular index on the expression column, the BETWEEN operator can greatly speed up geographical searches, as it reduces a two-dimensional AND search (with its potentially large merge or post-process) into a single-dimensional, all-index operation.