SQL Server 中 Geography 类相关方法的使用

--二进制（WKB）表示形式
SELECT geography::STGeomFromText('LINESTRING( -122.360 47.656, -122.343 47.656)', 4326).STAsBinary()

--WKT表示形式
SELECT geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326).STAsText()

--空间参考
SELECT geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326).STSrid

--类型
SELECT geometry::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326).STGeometryType()

--判断是否为空
SELECT geography::STGeomFromText('POLYGON EMPTY', 4326).STIsEmpty()

--判断是否为有效 Geography 格式
SELECT geography::STGeomFromText('LINESTRING(0 0, 2 2, 1 0)', 4326).STIsValid()

--是否不相联
SELECT geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326).STDisjoint(geography::STGeomFromText('POINT( -122.343 47.656)', 4326))

--点与点之间的距离
SELECT geography::STGeomFromText('POINT(118.816053 32.108986)', 4326).STDistance(geography::STGeomFromText('POINT(118.920491 31.904084)', 4326)) 
--返回值：:24771.3216754072

--查询某点经纬度距离1000米范围内的记录
DECLARE @GGP_LOCATE_POINT GEOGRAPHY = GEOGRAPHY::Point(31.972224,118.727901, 4326);  
SELECT *  FROM [dbo].[Points]
WHERE @GGP_LOCATE_POINT.STDistance(GEOGRAPHY::Point([LATITUDE],[LONGITUDE], 4326))<=1000
--注意经度和纬度的顺序

--点与线之间的距离
SELECT geography::STGeomFromText('LINESTRING(118.816053 32.108986, 118.920491 31.904084)', 4326).STDistance(geography::STGeomFromText('POINT(118.727901 31.972224)', 4326))
--返回值：13687.5877193686

--缓冲区
SELECT geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326).STBuffer(1)

--指定点
SELECT geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326).STPointN(2)

--起点
SELECT geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326).STStartPoint()

--终点
SELECT geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326).STEndPoint()

--最大维度
SELECT geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326).STDimension()

--判断是否闭合
SELECT geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326).STIsClosed()

--边长总长度
SELECT geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326).STLength()

--总表面积
SELECT geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326).STArea()

--判断是否相交
SELECT geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326).STIntersects(geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326))

--交集
SELECT geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326).STIntersection(geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326))

--并集
SELECT geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326).STUnion(geography::STGeomFromText('POLYGON((-122.351 47.656, -122.341 47.656, -122.341 47.661, -122.351 47.661, -122.351 47.656))', 4326))

--差集
SELECT geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326).STDifference(geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326))

--余集
SELECT geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326).STSymDifference(geography::STGeomFromText('POLYGON((-122.351 47.656, -122.341 47.656, -122.341 47.661, -122.351 47.661, -122.351 47.656))', 4326))

--返回指定的曲线
SELECT geography::STGeomFromText('COMPOUNDCURVE (CIRCULARSTRING (-122.358 47.653, -122.348 47.649, -122.348 47.658), CIRCULARSTRING(-122.348 47.658, -122.358 47.658, -122.358 47.653))',4326).STCurveN(2)

--圆弧线段的多边形近似值
SELECT geography::STGeomFromText('CIRCULARSTRING(-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653)',4326).STCurveToLine()

--判断是否相等
SELECT geography::STGeomFromText('GEOMETRYCOLLECTION(POLYGON((-122.368 47.658, -122.338 47.649, -122.338 47.658, -122.368 47.658, -122.368 47.658)), LINESTRING(-122.360 47.656, -122.343 47.656), POINT (-122.35 47.656))', 4326).STEquals(geography::STGeomFromText('POLYGON((-122.368 47.658, -122.338 47.649, -122.338 47.658, -122.368 47.658, -122.368 47.658))', 4326))

--查找 GeometryCollection 中第n个实例
SELECT geography::STGeomFromText('MULTIPOINT(-122.360 47.656, -122.343 47.656)', 4326).STGeometryN(2)