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.
What's Your Theory (or Experience) Doing Proximity Searches?
Moderator: General Moderators
- 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?
I guess none. Okay.
- 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?
Could you do something with google maps?
- 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?
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?
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?
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 proximityHockey 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.
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"
;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
- 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?
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.