Date: Mon, 21 Feb 2005 16:14:05 +0000 To: <mysql@lists.mysql.com> <mysql@lists.mysql.com> <mysql@lists.mysql.com> From: Peter O'Brien <[EMAIL PROTECTED]> Subject: Postcode Proximity Search? Message-Id: <[EMAIL PROTECTED]>
Hi I'm just starting out with PHP and MySQL My database has a postcode column and I want to be able to run a search (using a part of full postcode) to return results in order of proximity within a given range. If anyone could point to a decent tutorial / resource, or give me any advice or hints I would greatly appreciate it. Pete. ____________________________________ Sure, you can do that. First, go to the US Census Bureau's website and downloadthe file 'places.zip' (Download places.zip from http://www.census.gov/tiger/tms/gazetteer/places2k.zip).". This file contains the zip codes, latitutude and longitudes of cities in the United States. One thing you should be aware of is that the zip codes in this file do not fully correspond to the US Postal Service codes, but they are darn close. If 'darn close' is not good enough, you can pay the USPS to give you their current zip codes. The USCB files are free to all, though. Next, study the formula at "http://www.meridianworlddata.com/Distance-Calculation.asp"; (this site is very well-written). The "Great Cirlce" forumla is the one you want, but read the whole thing. Now, set up a script that implements the formula and the information contained in places.zip. You said you wanted to "return results in order of proximity within a given range". If, by that, you mean relative to a number you feed the query script, the above works great. If, however, by some off chance, you have a certain fixed point that you want all your ordering to be based around, say New Orleans, then you could simply pipe the entire places.zip file through a calculation and stick the results in the database. I'm guessing, though, that is not what you had in mind. One last note: The Great Circle formula assumes a smooth, perfectly round earth. So, if you go up Pike's Peak and down again, you add 5.3 miles to you trip, but the Great Circle formula will not reflect this because altitude is not used. Hope this helps! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
