日期:2014-05-16 浏览次数:20683 次
--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