Currently, I am using the following query: SELECT DISTINCT o.szZipCode FROM tblZips z,tblZips o WHERE z.szZipCode=$szZip AND (3956 * (2 * ASIN(SQRT(POWER(SIN(((z.dblLat-o.dblLat)*0.017453293)/2),2) + COS(z.dblLat*0.017453293) * COS(o.dblLat*0.017453293) * POWER(SIN(((z.dblLon-o.dblLon)*0.017453293)/2),2))))) < $iRadius
where $iRadius is the Radius of search and $szZip is the zip code (char) This runs rather slowly over the 76 thousand zip codes in the US. Here are my three solutions: 1) Use a subquery (not an option on 3.23) 2) Compute the max/min latitude/longitude and then query on that range (proximity becomes a square, but that's not a big deal) 3) Since I am using only 3 radius (10,25,50), compute for those three ranges over the entire table and make a new table (I guess around 1.5 million records will be created, so around 350 MB of space) Does anyone have any recommendations (or a better query that doesn't have to use a function for every row). ----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+------------------------------ ------------------------------+-------+ | szZipCode | varchar(5) | YES | MUL | NULL | | | szCityName | varchar(28) | YES | | NULL | | | szState | char(2) | YES | | NULL | | | szCounty | varchar(25) | YES | | NULL | | | szURL | varchar(255) | YES | | /cgi-bin/showPage.cgi?szNextPage=placead.html&szAction=NEW | | | dblLat | decimal(4,2) | YES | MUL | NULL | | | dblLon | decimal(4,2) | YES | MUL | NULL | | +------------+--------------+------+-----+------------------------------ ------------------------------+-------+ 7 rows in set (0.00 sec) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
