A look back on AQ

There is nothing important in this post. There is a lot of technical information to follow. Continue reading at your own risk! =)

So I was poking around the database, doing the usual sort of stuff, and I noticed the size of the database just hit 3 gigabytes. As I type this, it now stands at "3,000.07 MB" according to my little stats program.

It has 197 tables keeping track of all the happenings on AQ--the message boards, the letterbox listings, account information--in all, "approximately" 22,310,313 rows of organized information. I know that number looks quite precise, but according to the stats program, it's actually an "estimate." Some of the tables, apparently, are difficult to get solid numbers for. =)

Back in 2008--seems so long ago, doesn't? Anyhow, back in 2008, I posted about AQ Database Milestones. It's a long, boring and tedious post--no need to read it now. I remember it, though, because AQ had just passed the 100th table in the database and I had hoped the next 100 would run just as smoothly. I still haven't gotten to that 200th table, but at 197, it's pretty darned closed.

(I should note: These are tables strictly used for AQ purposes. There's a totally different database running Walking4.com. There are 11 tables for that website and that database weighs in at 4.52 MB--considerably smaller than even when AQ started!)

The most interesting part of that post, though, is that when Atlas Quest debuted, it had just 12 tables that took up 70 megabytes of data. Fortunately for me, I wasn't working with 3 gigs of data at the time because I worked on a dial-up connection then. Even 70 megabytes is pretty slow over a 56K modem. =) Now it's nearly 50 times that size and still growing....


The last table I created for the database deals with the geocoders. I've talked about the geocoders before here, in The Magic of Geocoders..... A geocoder, for those who aren't familiar with the term, is a program that can convert a street address or other location into latitude and longitude coordinates. When you run a search for "Houston, TX," AQ needs to convert that into coordinates to be able to run a search for all letterboxes near that location. That's where the geocoders step in.

Anyhow, one of the geocoders AQ depended on stopped working unexpectedly. Nobody noticed, though, because there was a backup geocoder that sprung into action to handle the problem.

Two years ago, when I first redesigned the geocoders, I created them to work like lego bricks--I could snap off one geocoder and snap on another one without affecting any of the code that actually needs a geocoder. This was the first real tests of that new redesign--and the system worked flawlessly. Even with one broken geocoder, AQ could detect the problem and automatically move to the working one. It's kind of a good feeling--that the code I worked on nearly two years ago to handle problems exactly like this worked flawlessly and generated absolutely no downtime for AQ.
So most of the past week I've been working on the "geocoder problem." I created two new geocoders--one that uses Bing and one that uses MapQuest. And retired the Yahoo geocoder which no longer is available, and yesterday, I snapped those into the rest of AQ. The update was so easy and so quick, I didn't even need to take down the website in order to get it done.

I also added more functionality to the geocoders. These geocoders have a much smaller limit on the number of locations I can geocode each day than the one it replaced, so I took steps to reduce those numbers by caching results.

If you're curious, when you do run a search for a location, AQ now tries to figure out what you're talking about in the following order:

1. It checks if you typed in latitude and longitude coordinates and just uses those.
2. It checks the AQ database to see if it's a location AQ already knows about.
3. It check the Bing geocoder to see if Bing can handle it.
4. It checks the Google geocoder to see if Google can handle it.
5. It checks an internally-hosted table with millions of locations to see if it can be found locally. This is a pretty bad and primitive search, though, which is why it comes so far down the list, but it's free and doesn't count against any quotas, so I'll keep it.
6. Then it checks if MapQuest can handle the location. The MapQuest geocoder worked so poorly in my tests, it essentially comes in dead last. I'll use it as a last resort!
7. For the next month or so, there's a second Google geocoder that will then get a crack at the location. This is the depreciated Google geocoder, an older version of their current geocoder, and Google is planning to pull the plug on it next month. I'll keep it in the lineup as long as it's still chugging along. It's a decent geocoder, but since I know already that it will stop working next month, it seems prudent not to be relying on it at this point. Except as a last resort. =) After Google does pull the plug on it next month, I'll just remove it.

The only real problem, I imagine, are those people who want my Under Construction letterbox. As AQ becomes more robust, I'm able to do more and more updates without actually taking the website down--and this clue is only available when the website is down for an update. Which makes the clue progressively more and more difficult to get! Not only am I having to do fewer "big updates" than in years past, but they're often a lot faster too.

Anyhow, I just felt like reminiscing.... Go ahead and go back to your normally scheduled program. =)