在另一个范围内查找地理点-SQL Server [英] Finding a geography point within a range of another - SQL Server

问题描述

我在SQL Server中有一个表，其中有地理数据类型列。我想查询该表以查找位于另一个给定地理点附近（在一定范围内）的行。有人对实现此目标的最佳方法有任何想法吗？
我有2种选择在哪里进行这种类型的查询。我可以将它作为存储的proc在sql服务器上写，也可以在c＃代码中完成，因为我正在使用实体框架进行数据访问。

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.

我会有一个具有范围（例如100m）和传入的地理位置的查询。
伪代码可能是这样的……

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...

选择行
，其中rows.geo在给定的地理位置范围内

select rows where rows.geo within range of given geography point

我在网络上的SQL Server中查找地理位置查询示例时遇到了一些麻烦。

I'm having a bit of trouble finding examples of geography queries in SQL Server on the web.

任何帮助都会

推荐答案

假定您在数据库中具有经度和纬度的点值。

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和reflong是您想知道附近地点的起点。
radiusofinterest是到该点的距离。
40000是地球的周长。您可以使用更准确的数字。

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.

我还没有使用SQLServer检查语法....因此可能存在一些错误。

i havent checked the syntax with SQLServer though.... so there may be some errors there.

cos（reflat）根据您所在的纬度校正圆周。对于较小的距离，它应该可以工作。

the cos(reflat) corrects the circumference based on the lat you are in. It should work ok for smaller distances.

这篇关于在另一个范围内查找地理点-SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！