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 location, corner1 and corner2 values all represent single geographical (latitude/longitude) points. 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 Vortex function <geo2code> or the SQL function latlon2geocode(). 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. Eg.:

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

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.