Search by Location and Limit Results by Distance

[eluser]JamesTaylor[/eluser]
I'm starting to plan a site that i will be building as a personal project and want to include a function which allows my database of clubs to be searched by a postcode and then have the results limited by the distance from the postcode supplied / displayed in order of distance from the postcode. All the locations will be in the UK.

I haven't worked with this type of search criteria before and have a few questions tumbling around in my head at the moment so i thought people more clued up than me may be able to throw some light on the subject!

I plan to use the google maps api with the google geocoding api so that i will be able to display the results as a simple html list and also plot them on a google map... again i will be figuring this out as i go along!

From what i have read so far it seems that any location provided or searched against needs to be translated to a longitude and latitude value 1st.

My main question is how store the location info of the clubs in my database... should i include a longitude / latitude field in the database which is generated when the the address / postcode is 1st entered into the system or should i just be using the postcode data and have the long / lat info created as the search code runs?

My feeling is that it would be best to generate the long / lat values on insertion to the database so it is easily available for future searches?

The next question is what php / mysql functions should i be reading up on to allow me to search the database and recieve results which are within a set distance from the user inputted location - which will be a postcode or town in the UK.

By asking these questions i am hoping to straighten my thought process out at this point of the project rather than seeking the exact answer of this is the code you will need, if anyone can provide a good explanation of what i will be encountering it'd be much appreciated.

Also any good tutorials links on the subject welcomed!

And as this is my first post here... Hello everyone!

Thanks

James

[eluser]mattpointblank[/eluser]
You should definitely cache the results of the geocode lookup in your database - these aren't particularly speedy lookups so it makes sense to store them once completed. I'd also suggest using the google maps API to work out distances, although I did use the code below for a rudimentary stab at it:

Code:

$lat = $_POST['lat'];
$lng = $_POST['lng'];
    
// reduce them down a bit, since mine were way too specific
$lat = round($lat, 3);
$lng = round($lng, 3);

// this is the range, eg i wanted to search for anything that was .05 of a degree from what was entered
$latHigher = $lat+0.05;
$latLower = $lat-0.05;
$lngHigher = $lng+0.05;
$lngLower = $lng-0.05;

// get the data
$query = "SELECT * FROM table WHERE (Latitude BETWEEN '$latLower' AND '$latHigher')
AND (Longitude BETWEEN '$lngLower' AND '$lngHigher')

This worked out for me.

[eluser]JamesTaylor[/eluser]
Matt,

thanks for the sample code.

i seem to follow how the code is working and have made a bit of a leap that we can associate a given change of degree in lat or long to a set distance... perhaps 1 degree = 20miles for example? (i know nothing about long / lat values in reality so having to play catch up!)

from that leap i have had a quick google search to look at info relating to lat / long degrees being relative to a given distance, what i have found is that although we can say a certain change in lat / long does equate to a set distance, the set distance changes dependant upon the portion of lat / long we are withing...?!

so i'm now wondering how this is dealt with? or do we just have to provide a default average value and accept a slight tolerance on our returned results?

this is what i was looking at:


AT LATITUDE 50 DEGREES (NORTH OR SOUTH)

One degree of latitude = 111.23 km or 69.11 mi
One minute of latitude = 1.85 km or 1.15 mi
One second of latitude = 30.90 m or 101.37 ft

One degree of longitude = 71.70 km or 44.55 mi
One minute of longitude = 1.19 km or .74 mi
One second of longitude = 19.92 m or 65.34 ft

AT LATITUDE 60 DEGREES (NORTH OR SOUTH)

One degree of latitude = 111.41 km or 69.23 mi
One minute of latitude = 1.86 km or 1.15 mi
One second of latitude = 30.95 m or 101.54 ft

One degree of longitude = 55.80 km or 34.67 mi
One minute of longitude = .93 km or .58 mi
One second of longitude = 15.50 m or 50.85 ft


if we say the uk lies between a latitude of 50 and 60 degrees (roughly) we can see from the info above that on the south coast (50 degree latitude) 1 degree of longitude = 44.55miles but at the north of scotland (60 degree latitude) 1 degree of longitude = 34.67miles which is a diference of 10miles and would obviously make the search in accurate at somepoints of the country??

I'm not knocking your code i'm just playing devils advocate as i have no prior understanding of this subject and just trying to get up to speed, your responce has been much appreciated!

[eluser]mattpointblank[/eluser]
Ah, I didn't realise this - my code was only used within 1 city, so accuracy was pretty solid, but I never tried it in two extremes, good spot! Hmm, it's a tough one. Maybe there's an algorithm to work out the degree of change (no pun)? Maybe it's worth exploring Google's distance calculation stuff, they have an API for it and their data is probably better than anything we could assemble.

[eluser]JamesTaylor[/eluser]
I've been doing some more searching and came across this:

http://code.google.com/apis/maps/article...earch.html

its appears to be exactly what i want to achieve and the formula looks very scary for calculating the radius so i think it will have to be taken on face value that is accurate! i think it will take a good few hours for me to go through as i like to try and understand whats going on, and all this is new to me! not used googles api's before or search by distance so it should be interesting!

Thanks again

[eluser]jedd[/eluser]
Have you considered / written off the geometry types available within MySQL?

I looked at the briefly a while back, as they promised some simplicity when it came to functions such as calculating the distance between two points, but got the impression it wasn't hugely well supported (or indeed documented) at the time.

[eluser]JamesTaylor[/eluser]
Jedd,

i haven't written them by any means... i actually don't know anything about them! The purpose of my post was to try and get some suggestions as to things i should be looking at with regard to planning how i am going to achieve my aims.

Can you provide some further info about the MySQL geometry type and i will do some further reading for definate, even if i don't end up using them its worth being aware they exist... i'm quite new to php / mysql side of webdesign and don't get to do as much of it as i do static xhtml / css so i have holes in my knowledge!

Often its not actually knowing how to do something that matters but more knowing that something is possible because then at least you know you can go and research it!

[eluser]jedd[/eluser]
I looked at using the native types a while back, for my current pet project, but it was a non-urgent module, so I put the whole thing off. I want to have global addressable geometry (spatial data) so I can refer to any place on the planet, as well as independent intra-site geometry information (where site is some nominated area, usually a few square kilometres, down to a square metre or so - with a hierarchy of ownership of sites within the scheme). It all got a bit much for my brain.

Anyhoo, if you do some intergoogling on 'mysql spatial' you'll get a few hits, and the first place to start (though I found it way confusing - probably because I didn't know what I really wanted to achieve, but partly because the documentation on this section seems a bit immature compared to the rest of the manual) is the [url="http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html"]Spatial Extensions to MySQL[/url] chapter.

[eluser]jedd[/eluser]
Oh, btw, if you're using UK postcodes, and you haven't heard .. wikileaks published the full postcode index a month or two ago, with much fanfare from the freedom-of-information mob.

The postcode database - compiled at taxpayer expense - was only available if you paid even more money .. hence there wasn't much moral outrage to be mustered about this leak from the UK government.

[url="http://wikileaks.org/wiki/UK_government_database_of_all_1,841,177_post_codes_together_with_precise_geographic_coordinates_and_other_information,_8_Jul_2009"]Wikileaks link here[/url] if you're interested and didn't know about it.