latlon2geocode, latlon2geocodearea

  latlon2geocode(lat[, lon])
  latlon2geocodearea(lat[, lon], radius)

The latlon2geocode function encodes a given latitude/longitude coordinate into one long return value. This encoded value - a "geocode" value - can be indexed and used with a special variant of Texis' BETWEEN operator for bounded-area searches of a geographical region.

The latlon2geocodearea function generates a bounding area centered on the coordinate. It encodes a given latitude/longitude coordinate into a two- value varlong. The returned geocode value pair represents the southwest and northeast corners of a square box centered on the latitude/longitude coordinate, with sides of length two times radius (in decimal degrees). This bounding area can be used with the Texis BETWEEN operator for fast geographical searches. latlon2geocodearea was added in version 6.00.1299627000 20110308; it replaces the deprecated Vortex <geo2code> function.

The lat and lon parameters are doubles in the decimal degrees format. (To pass DDDMMSS "degrees minutes seconds" (DMS) format values, convert them first with dms2dec or parselatitude()/parselongitude().). Negative numbers represent south latitudes and west longitudes, i.e. these functions are east-positive, and decimal format (unlike Vortex <geo2code> which is west-positive, and DMS-format).

Valid values for latitude are -90 to 90 inclusive. Valid values for longitude are -360 to 360 inclusive. A longitude value less than -180 will have 360 added to it, and a longitude value greater than 180 will have 360 subtracted from it. This allows longitude values to continue to increase or decrease when crossing the International Dateline, and thus avoid a non-linear "step function". Passing invalid lat or lon values to latlon2geocode will return -1. These changes were added in version 5.01.1193956000 20071101.

In version 5.01.1194667000 20071109 and later, the lon parameter is optional: both latitude and longitude (in that order) may be given in a single space- or comma-separated text (varchar) value for lat. Also, a N/S suffix (for latitude) or E/W suffix (for longitude) may be given; S or W will negate the value.

In version 6.00.1300154000 20110314 and later, the latitude and/or longitude may have just about any of the formats supported by parselatitude()/parselongitude() (here), provided they are disambiguated (e.g. separate parameters; or if one parameter, separated by a comma and/or fully specified with degrees/minutes/seconds).


  -- Populate a table with latitude/longitude information:
  create table geotest(city varchar(64), lat double, lon double,
                       geocode long);
  insert into geotest values('Cleveland, OH, USA', 41.4,  -81.5,  -1);
  insert into geotest values('Seattle, WA, USA',   47.6, -122.3,  -1);
  insert into geotest values('Dayton, OH, USA',    39.75, -84.19, -1);
  insert into geotest values('Columbus, OH, USA',  39.96, -83.0,  -1);
  -- Prepare for geographic searches:
  update geotest set geocode = latlon2geocode(lat, lon);
  create index xgeotest_geocode on geotest(geocode);
  -- Search for cities within a 3-degree-radius "circle" (box)
  -- of Cleveland, nearest first:
  select city, lat, lon, distlatlon(41.4, -81.5, lat, lon) MilesAway
  from geotest
  where geocode between (select latlon2geocodearea(41.4, -81.5, 3.0))
  order by 4 asc;

For more examples of using latlon2geocode, see the geocode script in the texis/samples directory.


The geocode values returned by latlon2geocode and latlon2geocodearea are platform-dependent in format and accuracy, and should not be copied across platforms. On platforms with 32-bit longs a geocode value is accurate to about 32 seconds (around half a mile, depending on latitude). -1 is returned for invalid input values (in version 5.01.1193955804 20071101 and later).


The geocodes produced by these functions are compatible with the codes used by the deprecated Vortex functions <code2geo> and <geo2code>. However, the <code2geo> and <geo2code> functions take Texis/Vortex DMS format (DDDMMSS "degrees minutes seconds", as described in the dec2dms and dms2dec SQL functions).

Copyright © Thunderstone Software     Last updated: Tue Feb 21 13:46:30 EST 2017
Copyright © 2017 Thunderstone Software LLC. All rights reserved.