June 22, 2011 at 3:01 am
Morning,
I know the title is a bit obscure, essentially what I would like to do is use the stintersects function to retrieve what polygons are intersected by a lat long point. But do this for all the points in a table at once - in many cases the point intersects more than one polygon.
So essentially create a 1-many table from a table.
I created a function which returns the polygons intersected in a table, if you pass the id of the point, the latitude and the longitude.
CREATE FUNCTION [dbo].[REGIONINTERSECTTABLE]
(
@GC_HEADER_ID int,
@latitude float,
@longitude float
)
RETURNS
@TABLEVAR TABLE
(
GC_HEADER_ID INT,
REGION_ID INT
)
AS
BEGIN
declare @fet geometry =([geometry]::STPointFromText(((('POINT('+CONVERT([varchar](20),@Longitude,(0)))+' ')+CONVERT([varchar](20),@Latitude,(0)))+')',(4326)))
INSERT INTO @TABLEVAR (GC_HEADER_ID, REGION_ID)
SELECT @GC_HEADER_ID,REGION_ID
from dbo.SPATIAL_GLOBAL_REGIONS WITH(INDEX(SPATIAL_REGION_IDX))
where GEOM.STIntersects(@fet)=1
RETURN
END
GO
And I want to run something like:
SELECT *
FROM dbo.REGIONINTERSECTTABLE (1,51,115);
GO
But instead of 1 at a time do it for all the rows in a table and populate 1 child table?
Any ideas would be a real help,
Thanks,
Oliver
June 22, 2011 at 4:59 am
Hi Oliver,
Have a look at CROSS APPLY in BOL.
June 23, 2011 at 2:18 am
Thank you for your help, exactly what I needed, now works like a dream.
SELECT * FROM GC_HEADER d
cross apply dbo.REGIONINTERSECTTABLE (d.GC_HEADER_ID,d.LATITUDE,d.LONGITUDE)
where LATITUDE is not null and LONGITUDE is not null
GO
Cheers,
Oliver
June 27, 2011 at 4:28 am
Glad I could help, Oliver.
Edit: Comma missing. Didn't sound/look right.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply