This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
admin:optimization:geo:optimization [2010/07/28 14:45] 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]]** | ||
| - | |||
| - | ===== Optimizing Geographic Searches ===== | ||
| - | |||
| - | Geographic searches are specified using a [[dev:sql:functions:distance|$distance function]]. As long as there is a multi-column Omnidex index containing the latitude and longitude, the $distance function will be automatically optimized. | ||
| - | |||
| - | <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}} | ||