sqlserver操作geography方法

--总表面积
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()

--二进制（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).STBuffer(1)

--返回指定的曲线
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('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).STDimension()  

--是否不相联
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('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326).STDistance(geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326))

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

--判断是否相等
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)

--类型 
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((-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).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).STIsClosed() 

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

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

--边长总长度
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()

--一维geography曲线数
SELECT geography::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING(-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))',4326).STNumCurves()

--geometry数量
SELECT geography::STGeomFromText('MULTIPOINT((-122.360 47.656), (-122.343 47.656))', 4326).STNumGeometries()

--点数量
SELECT geography::STGeomFromText('GEOMETRYCOLLECTION(CIRCULARSTRING(-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653)  ,CURVEPOLYGON(CIRCULARSTRING(-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653)))', 4326).STNumPoints()

--指定点
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).STSrid

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

--余集 
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('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 work_order_no,s.地市 as cityname,s.场景名称 as scenename from  tousu_f t
inner join scene_525 s on s.Shape.STContains(geometry::STGeomFromText('POINT('+cast(longitude as varchar)+' '+cast(latitude as varchar)+ ')', 4326))=1

--计算点1000米范围内点

--create table
create table t_gis_temp_lte_geom(
cgi varchar(50) primary key,
geom geometry
)
create table t_gis_temp_lte_range(
acgi varchar(50),
bcgi varchar(50),
distance float
)
--clear table
delete from t_gis_temp_lte_geom;
--delete spatial index
drop index t_gis_temp_lte_geom_index;
--insert into table
insert into t_gis_temp_lte_geom(cgi,geom)
select CGI as cgi,geometry::STGeomFromText('POINT('+cast(longitude_antenna as varchar)+' '+cast(latitude_antenna as varchar)+ ')', 4326) as geom from GC_LTE 
where DATE_TIME='2019-04-19 00:00:00' and longitude_antenna is not null and latitude_antenna is not null;
--create spatial index
CREATE SPATIAL INDEX t_gis_temp_lte_geom_index ON t_gis_temp_lte_geom(geom) WITH (BOUNDING_BOX =(95, 25, 110, 35 ));
--clear table
delete from t_gis_temp_lte_range;
--insert into table
insert into t_gis_temp_lte_range(acgi,bcgi,distance)
select a.cgi as agci,b.cgi as bcgi,a.geom.STDistance(b.geom)*111201.0 as distance from 
(select cgi,geom from t_gis_temp_lte_geom) a,
(select cgi,geom from t_gis_temp_lte_geom) b
where a.geom.STDistance(b.geom)<1000/111201.0 and a.cgi != b.cgi;
--select data
select　top 100 * from　t_gis_temp_lte_range;

修改成存储过程：

--create function
if (exists (select * from sys.objects where name = 'get_lte_range'))
    drop proc get_lte_range
go
create proc get_lte_range(
    @datetime datetime,
    @distance float
)
as
    --clear table
    truncate table t_gis_temp_lte_geom;
    --drop index t_gis_temp_lte_geom_index on t_gis_temp_lte_geom;
    --insert data
    insert into t_gis_temp_lte_geom(cgi,geom)
        select CGI as cgi,geometry::STGeomFromText('POINT('+cast(longitude_antenna as varchar)+' '+cast(latitude_antenna as varchar)+ ')', 4326) as geom from GC_LTE 
            where DATE_TIME=@datetime and longitude_antenna is not null and latitude_antenna is not null;
    --create spatial index
    --create spatial index t_gis_temp_lte_geom_index on t_gis_temp_lte_geom(geom) with (BOUNDING_BOX =(95, 25, 110, 35 ));

    --clear table
    truncate table t_gis_temp_lte_range;
    --insert data
    insert into t_gis_temp_lte_range(acgi,bcgi,distance)
        select a.cgi as agci,b.cgi as bcgi,a.geom.STDistance(b.geom)*111319.0 as distance from 
            (select cgi,geom from t_gis_temp_lte_geom) a,
            (select cgi,geom from t_gis_temp_lte_geom) b
                where a.geom.STDistance(b.geom)<@distance/111319.0 and a.cgi != b.cgi;
--execute proc
exec get_lte_range '2019-05-25 00:00:00',300;