数据库空间数据-CSDN博客

--1.空间数据
with Geo
as
(
select id,
       lon,
       lat,
       geography::Parse('POINT('+lon+space(1)+lat+')') as g       
from 
    (
        select 1 as id,'-16.96732' as lon,'36.943' as lat
        union all
        select 1,'-16.58963','36.943'
                
    )a
where lon is not null and
      lat is not null
)
 
 
--2.计算坐标之间的距离
select 
       s.ID,
       g.STDistance(geography::Parse('POINT('+r.LON+SPACE(1)+r.LAT+')'))
from Geo s
inner join 
        (
            select 1 as id,'-116.26598' as lon,'39.27763' as lat
            union all
            select 1,'-16.32683','36.94673'
        ) r
        on s.ID  = r.ID
 
 
--3.建立有空间数据的表
create table x
(
    v int not null identity(1,1) primary key,
    geog geography not null,
    geogWKT as geog.STAsText()
)
 
 
--4.添加空间数据
insert into x(geog)
values(geography::Parse('POLYGON(
                                  (-93.123 36.943,
                                   -93.126 36.953,
                                   -94.129 36.986,
                                   -93.123 36.943)
                                 )'
                       )
         ),   --多边形，开始坐标和结束坐标必须相同,注意polygon中必须包含2层括号，否则报错
        
       (geography::Parse('POINT(-93.123 36.943)')),    --点坐标
        
       (geography::Parse('LINESTRING(-93.123 36.943,
                                     -93.126 36.953)')
                         )  --两坐标之间的线
 
 
--5.地理数据的计算  
select v,
       geogWKT,
       geog.STDistance('POINT (-93.123 36.985)'),          --距离
       geog.STIntersects('POINT (-93.123 36.943)'),        --是否有交集
       geog.STLength(),                                    --长度
       geog.STArea(),                                      --多边形面积
       geog.STAsText()                                     --WKT格式的坐标      
from x