从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 =地理::点（52.675833,1.282778,4326）
 SET @EDI = geography :: Point（55.95，-3.3725,4326）
 SELECT @ NWI.STDistance（@EDI）/ 1000 
  

但我不想遍历所有在表中的纬度/经度肯定是可怕的性能？

我也试过转换下面的评论链接之一指出的一个例子这是MYSQL不MSSQL）但是我得到一个错误，代码如下：

  DECLARE @orig_lat decimal 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 ）* COS（'lat'* PI（）/ 180）* COS（（@ orig_long  - 'lon'）* PI（）/ 180））* 180 / PI（））* 60 * 1.1515） b $ b 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 
  pre> 
 
 
收到的错误是：
 
 
 Msg 8114， 5，第6行将数据类型varchar 
转换为数字时出错。
 
 
任何人能够编译出上述代码更好的解决方案？
解决方案
几年前我写了一篇博客，解释了如何不使用空间数据类型。因为它看起来好像有一张经度/纬度值表，这个博客很可能会帮助很多。
 
 
 
  SQL Server邮政编码纬度经度邻近搜索 
 
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

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