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

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

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

Post 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.
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

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

Post by volomike »

I guess none. Okay.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

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

Post by allspiritseve »

Could you do something with google maps?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post by Christopher »

Yes, Google Maps or some similar service should be able to provide longitudes and latitudes for each address.
(#10850)
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

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

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

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

Post 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
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

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

Post 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.
Post Reply