One of the unique capabilities in Jobster is the ability to display a clickable map of the entire US showing all jobs that match the users search.

When the user clicks on a region in the map, the display zooms to show all matching jobs in that region overlaid on Google maps.

Mark Maunder is the smart guy who built this; on his blog he shares some of the SQL tricks he devised to make radius queries lightening fast.

The typical approach in SQL to finding all locations within a given radius is to first find all locations with a square bounding box that encloses the radius, then use a precise (but expensive) distance function to eliminate points in the box but outside the circle.

This is not terribly inefficient, but certainly not optimal– it would typically turn into an index seek on latitude, another on longitude, an intersection operation to find points within the bounding box, and then a series of per-point radius computations.

Marks clever trick is to trade off space for time– in pure SQL he builds an enormous lookup table that gives the distances between all pairs of locations. This table only needs to built once and makes all subsequent radius queries significantly faster. Adding an index on source latitude, longitude, and distances allows to do the radius query in a single index seek. If there is maximum radius you care about, the size of the table can be reduced somewhat by eliminating pairs of locations further apart than distance.