On Fri, November 3, 2006 9:00 am, Ben Liu wrote:
> I'm trying to build some functionality commonly seen on the web where
> a user enters a zip code and they are provided with a listing of
> business or entity locations sorted by geographical distance. I've got
> a client with a distributor network and I need to create something
> like this for them. They want an end user to be able to enter a zip
> code and have a list of closest distributors be listed. Anyone know of
> any open source, free code to get this done. I have a zip code
> database and can perform a query on it and return the relevant
> records. I can even associate distributors with certain zip codes, but
> the whole geographical distance thing is beyond me. Anybody do this
> before and have advice? Thanks for any guidance here.
You can get the Lat/Long for the zips through any of the following
resources:
TIGER data (old, but you can just import it and be done)
Google Maps
Yahoo! Maps
I'm sure there are more free resources, and I *know* there are $$$ ones.
Doing the distance search gets a bit hairy in the trigonometry, but
you can find formulas on-line. *OR* if you only want the "closest"
distributor, you can just use the overly-simplistic (and much faster)
formula:
abs(lat1 - lat2) + abs(long1 - long2)
This is a Cartesian distance, and will go haywire at the North and
South poles -- Like you give a [bleep] at the North and South poles...
The zips table is HUMUNGOUS. For performance reasons, I *strongly*
encourace you to:
alter table distributors add latitude default null;
alter table distributors add longitude default null;
Now set up a "cron" job that finds NULL latitudes in distributors
LIMIT 100, and then looks up the zip in the zips table, and copies the
lat/long over to the distributors table.
Also be sure that if a zip changes on a distributor, that you NULL out
the lat/long -- You can do it with a fancy trigger, or with your
business logic in your application.
Your basic search logic then becomes:
require 'connect.inc';
$_CLEAN['zip'] = preg_replace('/^[0-9]{5}$/', '', $_REQUEST['zip']);
$q = "select latitude, longitude from zips where zip = '$_CLEAN[zip]'";
$r = mysql_query($q, $connection);
list($lat, $long) = mysql_fetch_row($r);
$q = "select id, abs(latitude - $lat) + abs(longitude - $long) as close";
$q .= " from distributors ";
$q .= " order by close ";
$q .= " limit 1 "; //or more, to give user some freedom of choice
Yes, I chose to do 2 queries for the lat/long lookup and distance
search instead of a JOIN between zips.
The zips table has ~O(100K) entries.
Your distributors table has, whatever, maybe ~O(1K) entries.
You do *not* want to JOIN 100K X 1K and slam the db.
You *might* have more luck than I did if your db is fancy enough and
you can use LatLong as a geo-spatial datatype and *if* your DB has the
ability to index a LatLong geo-spatial datatype...
But in *my* experience, you just end up killing the DB if you try
that, and 2 dirt-simple queries with mindless indexed fields was way
more better.
YMMV
There are *much* fancier ways to do this with a million-dollar budget.
They might even be better solutions.
If you have a fair amount of time on your hands, and want a REALLY
cool interface for your client, you can do a Yahoo! maps mash-up in
about an hour of work, just from reading Rasmus' tutorial on his toys
page.
http://toys.lerdorf.com/
If you've got several more hours to spare, providing a Google map as
an alternative is also quite do-able, though not as quick-n-easy as
you have to walk the XML output in Javascript "by hand" instead of
letting a built-in function do it for you.
Then you just need to spend a day or two figuring out how to get the
dang maps to zoom in and focus on the zip code point you want. That
has a couple gotchas I've just stumbled through myself, but can't
really express yet in short form...
--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some starving artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
