homepage Welcome to WebmasterWorld Guest from 54.227.215.140
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
How best to store this data
unique node distances
The_Hat

10+ Year Member



 
Msg#: 4343734 posted 9:25 pm on Jul 25, 2011 (gmt 0)

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

10+ Year Member



 
Msg#: 4343734 posted 9:48 pm on Jul 25, 2011 (gmt 0)

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

10+ Year Member



 
Msg#: 4343734 posted 10:04 pm on Jul 25, 2011 (gmt 0)

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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4343734 posted 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...)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved