CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sree
Sree

Reputation: 2922

How to get all other postcodes or (latitude & longitude) from table near by given postcode and given radius in miles in sql server?

I have table with latitude,longitude and postcodes. I want select the records which are near by given postcode and radius in miles. e.g user will give inputs postcode = 'NW44JL' and radius = 1 mile. I need to get all the records from the table with in 1 mile radius of postcode 'NW44JL'. Can anyone help me to get this. Thanks

Upvotes: 5

Views: 1008

Answers (2)

Habeeb
Habeeb

Reputation: 1040

Declare @radius int
--Radius for circle
set @radius=1

select distinct tb1.postcodes from table1 tb1
join table1 tb2 on (Power((tb1.latitude - tb2.latitude),2) + POWER((tb1.longitude - tb2.longitude),2)) < POWER(@radius,2)
where tb2.postcodes='NW44JL'

table1 is your table name

consided latitude and longitude in miles. If not need to convert to miles

Upvotes: 2

twoleggedhorse
twoleggedhorse

Reputation: 5048

For this you'll want to use the built in SPATIAL datatypes:

MSDN (Designing and Implementing Spatial Storage (Database Engine))

Try the following tutorials to get you started, written by the user @SQLMenace:

SQL Server Zipcode Latitude/Longitude proximity distance search

and

SQL Server 2008 Proximity Search With The Geography Data Type

Upvotes: 1

Related Questions