数据库函数

DECLARE @g geometry;
DECLARE @h geometry;空间数据库操作函数
1、STArea() 面积计算
2、STBuffer()缓冲区计算
3、STCrosses()如果geometry实例与另一个geometry实例相交，则返回1。

否则，返回0
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0)', 0);
SET @h = geometry::STGeomFromText('LINESTRING(0 0, 2 2)', 0);
SELECT @g.STCrosses(@h);
4、STDifference()返回一个对象，该对象表示来自一个geometry实例的点，这些点在另一个geometry 实例中不存在
SET @g = geography::STGeomFromText('POL YGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);
SET @h = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SELECT @g.STDifference(@h).ToString();
5、STDisjoint()如果一个geography 实例与另一个geography 实例在空间上不相联，则返回1。

否则，返回0
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.STDisjoint(@h)
6、STDistance()返回一个geometry 实例中的点与另一个geometry 实例中的点之间的最短距离
SET @g = geometry::STGeomFromText('POL YGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
SET @h = geometry::STGeomFromText('POINT(10 10)', 0);
SELECT @g.STDistance(@h
7、STEnvelope()返回实例的最小轴对齐边界矩形
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 3)', 0);
SELECT @g.STEnvelope().ToString()
8、STIntersection()返回一个对象，该对象表示一个Geometry 实例与另一个Geometry 实例的交点
SET @g = geography::STGeomFromText('POL YGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);
SET @h = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SELECT @g.STIntersection(@h).ToString()
9、STIntersects()如果geography 实例与另一个geography 实例相交，则返回1。

否则，返回0
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STIntersects(@h)
10、STOverlaps()确定指定的Geometry 对象是否与当前Geometry 重叠
SET @g = geography::Parse('POL YGON ((-120.533 46.566, -118.283 46.1, -122.3 47.45, -120.533 46.566))');
SET @h= geography::Parse('POL YGON ((-120.533 46.566, -118.283 46.1, -122.3 47.45, -120.533 46.566))')
SELECT @g.STOverlaps(@h)
11/STSymDifference()返回表示位于一个geometry 实例或者另一个geometry 实例中的所有点的对象，但不包含同时位于这两个实例中的那些点
SET @g = geometry::STGeomFromText('POL YGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);
SET @h = geometry::STGeomFromText('POL YGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STSymDifference(@h).ToString()
12、STTouches()是否接触
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STTouches(@h)
13、@pg.STWithin(@s)@pg是否包含在@s里
SET @g = geometry::STGeomFromText('POL YGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
SET @h = geometry::STGeomFromText('POL YGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STWithin(@h)
14、STGeometryN（geometry 数据类型）返回geometry collection 中的指定几何图形
SET @cd=geometry::STGeomFromText('MULTIPOINT((30.6666587469201
104.062021177233),(29.5076372217973106.489384971208))',4326)
Select STGeometryN（1）.STAsText() 表示返回第一个点
15、STIsValid();验证geometry对象是否为正确的空间对象，若正确，返回1，否则0
SET @g=geometry::STGeomFromText('MULTILINESTRING((30.6666 104.06202,
29.50763 106.48938),(30.6666 104.06202,33.78142 105.97914))',4326);
select @g.STIsValid()
游标函数
CREATE function getpolygon(@ZNo varchar(50))
returns varchar(2000)
as
begin
declare@points nvarchar(2000),@point nvarchar(100),@startPoint nvarchar(100) set@points=''
declare myCursor Cursor for
select ltrim(rtrim(str(X)))+' '+ltrim(rtrim(str(Y)))from dbo.界址点
where Number in(select Number from dbo.宗地界址点
where ZNo=@ZNo)
open myCursor
fetch next from myCursor into@Point
while(@@fetch_status=0)
begin
if(@points='')
begin
set@points=@point
set@startpoint=@point
end
else
set@points=@points+','+@point
fetch next from myCursor into@point
end
deallocate myCursor
return@points+','+@startpoint
END
调用游标函数创建宗地多边形实例
update dbo.宗地
set Geom=geometry::Parse('POL YGON(('+dbo.getpolygon(dbo.宗地.ZNo)+'))') //dbo.getpolygon(dbo.宗地.ZNo)已在前面游标函数中转成字符串。