在SQL SERVER中使用Long和Lat计算点之间的距离 [英] Calculate distance between points using Long and Lat in SQL SERVER

问题描述

我正在使用以下目的地

[BMAnalytics].[dbo].[EUACTIVESTORES]

它具有列

[Store No]
[Lat]
[Long]

我可以使用这些列自行加入[Store No]吗，所以我将商店"到商店"的每种组合都列在两列中，分别称为来源"和"目标"

Can I use these columns to self Join the [Store No] so I have each combination of Store to Store listed in two columns, called 'Source' & 'Target'

然后在第三列中可以计算两者之间的距离吗?

And then in a third column is it possible to calculate the distance between the two?

我以前使用过以下内容，但这仅适用于一个点对点，

I have used the below previously but this only works for one single point to point,

DECLARE @source geography = 'POINT(0 51.5)'
DECLARE @target geography = 'POINT(-3 56)'

SELECT (@source.STDistance(@target))/1000

理想情况下，我想要从每个分支到每个分支等的距离.

Ideally, i'd like the distance from each branch to each branch etc.

欢迎任何指导

推荐答案

以下是使用自连接的快速示例

Here is a quick example using a Self Join

如果可以的话，建议您在源表中添加一个包含地理位置的字段，这样就不必在每次运行查询时都计算此值.

If you can, I would suggest adding a field to your source table which contains the Geography Point, and thus eliminate the need to calculate this value each time you run a query.

示例

Declare @YourTable table ([Store No] int,Lat float,Lng float)
Insert Into @YourTable values
 (1,-8.157908, -34.931675)
,(2,-8.164891, -34.919033)  
,(3,-8.159999, -34.939999)  

Select [From Store] = A.[Store No]
      ,[To Store]   = B.[Store No]
      ,Meters       = GEOGRAPHY::Point(A.[Lat], A.[Lng], 4326).STDistance(GEOGRAPHY::Point(B.[Lat], B.[Lng], 4326))
 From  @YourTable A
 Join @YourTable B on A.[Store No]<>B.[Store No]

返回

编辑是否可以添加地理字段

Update YourTable Set GeoPoint = GEOGRAPHY::Point([Lat], [Lng], 4326)

然后计算将为

,Meters = A.GeoPoint.STDistance(B.GeoPoint)

这篇关于在SQL SERVER中使用Long和Lat计算点之间的距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！