Page 1 of 1

What's Your Theory (or Experience) Doing Proximity Searches?

Posted: Wed Aug 27, 2008 10:04 pm
by volomike
I've been asked to consider a project in the UK that requires proximity searches. I live in the USA, and the postal code system and geographical layout of the UK is just a plain oddity to me. It's a miracle people can drive anywhere there or even remember someone's phone number. And I say that kindly, of course, because I like my UK clients. (I hope they can understand.)

Basically the search just needs to be like "search for a job within x number of miles away". It then sorts jobs by closest mileage to farthest mileage. It knows mileage by their postal code.

So, how do I pseudocode that out? What database table or API do I need to purchase for this? How do I integrate it? I see lots of options on the web, but I'm wondering what you think is best.

Re: What's Your Theory (or Experience) Doing Proximity Searches?

Posted: Fri Aug 29, 2008 7:55 pm
by volomike
I guess none. Okay.

Re: What's Your Theory (or Experience) Doing Proximity Searches?

Posted: Fri Aug 29, 2008 8:11 pm
by allspiritseve
Could you do something with google maps?

Re: What's Your Theory (or Experience) Doing Proximity Searches?

Posted: Fri Aug 29, 2008 10:24 pm
by Christopher
Yes, Google Maps or some similar service should be able to provide longitudes and latitudes for each address.

Re: What's Your Theory (or Experience) Doing Proximity Searches?

Posted: Tue Sep 02, 2008 5:11 pm
by alex.barylski
I triple that...use Google to GeoCode the address (street, postal, whatever) and use great circle distance formula to determine precise distances between two way points.

Re: What's Your Theory (or Experience) Doing Proximity Searches?

Posted: Fri Sep 05, 2008 9:02 pm
by josh
Hockey wrote:I triple that...use Google to GeoCode the address (street, postal, whatever) and use great circle distance formula to determine precise distances between two way points.
Assuming your table has fields `lat` and `lon`, containing the said lat lon of each entry you're searching, and assuming the variables $lat and $lon correspond to the lat / lon at the center of the proximity

Code: Select all

$zip_range = ($zip) ? 
        sprintf(
            " ( POW((69.1 * (`lon` - %2\$s)*cos(%1\$s / 57.3)),2)   + POW((69.1 * (`lat` - %1\$s)),2))  <   (%3\$s) ",
            (float)$lat,
            (float)$lon,
            (int)pow($_GET['radius']?$_GET['radius']:10,2)
        )
    :
        "1"
    ;
Include this in your where clause. All that math is absolutely necessary for an accurate radius search, due to the fact youre looking for a radius on the surface of a sphere, the greater the radius you search the more inaccurate your proximity would be if you don't take that into account. This is how I did it for http://www.marinas.com

youd use it by issuing a query like
SELECT * FROM `records` WHERE $zip_range

You can lookup the lat lon for a zip code using widely available data found on google. Theres free and paid versions if your application is serious you'll want to find a service that gives you regular updates when the post office updates their info

Re: What's Your Theory (or Experience) Doing Proximity Searches?

Posted: Sat Sep 06, 2008 8:23 am
by volomike
Fantastic, jshpro. I'll give it a whirl. I don't know if we'll receive lat/lon codes from the data -- I think we're getting postal codes with nearby postal codes. But I'll find out soon. This project is on hold right now until I knock out two other projects, and then I'll start to review what my client has found for me as far as the db to be used on proximity searches.