I have a table in SQL Server that has geography datatype column in it. I want to query this table to find rows that fall near (within a range) of another given geography point. Does anyone have any ideas as to the best way to do this? I have 2 options of where to do this type of query. I can write it on the sql server as a stored proc or I can do it in c# code as I am using the Entity Framework for data access.
I would have a query that has a range (eg 100m) and a geography point passed in to it. The pseudo code would be something like this...
select rows where rows.geo within range of given geography point
I'm having a bit of trouble finding examples of geography queries in SQL Server on the web.
Any help would be appreciated.
Assuming you have lat and long values of the points in the 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 is the point from which you want to know the places close to. radiusofinterest is the distance from this point. 40000 is the circumference of the earth. you could use more accurate figures.
i havent checked the syntax with SQLServer though.... so there may be some errors there.
the cos(reflat) corrects the circumference based on the lat you are in. It should work ok for smaller distances.