Geographic Searches - Indexing

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

Geographic searches are specified using a $distance function. As long as there is a multi-column Omnidex index containing the latitude and longitude, the $distance function will be automatically optimized.

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";