从 MSSQL 数据库表中获取最近的经度和纬度? [英] Get the nearest longitude and latitude from MSSQL database table?

问题描述

好的，我已经搜索过 SO 和 Google，但还没有真正找到明确的答案，因此将其扔给 SO 社区.

基本上我有一个特定兴趣点的经度和纬度表，sql查询将知道您当前所在的位置(这将是表中的位置之一，作为参数传入)，因此它然后需要计算并返回与传入的经纬度最近的一行.

我要求这一切都在 MSSQL(2012/存储过程)中完成，而不是在调用应用程序(即 .NET)中完成，因为我听说 SQL 在处理此类查询时通常比 .NET 快得多?

我找到了 STDistance 函数，它给出了位置之间的英里数，例如:

DECLARE @NWI 地理，@EDI 地理SET @NWI = geography::Point(52.675833,1.282778,4326)SET @EDI = geography::Point(55.95,-3.3725,4326)选择@NWI.STDistance(@EDI)/1000

但是我不想遍历表中的所有纬度/经度，因为这肯定会影响性能吗?

我还尝试转换以下注释链接之一中指出的示例之一(这是 MYSQL 而不是 MSSQL)，但是出现错误，代码如下:

DECLARE @orig_lat decimal(6,2), @orig_long decimal(6,2), @bounding_distance int设置@orig_lat=52.056736;设置@orig_long=1.14822;设置@bounding_distance=1;SELECT *,((ACOS(SIN(@orig_lat * PI()/180) * SIN('lat' * PI()/180) + COS(@orig_lat * PI()/180) * COS('lat' *PI()/180) * COS((@orig_long - 'lon') * PI()/180)) * 180/PI()) * 60 * 1.1515) AS '距离'来自 [DB1].[dbo].[LatLons]在哪里('lat' BETWEEN (@orig_lat - @bounding_distance) AND (@orig_lat + @bounding_distance)AND 'lon' BETWEEN (@orig_long - @bounding_distance) AND (@orig_long + @bounding_distance))按距离" ASC 排序

收到的错误是:

<块引用>

消息 8114，级别 16，状态 5，第 6 行错误转换数据类型 varchar到数字.

有人能够计算出上述代码或提出更好的解决方案吗?

解决方案

几年前我写了一篇博客，解释了如何在不使用空间数据类型的情况下完成这项工作.由于您似乎有一张经度/纬度值表，因此这篇博客可能会很有帮助.

SQL Server 邮政编码纬度经度邻近搜索

从 Archive.org 保存的同一页面

Ok I have searched SO and Google but haven't really found a definitive answer so throwing it out there for the SO community.

Basically I have a table of longitudes and latitudes for specific points of interest, the sql query will know which location you are currently at (which would be one of those in the table, passed in as a parameter), therefore it then needs to calculate and return the one row that is the nearest latitude and longitude to the passed in one.

I require this to all be done in MSSQL (2012 / stored proc) rather than in the calling application (which is .NET) as I have heard that SQL is usually much quicker at processing such queries than .NET would be?

EDIT:

I have found the STDistance Function which gives the number of miles between locations such as :

DECLARE @NWI geography, @EDI geography
SET @NWI = geography::Point(52.675833,1.282778,4326)
SET @EDI = geography::Point(55.95,-3.3725,4326)
SELECT @NWI.STDistance(@EDI) / 1000

However I don't want to have to iterate through all of the lat/lons in the table as surely this would be terrible for performance?

I also tried converting one of the examples pointed out in one of the below comment links (which was MYSQL not MSSQL) however I am getting an error, the code is as follows:

DECLARE @orig_lat decimal(6,2), @orig_long decimal(6,2), @bounding_distance int
set @orig_lat=52.056736; 
set @orig_long=1.14822; 
set @bounding_distance=1;

SELECT *,((ACOS(SIN(@orig_lat * PI() / 180) * SIN('lat' * PI() / 180) + COS(@orig_lat * PI() / 180) * COS('lat' * PI() / 180) * COS((@orig_long - 'lon') * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS 'distance' 
FROM [DB1].[dbo].[LatLons]
WHERE
(
  'lat' BETWEEN (@orig_lat - @bounding_distance) AND (@orig_lat + @bounding_distance)
  AND 'lon' BETWEEN (@orig_long - @bounding_distance) AND (@orig_long + @bounding_distance)
)
ORDER BY 'distance' ASC

The error received is:

Msg 8114, Level 16, State 5, Line 6 Error converting data type varchar to numeric.

Anyone able to work out the above code or come up with a better solution?

解决方案

I wrote a blog a couple years ago that explains how this can be done without using the spatial data types. Since it appears as though you have a table of longitude/latitude values, this blog will likely help a lot.

SQL Server Zipcode Latitude Longitude Proximity Search

Same page saved from Archive.org

这篇关于从 MSSQL 数据库表中获取最近的经度和纬度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！