Geographic Searches - Indexing

Overview → Latitude and Longitude → Radius Distance Searches → Indexing → Optimization

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. They may be stored in either using character-class datatypes, or may be stored in floating point fields. Each column is identified using a USAGE clause which describes the content of the column.

create table               "ZIPCODES"
 physical                  "dat\zipcodes.dat"
 (
  "ZIP"                    C STRING(9)                        
  "CITY"                   C 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 latitude and then the longitude. 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"                    C STRING(9),
  "CITY"                   C 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 "LATLONG" ("LATITUDE", "LONGITUDE"),
 )
 in                        "star.xml";