Geographic coordinates are first declared in the Omnidex Environment File. The latitude and longitude must be stored in separate columns so that they can be differentiated. Each column is identified using a USAGE clause which describes the content of the column. Latitude and longitude may be stored in any of the following datatypes:
If geographic coordinates are stored in character-class datatypes, Omnidex will parse the latitude and longitude. Omnidex can accept the following notations:
Decimal Notation
Latitude | Longitude |
---|---|
40.015 | -105.27 |
Degrees, Minutes and Seconds Notation
Latitude | Longitude |
---|---|
40 00 54 | 105 16 12W |
40 00 54N | 105 16 12W |
40 00 54 N | 105 16 12 W |
Example
create table "ZIPCODES" physical "dat/zipcodes.dat" ( "ZIP" STRING(9) "CITY" STRING(25) "COUNTY_CODE" CHARACTER(3), "STATE" CHARACTER(2), "REGION" CHARACTER(2), "COUNTRY" CHARACTER(2), "LATITUDE" CHARACTER(9) usage "LATITUDE" "LONGITUDE" CHARACTER(9) usage "LONGITUDE" "TYPE" CHARACTER(2), "AREACODES" CHARACTER(15), "TIME_ZONE" TINYINT ) in "star.xml";
Geographic coordinates are indexed in a multi-column Omnidex index containing first the longitude and then the latitude. This index will then be available for use in optimizing the $DISTANCE function in SQL SELECT statements.
create table "ZIPCODES" physical "dat/zipcodes.dat" ( "ZIP" STRING(9), "CITY" STRING(25), "COUNTY_CODE" CHARACTER(3), "STATE" CHARACTER(2), "REGION" CHARACTER(2), "COUNTRY" CHARACTER(2), "LATITUDE" CHARACTER(9) usage "LATITUDE" "LONGITUDE" CHARACTER(9) usage "LONGITUDE" "TYPE" CHARACTER(2), "AREACODES" CHARACTER(15), "TIME_ZONE" TINYINT, omnidex "LONGLAT" ("LONGITUDE", "LATITUDE"), ) in "star.xml";