如何计算SQL Server中多个点之间的距离? [英] How to calculate distance between multiple points in SQL Server?

问题描述

我有一个表，其中包含来自GPS的数据，例如:纬度，经度，时间， UserId

如何汇总指定时间范围内的总距离，将所有点的所有距离(按时间排序)和用户分组的数据相加?

谢谢

解决方案

如果您使用的是SQL Server，则可能对使用地理数据类型感兴趣，因此可以使用专用方法和地理索引来请求数据库.

从SQL Server 2008开始，就可以使用geography数据类型，您可以在此处获取更多信息:

http://msdn.microsoft.com/en-us/library/cc280766.aspx

将数据放入地理"列后，您将能够使用STDistance()方法计算两点之间的距离，请参见MSDN:

http://msdn.microsoft.com/en-us/library/bb933808.aspx

这里是一个示例，其中STDistance()用于计算以米为单位(国际标准单位)返回的两个点之间的距离(具有地理变形):

  DECLARE @pointA地理；DECLARE @pointB地理位置；SET @pointA = geography :: STGeomFromText('POINT(-122.34900 50)'，4326);SET @pointB = geography :: STGeomFromText('POINT(-122.34900 47.65100)'，4326);SELECT @ pointA.STDistance(@pointB); 

如果您使用的是SQL Server 2005(或者希望避免使用地理数据类型)，则可以查看CodePlex上的MsSQLSpatial项目，该项目位于: 解决方案

If you are using SQL Server, you might be interested in using the geography data type so you can request the database using the dedicated method and geographical index.

The geography data type is available since SQL Server 2008, you can more information right here:

http://msdn.microsoft.com/en-us/library/cc280766.aspx

Once you've got the data into the geography column, you will be able to calculate the distance between two points using STDistance() methods, see the MSDN:

http://msdn.microsoft.com/en-us/library/bb933808.aspx

Here is an example where STDistance() is used to calculate the distance (with the geographical deformation) between two points returned in meters (international standard unit):

DECLARE @pointA geography;
DECLARE @pointB geography;
SET @pointA = geography::STGeomFromText('POINT(-122.34900 50)', 4326);
SET @pointB = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @pointA.STDistance(@pointB);

If you're using SQL Server 2005 (or if you want to avoid using the geography data type), you can take a look at the MsSQLSpatial project on CodePlex, available here: http://mssqlspatial.codeplex.com/wikipage?title=Features&referringTitle=Home

这篇关于如何计算SQL Server中多个点之间的距离?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！