Hi,
ETL gig involving bulk-geocoding a table of Location names. You'll need to be
familiar with the Geonames service (and perhaps Yahoo/Google geocoding APIs)
and have solid SQL skills.
You'll be given 3 MySQL tables:
1.) A "Locations" table containing 7400 international locations. Each location
row has a "parent id" defined, creating a hierarchical relationship between the
various locations. So, more precise names can be created by recursively
concatenating the location names together. For example:
-- World (id = 0, parent = null)
-- United States (id = 100, parent = 0)
-- New York (id = 200, parent = 100)
-- Montauk (id = 300, parent = 200)
2.) A table of US zip codes (will help to disambiguate some US locations).
3.) A table of country codes (will help to break up the lookups into country
chunks)
Your delivarable:
1.) A plan/approach to geocoding. This will outline the steps you'll take
before doing any work.
For example, perhaps you'd want to join countries -> locations, pull
geonames feeds for those countries, break down the lookups by country. Also,
what's your plan for disambuating the lookup results? If you want to write a
loop that hits geonames 7500 times with a raw location name, thank you in
advance for not replying. :)
2.) An updated Locations table containing 5 new fields:
a.) lat coord
b.) long coord
c.) foreign key to the US zip code table (if applicable)
d.) foreign key to the Country table
e.) geonames ID
Cheers,
Matt
_______________________________________________
New York PHP Community Jobs Mailing List
http://lists.nyphp.org/mailman/listinfo/jobs
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
