How do I find the records within a specified distance?

Question

How do I find the records within a specified distance?

drew.403

SSC-Addicted

Points: 418
More actions
January 8, 2010 at 12:23 am

#219084

I'm pretty new to SQL Server 2008. I've really been racking my brain to try and figure this out. The only examples I can find are all about converting a ZIP code to lat/long.
What I'm trying to do is to find all the records within a specified distance. I have the coordinates set in an geography field already.
When the user adds their coordinates I run a very basic sql statement to add them. here is the code I use.
sql = "UPDATE testing SET [coords] = geography::Point(52.029736, -113.973541, 4326) WHERE id=2"
What Im looking for ideally would be a way to just get all the records where this "coords" field is relatively close to the supplied coordinates.
I'm hoping there is an actual SQL statement that can be created on the fly to find the records. for example i could imagine something like this working... but I don't have the expertise to make it.
SELECT * FROM testing WHERE distance(coords,geography::Point(53.029736, -113.973541, 4326)) < 10 ORDER BY distance
If it helps, in english the statement would be;
Select all records where the distance between my coordinates is less than 10km from the coordinates saved in the record
I know this is no where close to what i need, I'm hoping someone could point me in the right direction? pretty, pretty, please, i'm running out of hair to pull out.. lol.
Thanks in advance
Drew

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

I think you just need the STDistance method. The following will help you:

http://blogs.msdn.com/pekorica/archive/2009/03/16/calculating-a-distance-between-two-point-of-interest-using-sql-server-2008-geography-data-type.aspx

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-2008-proximity-search-with-th

http://technet.microsoft.com/en-us/library/bb933808.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

drew.403 SSC-Addicted Points: 418 More actions · Answer 2

Thanks for your help, but those links where the ones I was trying to figure out. They are either zip code translations which completely throws me off, or are declaring the geography out of lat/longs.

Each one has numerous steps, which I'm hoping to avoid.

Ultimately I would like to end up with a statement like this one;

SELECT h.*

FROM zipcodes g

JOIN zipcodes h ON g.zipcode <> h.zipcode

AND g.zipcode = '10028'

AND h.zipcode <> '10028'

WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(20 * 1609.344)

However in this example they've declared the @h and @g, when in my case those fields already exists;

DECLARE @g geography;

DECLARE @h geography;

SET @h = geography::STGeomFromText('POINT(-77.36750 38.98390)', 4326);

SET @g = geography::STGeomFromText('POINT(-77.36160 38.85570)', 4326);

I already have the fields in the table in the geography format created by updating the table with this command for both the record's coords I'm using to search and the ones I'm comparing to.

sql = "UPDATE testing SET [coords] = geography::Point(52.029736, -113.973541, 4326) WHERE id=1"

sql = "UPDATE testing SET [coords] = geography::Point(52.029736, -113.973541, 4326) WHERE id=2"

thanks again,

Drew

Paul White SSC Guru Points: 150467 More actions · Answer 3

drew.403 (1/8/2010)
I'm hoping there is an actual SQL statement that can be created on the fly to find the records. for example i could imagine something like this working... but I don't have the expertise to make it.
SELECT * FROM testing WHERE distance(coords,geography::Point(53.029736, -113.973541, 4326)) < 10 ORDER BY distance
If it helps, in english the statement would be;
Select all records where the distance between my coordinates is less than 10km from the coordinates saved in the record

Drew,

Would something like the following not work for you?

SELECT*

FROMdbo.Testing

CROSS

APPLY(GEOGRAPHY::Point(53.029736, -113.973541, 4326).STDistance(coords)) CA (distance)

WHERECA.distance < 25000

ORDERBY CA.distance ASC;

I confess you lost me slightly with your last post, but I hope this helps.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sonealtx SSC Journeyman Points: 79 More actions · Answer 4

Using your example couldn't you do something like this

SELECT h.*

FROM zipcodes g

JOIN zipcodes h ON g.zipcode <> h.zipcode

AND g.zipcode = '10028'

AND h.zipcode <> '10028'

WHERE g.GeogCol1.STBuffer(20 * 1609.344).STIntersects(h.GeogCol1)

--STBuffer would create a 20 mile buffer around g.GeogCol1 then the STIntersects would return h.GeogCol1 points that fall within that 20 mile radius.

drew.403 SSC-Addicted Points: 418 More actions · Answer 5

Here is what I used, since I don't have a Zip relationship, and the current lat/lon is alreayd known to the script.

distcheck = "100"

lat = "39.023445"

lon = "-113.23444"

sql = " SELECT * FROM locations WHERE coords.STDistance(geography::Point(" & lat & ", " & lon & ", 4326)) < " & (cdbl(distcheck) * 1000)+1 & " ORDER BY ID"

The result selects records that are less than 101km away from the originating location.

Paul White SSC Guru Points: 150467 More actions · Answer 6

Cool. Thanks for the feedback Drew.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi