空间查询

USE [NSA]
GO
/****** Object:  StoredProcedure [dbo].[BufferSelect] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BufferSelect]
@WKT varchar(2000),
@startPt varchar(30),
@Buffer int,
@OrderbyX int
AS
begin
declare @stPt geometry;
set @stPt=geometry::STGeomFromText(@startPt,4326);
declare @urplace GEOGRAPHY;
set @urplace= GEOGRAPHY::STGeomFromText(@WKT,4326);
declare @bufArea GEOGRAPHY
set @bufArea = @urplace.STBuffer(3);
declare @bufArea2 geometry
set @bufArea2=geometry::STGeomFromWKB(@bufArea.STAsBinary(),4326)
select  p.*,l.LineID as LineName,l.Path as LinePath from SVPoint p left join SVLine l on p.LineID=l.LineID inner join (
select ptID as PointID, PtGeo from SVPointGeo where PtGeo.Filter(@bufArea2)=1 
) g on p.PointID= g.PointID order by g.PtGeo.STDistance(@stPt)