通信人家园

标题: 如何用SQL SERVER数据库计算距离并像MapInfo一样圈选数据 [查看完整版帖子] [打印本页]

时间: 2021-4-7 18:13

作者: yyyyyyyyjjj 标题: 如何用SQL SERVER数据库计算距离并像MapInfo一样圈选数据

如何用SQLSERVER数据库计算距离并像MapInfo一样圈选数据

在MapInfo因为版权的问题不让使用和存在大量的经纬度数据计算的背景下，本人提供3种方法进行经纬度的计算和Buffer数据圈选。（要求你有一点数据库的基础）

准备工作

准备一张基础数据表，表数据和表头如下，表名：T1

在该表数据基础上面增加空间字段（这一步很重要），表名：T1_Point

SELECT [Site_Name_Chinese]

,[Site_Name_English]

,[CGI]

,[gNBId]

,[经度]

,[纬度]

,geography::STGeomFromText('POINT('+convert(varchar(50),[经度])+ ' '+convert(varchar(50),[纬度])+')', 4326) [经纬度点]

into [T1_POINT]

FROM [dbo].[T1]

新表数据展示

新表空间点展示

方法一（利用自定义函数计算距离）

方法1需要在sql server进行自定义计算经纬度函数，具体函数如下：

计算方法：

SELECT * ,[dbo].[fnGetDistance] (A.[经度],A.[纬度],B.[经度],B.[纬度]) [DIS]

FROM [dbo].[T1] A,[dbo].[T1] B

where [dbo].[fnGetDistance] (A.[经度],A.[纬度],B.[经度],B.[纬度])<=500

解释一下：

直接像调用系统函数如max（），avg（）一样调用该自定义函数进行求两个点的距离。

方法二（利用空间类型数据进行计算距离）

计算方法：

SELECT * ,A.[经纬度点].STDistance(B.[经纬度点]) distance

FROM [dbo].[T1_Point] A,[dbo].[T1_Point] B

where A.[经纬度点].STDistance(B.[经纬度点])<=500

解释一下：

将度量该实例与调用 STDistance() 的实例之间的距离。如果 other_geography 是一个空集，则 STDistance() 返回 null。

方法三（利用空间类型数据制造Buffer对点数据进行圈选）

计算方法：

SELECT *

,A.[经纬度点].STDistance(B.[经纬度点]) distance

FROM [dbo]. [T1_Point] A,[dbo]. [T1_Point] B

where A.[经纬度点].STBuffer(500).STContains(B.[经纬度点])=1

解释一下：

STBuffer为geography的缓冲区，同mapinfo的buffer类似，里面的500指的是500米范围内。

STContains()为调用 geography 实例在空间上包含传递给该方法的 geography 实例，则返回 1；否则，返回 0。如果两个 geography 实例的 SRID 不同，则返回 null。

大白话就是以第一个点制作的buffer区域是否包含第二个里面的点

本人无不为，QQ345071917，本人愿意和大家一起共同学习！！！

附件: T1.png (2021-4-7 18:04, 58.41 KB) / 下载次数 0
https://www.txrjy.com/forum.php?mod=attachment&aid=NDc5MDc4fDZiZmZkYzEyfDE3NTQxNzAwMTN8MHww

附件: 空间数据点展示.jpg (2021-4-7 18:06, 15.76 KB) / 下载次数 0
https://www.txrjy.com/forum.php?mod=attachment&aid=NDc5MDc5fDU2NGVkMTQ1fDE3NTQxNzAwMTN8MHww

附件: 空间字段.jpg (2021-4-7 18:06, 53.2 KB) / 下载次数 0
https://www.txrjy.com/forum.php?mod=attachment&aid=NDc5MDgwfGM2ZmQ2YjgwfDE3NTQxNzAwMTN8MHww

附件: 函数.jpg (2021-4-7 18:12, 60.54 KB) / 下载次数 0
https://www.txrjy.com/forum.php?mod=attachment&aid=NDc5MDgxfDM5N2IwYjM3fDE3NTQxNzAwMTN8MHww

时间: 2021-4-7 18:14

作者: yyyyyyyyjjj

CREATE FUNCTION [dbo].[fnGetDistance](@lon1 REAL, @lat1 REAL, @lon2 REAL, @lat2 REAL) RETURNS FLOAT
  AS
BEGIN
  --距离单位(米)
  DECLARE @a_2d REAL,@e_2d REAL,@h_2d Integer,@DEG_2_RAD REAL,@RAD_2_DEG REAL
  DECLARE @x_rads REAL,@y_rads REAL,@n_2ds REAL,@x_2d REAL,@y_2d REAL,@z_2d REAL,@x_radm REAl,@y_radm REAL,@n_2dm REAL,@x_2d_mark REAL,@y_2d_mark REAL,@z_2d_mark REAL
  DECLARE @curdistance REAL

  SET @a_2d = 6378137
  SET @e_2d = 0.00669438
  SET @h_2d = 15
  SET @DEG_2_RAD = 0.01745329252
  SET @RAD_2_DEG = 57.2957795129

    SET @x_rads = Abs(@lon1) * @DEG_2_RAD
SET @y_rads = Abs(@lat1) * @DEG_2_RAD

SET @n_2ds = @a_2d / Sqrt(1 - @e_2d * Sin(@y_rads) * Sin(@y_rads))

SET @x_2d = (@n_2ds + @h_2d) * Cos(@y_rads) * Cos(@x_rads)
SET @y_2d = (@n_2ds + @h_2d) * Cos(@y_rads) * Sin(@x_rads)
SET @z_2d = (@n_2ds * (1 - @e_2d) + @h_2d) * Sin(@y_rads)

SET @x_radm = Abs(@lon2) * @DEG_2_RAD
SET @y_radm = Abs(@lat2) * @DEG_2_RAD

SET @n_2dm = @a_2d / Sqrt(1 - @e_2d * Sin(@y_radm) * Sin(@y_radm))

SET @x_2d_mark = (@n_2dm + @h_2d) * Cos(@y_radm) * Cos(@x_radm)
SET @y_2d_mark = (@n_2dm + @h_2d) * Cos(@y_radm) * Sin(@x_radm)
SET @z_2d_mark = (@n_2dm * (1 - @e_2d) + @h_2d) * Sin(@y_radm)

SET @curdistance = (@x_2d_mark - @x_2d) * (@x_2d_mark - @x_2d) + (@y_2d_mark - @y_2d) * (@y_2d_mark - @y_2d) + (@z_2d_mark - @z_2d) * (@z_2d_mark - @z_2d)
SET @curdistance = Sqrt(@curdistance)

RETURN @curdistance
END
（帖子长度居然有限制，差评！！！）

时间: 2021-4-7 18:15

作者: yyyyyyyyjjj

完整版文档！！！！

附件: 如何用SQL SERVER数据库计算距离并像MapInfo一样圈选数据.docx (2021-4-7 18:15, 126.36 KB) / 下载次数 1
https://www.txrjy.com/forum.php?mod=attachment&aid=NDc5MDgyfDM4YTIyZjdmfDE3NTQxNzAwMTN8MHww

通信人家园 (https://www.txrjy.com/)