CODEWITHSUNDEEP
sql-serverspatialintersect
VBAHole
VBAHole

Reputation: 1518

Perform a Sql Server Spatial Intersection against a table full of spatial features?

I have a table in sql 2015 that has a geometry column and about 10,000 records. I want to test if a new record i am thinking about adding overlaps any of the existing records. I know that i can compare two features with STIntersects like this:

DECLARE @a geometry; DECLARE @b geometry;

SET @a = GEOMETRY::STPolyFromText('POLYGON((-10277454.3014 4527261.7601, -10277449.1674 4527236.5722, -10277503.1433 4527245.177, -10277462.2333 4527281.9267, -10277454.3014 4527261.7601))',3857);

SELECT @b = [Shape] FROM [GIS].[ggon].[blah] WHERE OBJECTID = 4539;

SELECT @a.STIntersects (@b);

But what if i wanted to compare @a to ALL of the features in the blah table?

If the result of the intersection above is 1 then i know that the @a geometry intersects the @b geometry which it does. That works. But if i change @b to be:

SELECT @b = [Shape] FROM [GIS].[ggon].[blah]

then i get a 0 - which is not correct

SELECT @a.STIntersects (SELECT [Shape] FROM [GIS_PL].[talon].[MDC_WM]);

also fails

Upvotes: 2

Views: 862

Answers (1)

hcaelxxam
hcaelxxam

Reputation: 786

DECLARE @a geometry; DECLARE @b geometry;

SET @a = GEOMETRY::STPolyFromText('POLYGON((-10277454.3014 4527261.7601, -10277449.1674 4527236.5722, -10277503.1433 4527245.177, -10277462.2333 4527281.9267, -10277454.3014 4527261.7601))',3857);

SELECT * FROM [GIS].[ggon].[blah] WHERE [Shape].STIntersects(@a) = 1

Upvotes: 2

Related Questions