SQL Server 2016 绘制空间点和空间线

薛定谔的DBA

于 2021-03-10 14:22:21 发布

阅读量423

点赞数 1

分类专栏： SQLServer SQLServer 高级特性

本文链接：https://blog.csdn.net/kk185800961/article/details/113757745

版权

SQLServer 同时被 2 个专栏收录

269 篇文章 25 订阅

订阅专栏

SQLServer 高级特性

23 篇文章 0 订阅

订阅专栏

本次测试只是大概了解 SQL Server 在地理空间的基本使用方法。由于文章使用到了2个函数 STRING_AGG 和 CONCAT_WS，建议使用 SQL Server 2017 及以上版本，本人测试的是 SQL Server 2019。我在网上找了份广东省城市经纬度信息，先贴出来，方便大伙测试。

CREATE TABLE [dbo].[GuangDong](
	[city] [varchar](50) NOT NULL,
	[longitude] [decimal](10, 6) NOT NULL,
	[latitude] [decimal](10, 6) NOT NULL,
) 
GO
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东广州', CAST(113.230000 AS Decimal(10, 6)), CAST(23.160000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东花县', CAST(113.190000 AS Decimal(10, 6)), CAST(23.400000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东新十', CAST(114.200000 AS Decimal(10, 6)), CAST(24.090000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东增城', CAST(113.810000 AS Decimal(10, 6)), CAST(23.130000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东从化', CAST(113.550000 AS Decimal(10, 6)), CAST(23.570000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东龙门', CAST(114.250000 AS Decimal(10, 6)), CAST(23.750000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东番禺', CAST(113.360000 AS Decimal(10, 6)), CAST(22.950000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东海口', CAST(110.350000 AS Decimal(10, 6)), CAST(20.020000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东汕头', CAST(116.690000 AS Decimal(10, 6)), CAST(23.390000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东洪江', CAST(110.380000 AS Decimal(10, 6)), CAST(21.200000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东茂名', CAST(110.880000 AS Decimal(10, 6)), CAST(21.680000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东佛山', CAST(113.110000 AS Decimal(10, 6)), CAST(23.050000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东江门', CAST(113.060000 AS Decimal(10, 6)), CAST(22.610000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东深圳', CAST(114.070000 AS Decimal(10, 6)), CAST(22.620000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东宝安', CAST(113.850000 AS Decimal(10, 6)), CAST(22.580000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东珠海', CAST(113.520000 AS Decimal(10, 6)), CAST(22.300000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东韶关', CAST(113.620000 AS Decimal(10, 6)), CAST(24.840000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东曲江', CAST(113.580000 AS Decimal(10, 6)), CAST(24.680000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东乐昌', CAST(113.350000 AS Decimal(10, 6)), CAST(25.140000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东仁化', CAST(113.730000 AS Decimal(10, 6)), CAST(25.110000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东南雄', CAST(114.330000 AS Decimal(10, 6)), CAST(25.140000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东始兴', CAST(114.080000 AS Decimal(10, 6)), CAST(24.780000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东翁源', CAST(114.130000 AS Decimal(10, 6)), CAST(24.360000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东佛岗', CAST(113.520000 AS Decimal(10, 6)), CAST(23.860000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东英德', CAST(113.380000 AS Decimal(10, 6)), CAST(24.170000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东清远', CAST(113.010000 AS Decimal(10, 6)), CAST(23.700000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东阳山', CAST(112.650000 AS Decimal(10, 6)), CAST(24.480000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东连县', CAST(112.400000 AS Decimal(10, 6)), CAST(24.770000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东连山', CAST(112.070000 AS Decimal(10, 6)), CAST(24.590000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东连南', CAST(112.280000 AS Decimal(10, 6)), CAST(24.770000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东惠州', CAST(114.400000 AS Decimal(10, 6)), CAST(23.090000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东惠阳', CAST(114.400000 AS Decimal(10, 6)), CAST(23.090000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东博罗', CAST(114.280000 AS Decimal(10, 6)), CAST(23.180000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东河源', CAST(114.680000 AS Decimal(10, 6)), CAST(23.730000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东连平', CAST(114.480000 AS Decimal(10, 6)), CAST(24.390000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东和平', CAST(114.890000 AS Decimal(10, 6)), CAST(24.450000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东龙川', CAST(115.250000 AS Decimal(10, 6)), CAST(24.090000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东紫金', CAST(115.180000 AS Decimal(10, 6)), CAST(23.640000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东惠东', CAST(114.700000 AS Decimal(10, 6)), CAST(22.970000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东东莞', CAST(113.750000 AS Decimal(10, 6)), CAST(23.040000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东梅州', CAST(116.100000 AS Decimal(10, 6)), CAST(24.550000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东梅县', CAST(116.100000 AS Decimal(10, 6)), CAST(24.550000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东平远', CAST(117.900000 AS Decimal(10, 6)), CAST(24.590000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东蕉岭', CAST(116.180000 AS Decimal(10, 6)), CAST(24.660000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东大埔', CAST(116.700000 AS Decimal(10, 6)), CAST(24.340000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东丰顺', CAST(116.180000 AS Decimal(10, 6)), CAST(23.780000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东五华', CAST(115.750000 AS Decimal(10, 6)), CAST(23.930000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东兴宁', CAST(115.750000 AS Decimal(10, 6)), CAST(24.150000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东潮州', CAST(116.630000 AS Decimal(10, 6)), CAST(23.680000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东澄海', CAST(116.800000 AS Decimal(10, 6)), CAST(23.480000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东潮安', CAST(116.630000 AS Decimal(10, 6)), CAST(23.680000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东饶平', CAST(117.010000 AS Decimal(10, 6)), CAST(23.700000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东南澳', CAST(117.030000 AS Decimal(10, 6)), CAST(23.440000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东潮阳', CAST(116.610000 AS Decimal(10, 6)), CAST(23.270000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东惠来', CAST(116.290000 AS Decimal(10, 6)), CAST(23.070000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东陆丰', CAST(117.640000 AS Decimal(10, 6)), CAST(22.950000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东海丰', CAST(117.330000 AS Decimal(10, 6)), CAST(22.980000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东普宁', CAST(116.170000 AS Decimal(10, 6)), CAST(23.290000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东揭西', CAST(115.820000 AS Decimal(10, 6)), CAST(23.450000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东揭阳', CAST(116.350000 AS Decimal(10, 6)), CAST(23.550000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东南海', CAST(113.110000 AS Decimal(10, 6)), CAST(23.050000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东三水', CAST(112.890000 AS Decimal(10, 6)), CAST(23.180000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东顺德', CAST(113.240000 AS Decimal(10, 6)), CAST(22.840000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东中山', CAST(113.380000 AS Decimal(10, 6)), CAST(22.520000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东斗门', CAST(113.250000 AS Decimal(10, 6)), CAST(22.200000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东新会', CAST(113.020000 AS Decimal(10, 6)), CAST(22.520000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东鹤山', CAST(112.940000 AS Decimal(10, 6)), CAST(22.760000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东开平', CAST(112.680000 AS Decimal(10, 6)), CAST(22.360000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东台山', CAST(112.780000 AS Decimal(10, 6)), CAST(22.270000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东恩平', CAST(112.290000 AS Decimal(10, 6)), CAST(22.210000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东高明', CAST(112.760000 AS Decimal(10, 6)), CAST(21.710000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东廉江', CAST(110.270000 AS Decimal(10, 6)), CAST(21.630000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东化州', CAST(110.590000 AS Decimal(10, 6)), CAST(21.640000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东高州', CAST(110.830000 AS Decimal(10, 6)), CAST(21.950000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东信宜', CAST(110.900000 AS Decimal(10, 6)), CAST(22.360000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东阳春', CAST(111.780000 AS Decimal(10, 6)), CAST(22.160000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东阳江', CAST(111.950000 AS Decimal(10, 6)), CAST(21.850000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东电白', CAST(110.990000 AS Decimal(10, 6)), CAST(21.520000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东吴川', CAST(110.780000 AS Decimal(10, 6)), CAST(21.430000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东徐闻', CAST(110.170000 AS Decimal(10, 6)), CAST(20.340000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东海康', CAST(110.070000 AS Decimal(10, 6)), CAST(20.910000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东遂溪', CAST(110.240000 AS Decimal(10, 6)), CAST(21.390000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东肇庆', CAST(112.440000 AS Decimal(10, 6)), CAST(23.050000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东高要', CAST(112.440000 AS Decimal(10, 6)), CAST(23.050000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东怀集', CAST(112.180000 AS Decimal(10, 6)), CAST(23.930000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东广宁', CAST(112.430000 AS Decimal(10, 6)), CAST(23.140000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东四会', CAST(112.680000 AS Decimal(10, 6)), CAST(23.360000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东新兴', CAST(112.200000 AS Decimal(10, 6)), CAST(22.680000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东云浮', CAST(112.020000 AS Decimal(10, 6)), CAST(22.930000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东罗定', CAST(111.560000 AS Decimal(10, 6)), CAST(22.770000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东郁南', CAST(111.510000 AS Decimal(10, 6)), CAST(23.230000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东德庆', CAST(111.750000 AS Decimal(10, 6)), CAST(23.150000 AS Decimal(10, 6)))
INSERT [dbo].[GuangDong] ([city], [longitude], [latitude]) VALUES (N'广东封开', CAST(111.480000 AS Decimal(10, 6)), CAST(23.450000 AS Decimal(10, 6)))
GO

现在将测试，绘制空间线将这些城市点连接起来。上面已经创建了表，再新增一个空间字段 LocationGeog，并使用纬度和经度信息填充它。你可以使用Long和Lat名称来查看点的纬度和经度。

ALTER TABLE GuangDong add LocationGeog geography 
UPDATE GuangDong set LocationGeog = geography::Point(Latitude, Longitude, 4326)

现在再查询表，除了显示表格记录，查询结果窗口也出现了“空间结果”。

这些点非常小，很考验视力！不过我们确实看到了地理位置上各城市点的分布情况。当然也可以单独绘制一个点。

--SELECT geography::Point(纬度,经度,4326) 
SELECT geography::Point(22.62,114.07,4326) --广东深圳

进行多个点的绘制，使用空间数据类型 MULTIPOINT。

SELECT geometry::STGeomFromText('MULTIPOINT(114.07 22.62,113.23 23.16,113.52 22.3)', 4326);  
SELECT geometry::STGeomFromText('MULTIPOINT((114.07 22.62),(113.23 23.16),(113.52 22.3))', 4326);

进行线条绘制，则使用空间数据类型 LINESTRING。

SELECT geography::STGeomFromText('LINESTRING(114.07 22.62,113.23 23.16,113.52 22.3)',4326)

按照以上格式，使用函数 STRING_AGG 和 CONCAT_WS 将所有城市经纬度拼接起来。

SELECT STRING_AGG(CONCAT_WS(' ', LocationGeog.Long, LocationGeog.Lat),',')
FROM GuangDong

-- 113.23 23.16,113.19 23.4,114.2 24.09,113.81 23.13,113.55 23.57,114.25 23.75,113.36 22.95, ……

为了方便观看，我这使用 Latitude+Longitude 先排序，再按顺序连接各城市。

SELECT geography::STGeomFromText('LINESTRING(' + STRING_AGG(CONCAT_WS(' ', LocationGeog.Long, LocationGeog.Lat),',') + ')',4326) 
FROM(
	SELECT TOP 10000 * FROM GuangDong ORDER BY Latitude+Longitude
) T

至此，完成基本测试。更多参考：空间数据 (SQL Server)。

对于上面出现的 4326，可选数字参考 sys.spatial_reference_systems ，表示要返回的 geography 实例的空间引用标识符 (SRID)。更多参考：空间引用标识符 (SRID)

薛定谔的DBA

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
SQL Server 2016 绘制空间点和空间线

本次测试只是大概了解SQL Server 在地理空间的基本使用方法。由于文章使用到了2个函数STRING_AGG 和 CONCAT_WS，建议使用 SQL Server 2017 及以上版本，本人测试的是 SQL Server 2019。我在网上找了份广东省城市经纬度信息，先贴出来，方便大伙测试。CREATE TABLE [dbo].[GuangDong]( [city] [varchar](50) NOT NULL, [longitude] [decimal](10, 6) NOT NULL..
复制链接

扫一扫