BIG Database: input please
Posted 01 May 2003 - 03:21 AM
I\'m developing a personals site whereby users can search profiles using zip codes.
One of the most important features of this site will be the ability to search for profiles within a certain mile radius of a zip code.
e.g. I am an x looking for a y within a z mile radius of \'A\'
(where A is the searchers ZIP CODE)
I have a zip code database which consists of 42,000 rows each entry has
Rather than put a huge stress on the server by calculating distances between A and B on the fly, I thought I would create a matrix whereby each unique zip code would be cross-referenced with every other unique zip code. A distance in miles (as the crow flies) would be calculated using PHP (Long,Lat) and inserted for each unique combination of 2 zip codes.
TABLE would resemble something like.
IF I have 42,000 unique zip codes,
1. How many rows will I end up with (approximately)?
2. Is this the best approach?
I envisaged that queries where:
ZIP1=\'A\' AND DISTANCE<z
would work quite well?
Any input greatly appreciated.
(I\'m in the UK, dont know much about zip codes, are there really 42,000 or do I have too many!?)
Posted 02 May 2003 - 04:32 AM
Posted 05 May 2003 - 08:13 AM
You have 42000 cities and apparently a lot of UK cities have identical zipcodes or UK has 42000 unique zipcodes !! (which wouldn\'t surprise me, no offense )
select distinct ZIPCODE from TABLE_42000_row;
It will output all the unique zipcodes...
About the rest: It is probably a good idea to calculate it once and store it (remember to index it on the distance, speeds things up considerably when having 882 million rows... ) Also, note that this is only half the matrix, so you would have to select it like
select * from distances where zip1 = $ZIP or zip2 = $ZIP and distance < $distance....
But it depends a little bit on the structure of the DB, if you have 42000 unique zips, no problem - otherwise you\'ll have to define the \"center\" of a zipcode and then do the math....
Alternatively you could probably do something like
select (arccos(cos($a1)*cos($b1)*cos(LATITUDE)*cos(LONGITUDE) + cos($a1)*sin($b1)*cos(LATITUDE)*sin(LONGITUDE) + sin($a1)*sin($a2)) * 6378) as distance....
6378 is the earth radius in km, hence the units are km.
I\'m not sure if mysql supports arccos - but it would probably be too slow anyway...
Posted 05 May 2003 - 08:26 AM
1. convert lat, long to decimal format
2. convert to radians
3. perform the calculation...
$radians = $degrees*(pi/180); (eastern or north)
$radians = -1 * $degrees*(pi/180); (western or southern)
... this gets too heavy for mysql.... you should do the precalculations, remember to index it on zip1 and zip2 - since they are defining the subset you want to search for distances < something...
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users