For those of you that follow my blog posts and forum activity you’ve probably been wondering where the heck I’ve been. Sadly I was not on some exotic trip somewhere, or even a local weekend getaway for that matter. Instead I was playing furniture mover, construction assistant, and general mayhem manager for just over a week now. The house flooding that happened last July finally started the repair process which was a nightmare. The contractor has been great, but along the way we discovered a leak at a roof seam which meant replacing an entire side to the house. Then we found the last contractor nailed through some water pipes which means some unscheduled repairs to the master bedroom. Needless to say I’ve been busy as heck but have not been busy with getting SLP4 wrapped up or answering forum questions.
I think we’re entering the final stretch of construction and actually had the opportunity to spend most of the day on SLP code and related issues. The past 24 hours I’ve been chasing down some issues with SLP4 and an exponential delay in processing CSV imports. Turns out the “increase the delay each time a location hits OVER QUERY LIMIT” is great in theory, but in the real world you can hit Google’s infamous “Daily Limit” which caused all sorts of problems for me during my testing.
Google Query Limits
Google has two different paths to reaching the “Over Query Limit” issue. Both are typically related to a bulk import using the CSV Import in Pro Pack. Users visiting your website are using the client side geocoding process which Google counts towards their personal browser query limit which, to paraphrase the Google website, “you don’t have to worry about, nobody but a spam bot will hit that limit”.
However server based requests, which is the proper method for handling mass lookups of locations and geocoding them with proper latitude and longitude coordinates, is another story. You CAN hit one of the two limits Google enforces. The first limit is the “how many requests per minute” limit, or “Rate Limit”. The rate limit is usually fairly happy as long as you are not requesting more than a dozen geocodes per minute. If you run at “full throttle” for too long, Google will put you on a brief hold.
Rate Limit Management
Store Locator Plus does a fairly good job at managing the Rate Limit with Google. This is the most common cause of Over Query Limit (OQL) and by introducing a slight delay between requests the Store Locator Plus engine can mitigate the OQL responses. In SLP3 the process was fairly simple, if you receive an over query limit response from Google, wait a half-second and re-submit. It will do this process up to the number of retires you set in the settings panel.
With SLP4 the process is far more intelligent. While it will still retry n-times based on your setting, it starts out with a 2 second delay and increases the delay by 1 seconds each time an over query limit message is received. These parameters are based on Google’s recommendations when OQL is received, waiting 2 seconds before the first request that returns OQL and waiting 1 second between subsequent requests. The SLP4 engine will keep track of the delay between all of your location geocoding requests, extending the delay up to 6 seconds between requests until the time the Google stops returning the OQL message. This makes the SLP4 bulk imports much faster and has shown to get more locations encoded than the SLP3 geocoding engine. With SLP4 you can also change the maximum delay to increase it if you are on shared or cloud hosted server where one IP address may have many sites using Google service, or turn it down if you are on a dedicated server with a dedicated IP address.
Daily Limit Management
The other way Google stops you from running geocoding processes is via the Daily Limit. If you’ve been abusing their geocoding service for too long they will shut you off for at least 24 hours. Normally you (or the IP address you share) need to make at least 2500 requests before they put you on this limitation. In my testing I reached nearly 10,000 locations encoded before I ASSUME I was put on the Daily Limit restriction. Since Google returns the same general Over Query Limit response for both Rate Limit and Daily Limit I cannot tell for certain but there are some key indicators. Once I was put on Daily Limit, I could not encode a SINGLE location from the server request interface. I also learned that if I tried to re-geocode a location my 24-hour wait limit was extended. I have no idea by how much, but it seems like they won’t reset your Daily Limit or put you on a strict Rate Limit program once you’ve “crossed the line”.
Google also notes in their Web API Strategies document that you can be locked out from their service, meaning your IP address can be permanently banned from doing any geocoding.
The short version of what this means: if you hit the Daily Limit STOP trying to geocode your locations for at least 24 hours.
Mitigation of OQL
– Set the Google Retries to 3, more than 3 doesn’t usually yield more locations being geocoded and just adds more marks against your Rate Limit or Daily Limit.
– If you are on a SHARED IP address server (virtual server, virtual dedicated server, cloud server, or don’t-know-what server) set your maximum delay to 10 seconds.
– If you suspect you have hit your Daily Limit, import your data with “skip geocoding” turned ON. This will get the locations into your database, though they will not be searchable, so you can later geocode them in smaller blocks. I try to do no more than 100 locations per re-coding session, wait 20 minutes, and try 100 more. Stop the process when any single batch has more than half the locations returning “Over Query Limit” warnings.
– Look closely at the SLP4 messages that are returned. They provide more information and better statistics on the uncoded locations. Not all uncoded locations are Over Query Limit issues. Some addresses simply cannot be located by Google or got lost in the Google processing. Each result will report different status messages. Only re-try geocoding locations with OQL messages.
– Use the CSV lat/long fields to skip geocoding on any locations where you know the proper lat/long. You can use free services like the Texas A&M service for US locations to manually look up lat/long. They even allow spreadsheet input of locations.
– Once you have locations geocoded use the export feature in Pro Pack (v4+) to get the lat/long out of the system for future updates. Any time you can avoid the bulk import geocoding process you will speed up the import AND stop Google from giving you the Overy Query Limit message.