Getting all Zip Codes in a given Radius(km) from given Zip code

Question

3.00/5 (2 votes)

See more:

hi,
i have my database with all Zip codes, latitudes and long address, here my requirement is,
if i given Zip code and Radius , i need to get all Zip Codes in that Region from My database only.
i searched it, but i got google API from https://developers.google.com/maps/documentation/javascript/tutorial[^],
i don't need API , i just want my results from my SQL database table only.

Any Ideas,
Thanks In advance

Posted 6-Dec-13 0:09am

dorababu407

Updated 6-Dec-13 0:20am

Richard MacCutchan

v2

Add a Solution

Comments

Richard MacCutchan 6-Dec-13 6:21am

You need to write a query command to your database.

dorababu407 6-Dec-13 6:29am

how can we write command for convert given radius to latitude and longitude .
my input is ZipCode and Radius(km) only.
in my database table has four fields, 1)ZIPCode 2)latitude 3) longitude 4)StateAbbreviation
how can i write code for this ? plz help me

Richard MacCutchan 6-Dec-13 7:00am

Sorry, but I have no information on how zip codes relate to latitude and longitude. You need to do some research on the subject.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wombaticus · Accepted Answer · 2013-12-06T01:26:00

Solution 1

reslts in miles

SQL

SELECT id, ( 3959 * acos( cos( radians(YOUR_LATITUDE) ) * cos( radians( YOUR_DB_LAT_FIELD ) ) * cos( radians( YOUR_DB_LNG_FIELD )
- radians(YOUR_LONGITUDE) ) + sin( radians(YOUR_LATITUDE) ) * sin( radians( YOUR_DB_LAT_FIELD ) ) ) ) AS distance
FROM YOUR_DB_TABLE HAVING distance < 25 ORDER BY distance ASC;

YOUR_LATITUDE and YOUR_LONGITUDE are where you enter the values of your ZIP code, YOUR_DB_LAT_FIELD and YOUR_DB_LNG_FIELD are the fields containing the longitude and latitude values for each ZIP code in your database, and finally YOUR_DB_TABLE is the database table you are querying. “distance < 25″ tells the database to only return values that are less than 25 miles away. You can modify or omit this to return all.

Posted 6-Dec-13 1:26am

Wombaticus

Comments

dorababu407 6-Dec-13 9:37am

our values for latitude and longitudes are in vachar type, can you please suggest me how to convert them from varchar to geography or how to query the varchar type values with the above query. we are not even able to run the sql query. it is giving invalid columns as my datatype are in varchar type. please let me know a solution to the problem. Thanks

Wombaticus 6-Dec-13 12:00pm

Well it depends on what database you're using but most will have a CAST or CONVERT function - eg
radians(convert(YOUR_LATITUDE as float)) (or whatever)
etc

Wombaticus 6-Dec-13 12:04pm

If I was you, I'd convert the lat/long fields in my database to a numeric format, and save the problem of converting them in the SQL query.

dorababu407 6-Dec-13 11:52am

what is 3959 in the query?

Wombaticus 6-Dec-13 12:00pm

it's just a constant - it's the way the maths works out.

dorababu407 6-Dec-13 12:08pm

i just changed the type latitude/longitude to decimal(9,6). i got result for your command, but i think it was not accurate, because it showing distance above 600 miles.

here is the cmd

SELECT zipcode, ( 3959 * acos( cos( radians(94595) ) * cos( radians(latitude ) ) * cos( radians(longitude)
- radians(94595) ) + sin( radians(94595) ) * sin( radians(latitude ) ) ) ) AS distance
FROM dbo.ZIP where (( 3959 * acos( cos( radians(94595) ) * cos( radians(latitude ) ) * cos( radians(longitude )
- radians(94595) ) + sin( radians(94595) ) * sin( radians(latitude ) ) ) ) < 7500) ORDER BY distance ASC

Wombaticus 6-Dec-13 12:12pm

well your query is completely different from what I gave you, so I can't comment.... why do you have such a complex WHERE clause? You don't need a WHERE clause at all, just the HAVING one I gave.

dorababu407 6-Dec-13 12:17pm

same query i write it again becoz of HAVING clause is not worked for
distance comparision(HAVING distance < 25).

here is the query

SELECT zipcode, ( 3959 * acos( cos( radians(94595) ) * cos( radians(latitude ) ) * cos( radians(longitude)
- radians(94595) ) + sin( radians(94595) ) * sin( radians(latitude ) ) ) ) AS distance
FROM dbo.ZIP

