How best to store this data

unique node distances

[The_Hat]

I am building a web app and part of this app will be information of other locations of the same type as the one the user is looking at. Such like "You are looking at a piece of data of a certain type... here are other locations of this type near to this one.."...

I have written php to go through all of the address and query for lat/lng.. and have written php to find the distances from one place to the other and am considering to store in a table

place1ID | place2ID | miles


( Where place IDs are INT(10) and miles is a 4,6 decimal )

but only where the distance from one to another is less than 2 miles... and only unique distances.. and not a distance from a place to itself..

My concern is the table of locations right now is about 15 thousand rows and so the resulting table for unique distances may be very large.. so I am concerned about size of resulting table as well as query time of that table to show the nearby location data..

Anybody have any input as to a better way to handle this?

[stajer]

Don't do it that way.

1. Store the lat/long of the original piece of data in the table.
2. Google for the formula to calculate distances between lat/long coordinates.
3. When you need to find other pieces of data within x miles, use the formula from #2 to calculate the outside lat/long of those X miles from the original piece of data.
4. query your db for other pieces of data between the lat/long you calculated.

[The_Hat]

1. .. Yes I already do..
2. .. Already have it and use it as I mentioned in my second paragraph.
3. .. So you are saying that it is better to query the tables and build the list on the fly rather than get the information and store it.

Thank you for the reply.

[LifeinAsia]

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...)