[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)

[SqlServer] geography 空间类型数据

回复内容 *

目录

AUTHOR