This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision | |||
|
admin:optimization:geo:indexing [2010/07/29 02:15] els |
— (current) | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Geographic Searches ====== | ||
| - | |||
| - | [[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. 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: | ||
| - | |||
| - | * FLOAT | ||
| - | * DOUBLE | ||
| - | * CHARACTER | ||
| - | * C STRING | ||
| - | |||
| - | 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** | ||
| - | |||
| - | <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> | ||
| - | |||
| - | **[[admin:optimization:geo:radius|Prev]]** | **[[admin:optimization:geo:optimization|Next]] ** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | * [[admin:optimization:home|Optimization Strategies]] | ||
| - | * [[admin:optimization:geo:math|The Math Behind Geographic Searches]] | ||
| - | * [[dev:sql:functions:distance|$DISTANCE function]] | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||