Page is a not externally linkable
- Code, Content, and Presentation
-- Databases
---- How best to store this data


LifeinAsia - 10:46 pm on Jul 25, 2011 (gmt 0)


Rather than computer the distances on the fly for every location except the current one (since you have to compute the distance before you know if it's less than 2 miles or not), I'd either:
A) pre-cook a table of distances, or
B) limit the scope of locations that you check.

For A, yes, it will be a rather large table. But that's what databases are built for. Since you're only looking for locations within 2 miles of each other, only store those. (Or compute out to 5 miles if you think you might change in the future). Make sure you have the table properly indexed and you should have little problem.

For B, you only want to compute the distances for locations you know will be within 2 miles. Shrink the pool of 15,000 locations down to those within a 4-mile "square" around the current location. Compute the rough length of 2 miles in latitude (call it LatDelta) and rough length of 2 miles of longitude (call it LongDelta). Assuming Lat0 and Long0 for the Lat/Long of your current location, your pool of locations to computer would be all those where:
Lat0-LatDelta <= Latitude <= Lat0+LatDelta and
Long0-LongDelta <= Longitude <= Long0+LongDelta
(You may need to modify slightly depending on when hemisphere you're in...)


Thread source:: http://www.webmasterworld.com/databases_sql_mysql/4343734.htm
Brought to you by WebmasterWorld: http://www.webmasterworld.com