sql - 在sql server中计算两个地理位置之间的距离

Question

我正在尝试使用 SQL Server 中的以下查询计算从一个点到另一个点（多边形/多多边形）的距离。

DECLARE @g geometry;  
DECLARE @h geometry;  
SET @g = geometry::STGeomFromText('POLYGON ((139.999722222222 40.0513888888889, 141.401111111111 40.0513888888889, 141.398333333333 39.3494444444444, 140.765 38.1005555555556, 140.766111111111 37.3333333333333, 140.314722222222 36.8161111111111, 140.316111111111 36.2841666666667, 139.050277777778 36.2847222222222, 139.049444444444 35.1669444444444, 138.499444444444 35.1669444444444, 138.499444444444 35.5852777777778, 136.816944444444 35.5830555555556, 136.034166666667 34.8980555555556, 135.732222222222 34.8997222222222, 135.732222222222 35.3827777777778, 136.431944444444 35.8013888888889, 137.365 36.5341666666667, 138.165555555556 36.9333333333333, 139.415 37.8152777777778, 140.285833333333 39.4497222222222, 140.285833333333 39.8652777777778, 139.915555555556 39.8652777777778, 139.915555555556 39.9669444444444, 139.999722222222 40.0513888888889))', 4326);
SET @h = geometry::STGeomFromText('POINT(-1.9335937499142 53.956085529457)', 4326);  
SELECT @g.STDistance(@h)/1609.344 as DistanceInMiles,@h.STDistance(@g) as DistanceInMeters

以下坐标在英国，

点（-1.9335937499142 53.956085529457）

多边形的坐标在日本。不知何故，当我运行查询时，我得到的距离不正确。

您可以在以下 db fiddle 中检查结果，

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d6f4e3cbfa8d836c24006f6005eefc4b

不确定我是否做错了什么？

标签： sqlsql-serverazure-sql-databasespatialsql-server-2017

Answer 1

您在这里有 2 个问题。当您实际使用 GPS 坐标时，第一个不是使用GEOGRAPHY数据类型而是使用。GEOMETRY地理将考虑地球的形状，并将针对这种情况返回更精确的值。

第二个也是最大的问题是日本多边形的环方向。坐标放置在硬编码字符串上的顺序对于确定生成的多边形非常重要。有时 SQL Server 无法正确确定我们的点之间的边的哪一侧实际上是指多边形。

你可以用一个非常巧妙的技巧来解决这个问题，方法是使用同一个多边形STUnion的GEOMETRY表示：

DECLARE @g_metry GEOMETRY = GEOMETRY::STGeomFromText('POLYGON ((139.999722222222 40.0513888888889, 141.401111111111 40.0513888888889, 141.398333333333 39.3494444444444, 140.765 38.1005555555556, 140.766111111111 37.3333333333333, 140.314722222222 36.8161111111111, 140.316111111111 36.2841666666667, 139.050277777778 36.2847222222222, 139.049444444444 35.1669444444444, 138.499444444444 35.1669444444444, 138.499444444444 35.5852777777778, 136.816944444444 35.5830555555556, 136.034166666667 34.8980555555556, 135.732222222222 34.8997222222222, 135.732222222222 35.3827777777778, 136.431944444444 35.8013888888889, 137.365 36.5341666666667, 138.165555555556 36.9333333333333, 139.415 37.8152777777778, 140.285833333333 39.4497222222222, 140.285833333333 39.8652777777778, 139.915555555556 39.8652777777778, 139.915555555556 39.9669444444444, 139.999722222222 40.0513888888889))', 4326);

DECLARE @g GEOGRAPHY = @g_metry.MakeValid().STUnion(@g_metry.STStartPoint()).STAsText()

DECLARE @h GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(-1.9335937499142 53.956085529457)', 4326); 

SELECT 
    @g.STDistance(@h)/1609.344 as DistanceInMiles,
    @h.STDistance(@g) as DistanceInMeters

结果：

DistanceInMiles     DistanceInMeters
5574.53292757983    8971341.11980304

如需进一步阅读，您可以阅读此博客文章。