佳佳的博客
Menu
首页
[SqlServer] geography 空间类型数据
Posted by
佳佳
on 2020-05-15
IT
SQL Server
<!-- # [SqlServer] geography 空间类型数据 --> <!-- sqlserver-geography --> ## geography 空间数据类型 `geography` 用来表示圆形地球空间坐标系中的数据,如 GPS 经纬度坐标。 SqlServer 提供一些列方法用于创建、构造和查询这些数据。具体信息请参照 [MSDN 文档][1] 。 下面是两个摘自 MSDN 的示例: 其中 *4326* 是 SQL Server 默认的 [SRID][4],它将映射到 *WGS 84* 空间引用系统。 ### Example A ```sql IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL DROP TABLE dbo.SpatialTable; GO CREATE TABLE SpatialTable ( id int IDENTITY (1,1), GeogCol1 geography, GeogCol2 AS GeogCol1.STAsText() ); GO INSERT INTO SpatialTable (GeogCol1) VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326)); INSERT INTO SpatialTable (GeogCol1) VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326)); GO ``` ### Example B ```sql DECLARE @geog1 geography; DECLARE @geog2 geography; DECLARE @result geography; SELECT @geog1 = GeogCol1 FROM SpatialTable WHERE id = 1; SELECT @geog2 = GeogCol1 FROM SpatialTable WHERE id = 2; SELECT @result = @geog1.STIntersection(@geog2); SELECT @result.STAsText(); ``` ## 最短距离 查询某个位置最近的记录是比较常见的需求,这个需要用到 [STDistance][3] 函数,返回的是两个坐标之间的距离(单位是米)。 下面示例返回距离坐标点 *-121.626 47.8315* 最近的七条记录,使用 *ToString()* 方法获取坐标的字符串。 ```sql DECLARE @g geography = 'POINT(-121.626 47.8315)'; SELECT TOP(7) SpatialLocation.ToString(), City FROM Person.Address WHERE SpatialLocation.STDistance(@g) IS NOT NULL ORDER BY SpatialLocation.STDistance(@g); ``` [1]: https://docs.microsoft.com/en-us/sql/relational-databases/spatial/create-construct-and-query-geography-instances?view=sql-server-ver15 (Create, Construct, and Query geography Instances) [2]: https://docs.microsoft.com/en-us/sql/relational-databases/spatial/query-spatial-data-for-nearest-neighbor?view=sql-server-ver15 (Query Spatial Data for Nearest Neighbor) [3]: https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stdistance-geography-data-type?view=sql-server-ver15 (STDistance (geography Data Type)) [4]: https://baike.baidu.com/item/srid/2496422 (SRID)
版权声明:原创文章,未经允许不得转载。
https://www.liujiajia.me/2020/5/15/sqlserver-geography
“Buy me a nongfu spring”
« 《重构》 8. 搬移特性
C# 深拷贝方法效率比较(2) »
昵称
*
电子邮箱
*
回复内容
*
(回复审核后才会显示)
提交
目录
AUTHOR
刘佳佳
江苏 - 苏州
软件工程师