Places in a database within x miles of zipcode

[chadrt]

I have written some simple queries in PHP and I have even gone thru and changed code in prewritten scripts to make things more suited to me but this is one area that I really lost in now.  The only way I can think to do this is to query every single entry in the DB (MySQL) and then run the calculation to assign distance values and then list the ones less than a given radius.  This seems very processor intense and even difficult to do.  Although I have many stores that have the ability to do this and I am very intrigued as to how I might program this.  Any help would be great!!  Thank you...

 

Chad

[chadrt]

This is still a perplexing little issue of mine!  I have found a package that does this sort of thing but uses flat files and it is dreadfully slow so I am back at square 1 again.  I have found many closely related scripts but they rely on a FULL store locator system where the entire scripts are based on a system that was especially desiged for storing the location information of stores etc.  But not to retrieve my lat's and long's from a db and compare them to a lat and long provided by a zipcode directory.  Then list the ones within x number of units (miles, kilometers, nautical miles etc.) from that zip code.  Any suggestions would be great.  Thank you...

[chronister]

Store Locator scripts still baffle me too.  It sounds like you need a db of zips with their corresponding latitude / longitude. You would determine the lat/lon of a particular zip. Then you would have to run a query to return all zips within a given distance of said zip. The real tricky part I think is converting miles distance to degrees in the lat/lon coords.

 

Probably not much help, but maybe a step in the right direction.

 

Nate

 

 

[chadrt]

I have a DB of zips with lats and longs!  And my locations all have lats and longs.  Well at least all of them that will be factored into the distance search.  I even found a real simple function that will perform the calculations.  But I am a little confused about how to do this with some speed.  The only way I can think to do this is to write a script that will check every single lat/long combo in the locations DB and create an array that also contains the newly calculated distance to each place in the entire database.  Then sort by that distance and only show the ones that have xx miles or less!  But alas I have no idea how to put something outside the DB into the array and it seems like calculating every single entry in the db would be very time consuming.  Now that I have it all spelled out on here maybe I can get back to the editor and see if I can come up with something.  If I do I will post it back here for the rest of the community to use.  Thank you.

 

Chad

[MmmVomit]

You could come up with a scheme of assigning zip codes into larger areas based on their location.  This way, if you're only looking within 100 miles, your query can select all zip codes that fall into the larger areas possibly within 100 miles.  This way if your location is in Delaware, you don't have to do the calculation on zip codes in California.

[BlueSkyIS]

i have a script that does this using a database of zip codes with lat/lon for each zip code:

 

for the latitude and longitude of the search zip code, add and subtract 2 degrees, then find all zip codes that are inside this box, ala "SELECT FROM some_table WHERE lat < $high_lat AND lat > $low_lat AND lon < $high_long AND lon > $low_long". This greatly reduces the number of zip codes that you have to test for distance. Now loop over the results, measuring actual distance from the result lat/lon to the search lat/lon. That's math that you're probably already using.

[effigy]

GIS, perhaps?

[BlueSkyIS]

wow, i was unaware of GIS in MySQL. Thanks for the tip!