Performance using location based information and proximity filters

Question

I'm building a site where each piece of content is geocoded with latitude and longitude. Information will be shown based on proximity to a location chosen by the user. It seems like it would be faster if the information was shown based on city or state arguments because then Views can be cached. If every page view requires a proximity search of nodes from a lat/lng coordinate that no two users will ever share, I imaging the database will be getting a big work out.

A proximity search is fine, but how do I show other nodes in context of proximity whether it is state, town, city, or country?
What would the information architecture be?

Answer 1

I'll answer the first part of your question about caching with a gentle warning and a tip...

I've tried caching location-based searches and it's a fast way to a HUGE (slow) cache_views_data table.

The reason is the same as the need to cache; there are lots of permutations of latitude and longitude, so every new request will add to the cache. Depending on the speed at which you have new content or updates, the cache will either:

• become immense as the queries are stored for many different lat/long combinations. This will happen with long cache times.
• be quite big but rarely hit because the cache times are suitably short to keep the pages showing fresh data.

Super-huge cache tables can quite possibly be as much of a performance hit as having the database just calculate what's nearby. Lookups might be ok but inserts and expunging old data will certainly slow your database server down.

One approach I considered but haven't yet implemented is to make the latitude/longitude arguments less granular by rounding the values to fewer decimal places. This way, you can have much higher cache hit rate at the expense of a little accuracy. For example, there's much more chance of (51.1, 0.9) being hit by multiple users than (51.123456, 0.887744). The number of decimals, and therefore accuracy loss, is entirely up to you.

You can either implement a views hook to change the views arguments, or use Views PHP to add custom code to your arguments which allows them to be rounded within the view itself.