Distance Calculations - Best way to speed it up

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
inspireme
Forum Newbie
Posts: 2
Joined: Tue Jul 05, 2005 8:05 am

Distance Calculations - Best way to speed it up

Post by inspireme »

Hi, my first post on these forums, I hope somebody is able to help me :

I have a database with longitude and latitude coordinates. I also have 3 other databases of other items that also have longitude/latitude coordinates to them.

Now i want to compare all the lon/lat values from the first dataset, and then show the nearest data for the other databases.

To explain better I will tell you what im doing :
I have a directory of campsites.
I have a directory of pubs, days out, and bed and breakfast locations.

So, for the details on a campsite i want to have a box that says "the nearest pub is" + * name, number of miles away*

Now this seems to me to be extremly server intensive stuff, i have to loop through all the coordinates and output distances between them, then go back through the data and check distances to find the smalles amount. So, my question (finally!) is how to go about it? Is this the only way i can do this? How can i speed it up? Am i way off with my theory?

And lastly anybody know of any tutorials that will help with this? (especially regarding creating a temporary table that will hold the distance between coordinates, which i will later check through again and find the closest distance in miles)

It seems that im probably not up to the task on this one (pretty new to php) but I really want to do it... :S
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Options

1. Don't check the campsite against every single record in the database. Using the long/lat of the campsite you can tell certain records are going to be too far away just by using either the long or the lat.

2. Cache everything.

3. Pre-calculate the nearest pub when the record for the campsite is generated. Redo the calculation nightly (or weekly, or whatever).

4. Reduce the accuracy. Only bother using degrees and minutes for example, rather than going down to the nearest second. You may end up with 3 pubs all seemingly the same distance away, just report "3 pubs close by" to the user.

5. Buy a faster server.
inspireme
Forum Newbie
Posts: 2
Joined: Tue Jul 05, 2005 8:05 am

Post by inspireme »

The listings for the campsites are already cached, I like the idea of running a cron-job to generate the ready-cached pages.I will look into that.

Slimming down the query would help greatly - I guess i need to work out how the coordinates work before I can work that.

thanks
Post Reply