Wombaticus 6-Dec-13 12:25pm

I don't understand - it won't work using a WHERE clause instead of a HAVING one.

dorababu407 6-Dec-13 12:30pm

if having clause is used, it shows error:
Invalid column name 'distance'

here is the same query with having clause

SELECT zipcode, ( 3959 * acos( cos( radians(94595) ) * cos( radians(latitude ) ) * cos( radians(longitude)
- radians(94595) ) + sin( radians(94595) ) * sin( radians(latitude ) ) ) ) AS distance
FROM dbo.ZIP HAVING distance < 25 ORDER BY distance ASC

Wombaticus 6-Dec-13 12:36pm

What database are you using? It seems most odd giving that error when (if) you have specified "AS distance" in the query

dorababu407 6-Dec-13 12:39pm

Sql Server 2008R2

Wombaticus 6-Dec-13 12:42pm

hmm.. ok, well in that case try that same query just replacing HAVING with WHERE:

SELECT zipcode, ( 3959 * acos( cos( radians(94595) ) * cos( radians(latitude ) ) * cos( radians(longitude)
- radians(94595) ) + sin( radians(94595) ) * sin( radians(latitude ) ) ) ) AS distance
FROM dbo.ZIP WHERE distance < 25 ORDER BY distance ASC

dorababu407 6-Dec-13 12:56pm

just for know forgot about WHERE and HAVING.
i just removed the distance comparasion in the query.
Now iam getting results all Zipcodes and distances from the ZipCode i given.
whatever the Zip Code i am giving it shows distnace in above 500 km.

here is the query:
SELECT zipcode, ( 3959 * acos( cos( radians(94595) ) * cos( radians(latitude ) ) * cos( radians(longitude)
- radians(94595) ) + sin( radians(94595) ) * sin( radians(latitude ) ) ) ) AS distance
FROM dbo.ZIP

here i gave Zipcode as 94595, in the results it showing distance 11355 km for the behind zipcode 94596.

Wombaticus 6-Dec-13 13:02pm

DId you try my last suggestion above? I don't have or use SQLServer, so can't run a direct comparison, but all I can say is that the query I have works for me, and according to the documentation SQLServer treats HAVING just like WHERE when used without a GROUP BY clause (as is teh case here) so it really ought to work for you.

dorababu407 6-Dec-13 13:08pm

ya, i tried it but it shows the same error:
Invalid column name 'distance' .

why the distances is showing above 600 km from a given every Zipcode?

dorababu407 6-Dec-13 13:15pm

Here is the link for Results of my query
https://www.dropbox.com/s/zibpn3ptq0keaws/ZipCodes.txt

i think query calculating distance not exactly from given ZipCode,
that's why it showing distance very large values

please check it once

Wombaticus 6-Dec-13 13:33pm

I don't understand what numbers you are using in your query... you are supposed to enter the latitude adn longitude. 90260 is not a valid number for any latitude - it only goes from -90 to +90, and longitude from -180 to +180

Wombaticus 6-Dec-13 13:36pm

If you want to find zipcdes within 25 miles of zipcode 90260, you need to first get teh latitude and longitude of 90260, adn use those in the query in place of YOUR_LATITIUDE and YOUR_LONGITUDE

Wombaticus 6-Dec-13 13:43pm

Suppose 90260 has
latitde = 34.534
longitude = -110.245
then your query would be

SELECT zipcode, ( 3959 * acos( cos( radians(34.534) ) * cos( radians( latitde ) ) * cos( radians( longitude )
- radians(-110.245) ) + sin( radians(34.534) ) * sin( radians( latitde ) ) ) ) AS distance
FROM dbo.ZIP HAVING distance < 25 ORDER BY distance ASC

(Take out the distance and having clause if ytou want to test it for all values)

dorababu407 7-Dec-13 2:09am

Thank you, for spending lot of time for my questions . its really helpful.
Do u have any blog? if there please give me URL

Wombaticus 7-Dec-13 3:51am

You're welcome - do you have it working now? No, I don't have a blog... I'm too busy for that! :) (Or maybe I'm just too slow - othe busy people seem to manage ti...)

dorababu407 7-Dec-13 4:09am

yes, its working fine now.
you are not slow and you are good in managing time by answering questions and doing your work.
Thank you..
can i have your EmailId ?

Member 15650945 26-May-22 7:01am

its working perfectly fine , But I want to know how this is happening . can you please explain or you can share a link where they explained this formula.