sqlserver 空间数据类型

最新推荐文章于 2022-04-09 11:52:48 发布

weixin_33816611

最新推荐文章于 2022-04-09 11:52:48 发布

阅读量236

点赞数

文章标签：数据库

--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
/*
v    geogWKT    (无列名)    (无列名)    (无列名)    (无列名)    (无列名)
1    POLYGON ((-93.123 36.943, -93.126 36.953, -94.129 36.986, -93.123 36.943))    3561.29978820883    1    180244.411606998    48996227.1008911    POLYGON ((-93.123 36.943, -93.126 36.953, -94.129 36.986, -93.123 36.943))
2    POINT (-93.123 36.943)    4661.03285004629    1    0    0    POINT (-93.123 36.943)
3    LINESTRING (-93.123 36.943, -93.126 36.953)    3561.29978820883    1    1141.48450709835    0    LINESTRING (-93.123 36.943, -93.126 36.953)
*/
select * from x