Re: Zip Code Proximity search - Examples II

[Thomas Butler]

I really like this approach of using a bounding rectangle - very straightforward and simple - I'm working on implementing this in my solution.

A Question though - my geocode zip code database has lat/long coordinates as (see last 2 values in each row represent lat/long):

Aurora","CO","80010","39.7373","-104.8639",
"Austin","TX","73301","30.2669","-97.7429",

From your step #3 - it seems that I will need to convert these lat/long coordinates so that I can do a radius search using miles as the distance units? For example, the user will provide a zip code, select find 'within 10miles' or find 'within 25miles' - I will look up the lat/long for this zip - then I will create the rectangle based on your formula (X + (8 + R)) - However, I can not add 39.7373 (lat) + (8miles + 10miles) because the units for latitude do not represent miles and adding 18 to 39.7373 will not give the correct result - How do I do the conversion here? i.e., do I convert the lat/long values? do I convert miles to the lat/long units? what is the formula?

Thanks for your help


SELECT *
FROM addresses addr
JOIN zip_table zip ON addr.zip_code = zip.zip_code
WHERE addr.business_type = 'drug_store' -- or whatever
AND addr.expensive = 'F' -- or whatever
AND addr.sells_viagra = 'T' -- or whatever
AND zip.lat >= ... AND zip.lat <= ...
AND zip.long >= ... AND zip.long <= ... \

p