Search for a geography point within a different range - SQL Server

Question

I have a table in SQL Server that has a geography data column. I want to query this table to find rows that are approaching (within the range) of another given geography point. Anyone have any ideas on the best way to do this? I have 2 options where to make this type of request. I can write it on sql server as a stored procedure, or I can do it in C # code since I use Entity Framework to access data.

I will have a query with a range (e.g. 100 m) and a geography point passed to it. The pseudocode will be something like this ...

select rows where rows.geo within a given geography point

I'm having trouble finding geographic query examples in SQL Server on the Internet.

Any help would be appreciated.

Answer 1

Assuming you have lat and long dot values ​​in db.

select * from yourtable where SQRT ( POWER((yourtable.lat - reflat) * COS(reflat/180) * 40000 / 360, 2) + POWER((yourtable.long - reflong) * 40000 / 360, 2)) < radiusofinterest 

reflat and reflong are the point from which you want to know close places. radiusinterinterest is the distance from this point. 40,000 is the circumference of the earth. you can use more accurate numbers.

I havent checked the syntax with SQLServer though .... so there might be some errors.

cos (reflat) adjusts the circle based on the lat you are in. It should work fine at shorter distances.

Answer 2

You are already using SQL Server spatial and geographic columns, so you can use the following to get the result. There are two ways:

Using STDistance ():

 -- Get the center point DECLARE @g geography SELECT @g = geo FROM yourTable WHERE PointId = something -- Get the results, radius 100m SELECT * FROM yourTable WHERE @g.STDistance(geo) <= 100 

Using STBuffer () and STIntersects

 -- Get the center buffer, 100m radius DECLARE @g geography SELECT @g = geo.STBuffer(100) FROM yourTable WHERE PointId = something -- Get the results within the buffer SELECT * FROM yourTable WHERE @g.STIntersects(geo) = 1 

From my experience, the performance of the two methods depends on the distribution of data and the size of the grid of spatial indices, so check your own data to decide which one to use. Remember to create a spatial index in the geo column.