Geocoding for free (nearly)

So, what is "geocoding"? Apparently, it's translating addresses into longitude/latitude.

I got a Magellan GPS for christmas and was disappointed by the crappy POIs included. I then got a TomTom and was even more disappointed than I was about the Magellan.
The Magellan is like a fairly average human (average, dependable, gets along fairly well), while the TomTom is like RainMan (brilliant, buys underwear at KMart, but not very useful)...

So, I started researching.... How do I get useful POIs into these crappy GPS devices? Geeze. What I really want is the "yellow pages" on the GPS... So, when I want to look up a phone number, I'd open my GPS, search, call, and *THEN* the GPS would give me directions to address. Way cool, right?

So, here's my $60 solution (nearly free, right??)...

Hear me out on this... it's a bit convoluted but it works...

1. grab a copy of Street Atlas 2008 PLUS (the one with the phone book included)...

2. Export a bunch of "locations" (via the phone search) as XDATA. The serach is limited to 1,000 so, you'll have to do a "batch at a time"... eg, do one zip code, then another zip code, etc... This is the "geocoding" part. Exproting as XDATA is the where the magic happens.

3. The XDATA created by Street Atlas is actually an MDB (access) database (it names it as filename.DDS or something). The password is "SELECT USER". Under vista, the location is "C:\ProgramData\DeLorme Docs\Datasets"... Open this file with Access.

4. Use access to create an a set of Excel/CSV ready rows... Yummy!!!
Use a query like:
SELECT XUserTable.Listing, XUserTable.Address, XUserTable.City, XUserTable.State, XUserTable.Yellow_Page_Heading, geolat / -8388608.07678 as lat, geolon / 8388607.9284564 as lon FROM XUserTable INNER JOIN Point ON XUserTable.ID = Point.PointID;

Note: the "Score" column in the XUserTable indicates the accuracy of the "geocoding" algorithm (0-100). The higher the score, the better the lon/lat results. I'd suggest squelching anything below 97.


If all this sounds exciting but you don't have M$ Access... well, you can extract the data to CSV using just VB script (which you have if you run Vista/XP/2000). If someone is interested, I can post a .VBS example.

And there you go...