计算两点距离,4326是空间引用标识符,4326代表GCS-WGS-1984坐标系,是系统默认的坐标系。
地理坐标系空间需要用geography ,平面坐标系空间用geometry,计算距离使用STDistance
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POINT(104.12765 31.61)', 4326);
SET @h = geography::STGeomFromText('POINT(114.132179 22.547010)', 4326);
SELECT @g.STDistance(@h);
查询某点经纬度距离1000米范围内的记录
SELECT * FROM [GpsData] WHERE
geography::STGeomFromText('POINT(' + cast([Longitude] as varchar(20)) + ' '
+ cast([Latitude] as varchar(20)) +')', 4326).STDistance(
geography::STGeomFromText('POINT(114.132179 22.547010)', 4326))<1000
STDistance也可以计算点到面的最短距离。
参考:
空间引用标识符
https://technet.microsoft.com/zh-cn/library/bb964707(v=sql.105)
STDistance(geometry 数据类型)
https://msdn.microsoft.com/zh-cn/library/bb933952(v=sql.100).aspx
STDistance的使用及geometry和geography的区别
http://blog.csdn.net/jcx5083761/article/details/46010215