This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
admin:optimization:geo:indexing [2010/07/28 14:42] els created |
— (current) | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Geographic Searches - Indexing ====== | ||
| - | |||
| - | [[admin:optimization:geo:home|Overview]] -> [[admin:optimization:geo:latlong|Latitude and Longitude]] -> [[admin:optimization:geo:radius|Radius Distance Searches]] -> **[[admin:optimization:geo:indexing|Indexing]]** -> [[admin:optimization:geo:optimization|Optimization]] | ||
| - | |||
| - | ===== Declaring Geographic Coordinates ===== | ||
| - | |||
| - | 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. | ||
| - | |||
| - | <code> | ||
| - | 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"; | ||
| - | </code> | ||
| - | |||
| - | ===== Indexing Geographic Coordinates ===== | ||
| - | |||
| - | 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 [[dev:sql:functions:distance|$distance function]] in SQL SELECT statements. | ||
| - | |||
| - | <code> | ||
| - | 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"; | ||
| - | |||
| - | </code> | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||