2

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

VBAHole
  • 1,508
  • 2
  • 24
  • 38

1 Answers1

2

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

hcaelxxam
  • 616
  • 3
  • 14