If you’ve been following my geocaching posts, you have probably realized that I keep everything in a GPX (read: XML ) file and go lookups on that.  The reason is simple: with a GPX file you can keep everything together in one place and use GPSBabel to do “queries”  on that.  An important query for me is finding the distance away from one point to a geocache in my GPX file.

I wrote many shell scripts to manipulate data into GPX files and to query against them.  But, eventually, my XML-as-a-database abstraction ran into problems:

• I wanted to be able to choose a waypoint (geocache or otherwise) by name and find all the geocaches around it.  Doable with some Python DOM-fu, but not something I wanted to do a lot of.
• Mucking with the GPX files, especially those with Groundspeak’s namespaces is bulky.  The size is huge.
• I wanted to easily mark a geocache that I had found as “Found” but keep it in my records.

 All of this means that my abstraction is leaky.  The real solution was to put it in a database but manually figuring out the distance between two latitude and longitude coordinates is not trivial.  I could do it at the database level, but the only decent, free DB with geographical support is PostGIS, which implies a lot of overhead for my little stuff.  Not that I’m complaining — it’s just not something I wanted to do.

I was looking for something else last night when I saw this on the close method in the Geo::Distance  documentation.  Huh? A DBH filehandler?  Sqlite database?  Oh, it seems I can point Perl at a database of waypoints and it will find the closest points to the given latitude and longitude.  This is what I’ve been looking for.

Using Geo::Distance is a piece of cake.  Making the database is a different story.  I grabbed Python and SQLObject and created two tables — waypoint, which contains the latitude, longitude, name, and description from the GPX file.  Then another table called cache that contains the geocache-specific information — container type, diffculity, etc. That way, I can have waypoints that aren’t geocaches, so I can still query on them.

Then the diffcult part — writing a GPX to DB converter.  It was easier than I thought, because I combined SQLObject to represent the databae and cElementTree to work the XML and *poof* things were converted.  I first worked on my found cache list and then a list of caches from an upcoming trip.  Very easy.  Then I worked my “master list” — the 8MB GPX file.  I had to do some massaging, but it turned out nicely.  And the SQLite DB is just over 1MB — much smaller than the GPX file!

I haven’t posted anything yet, because it’s still a little rough.  That said, if you read this via Google and want a copy, drop me a line and I’ll ship it off to you.