4 Replies Latest reply on Jan 10, 2007 12:25 PM by Newsgroup_User

# Longitude and Latitude to search by zip

I found the formula that calculates distance with zip codes. Where do I get
the 2nd long and lat for the formula? Do I add the distance (25 miles) to
both long and lat to get the 2nd set of variables? Or do I use the base long
and lat add and subtract to get the 2 sets?

Has anyone done a search using zip codes to determine everything within a
specific range (miles / kilometers)?

And when I get the result of the formula, how does it apply to getting a
range of zip codes? And how to I query using the result.

Thanks

--
Wally Kolcz
MyNextPet.org
Founder / Developer
586.871.4126

• ###### 1. Re: Longitude and Latitude to search by zip
I entered "Zip Code Radius" into google and I am finding many products
and services that will do this for you. Haven't found a do it yourself
version yet.
• ###### 2. Re: Longitude and Latitude to search by zip
Ok that did not take too long.

Building a Zip Code Proximity Search with ColdFusion
[ColdFusion Developer's Journal]

Proximity Searches for Fun and Profit [Webmonkey]
http://www.webmonkey.com/webmonkey/05/32/index4a.html?tw=programming
• ###### 3. Re: Longitude and Latitude to search by zip
see if this will work for you:

SELECT b.zipcode, b.state,
(3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2))))) AS distance
FROM zipcodes a, zipcodes b, zipcodes c
WHERE
a.zipcode = '02134' AND # <-- Your starting zipcode
a.zipcode = c.zipcode AND (3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
GROUP BY distance
• ###### 4. Re: Longitude and Latitude to search by zip
Took a lot of tweeting and work, but I got it to finally work, Thank you so
much!

I had to edit the cfc to work (the SQRT function was spelled out SQUARE and
gave me fits) and I had to capture and convert a whole new set of zip codes,
longitudes and latitudes and convert them into the radiuses. But all in all
it finally worked. The database on the example is written for MS SQL and I
am using MySQL. Each enough to do. All I had to do is take the text file,
add headers to the data, save it as a CSV, take it using CFhttp and loop it
into the MYSQL database while converting the longitude and latitude into
'rlongitude' and 'rlatitude' which the CFC requires to make it work.

Thanks again Ian. I still want to add you to my site with a linkback if you
want it.

If anyone wants the modified CFC, application.cfm, and a MYSQL script for
the zipcodes of the US (and beyond). Let me know

wkolcz (at) mynextpet (dot) org