I have a table of geometry values where some rows intersect other rows.
I need a list of rows where the geometry overlaps other rows, but I'd like the list to be as concise as possible.
Here's the setup:
USE tempdb; DROP TABLE IF EXISTS dbo.t; CREATE TABLE dbo.t ( n varchar(100) NOT NULL , i geometry NOT NULL ); INSERT INTO dbo.t (n, i) VALUES ('poly1', geometry::STGeomFromText('POLYGON ((1 2, 1 4, 1 5, 4 6, 1 2))', 4326)) , ('poly2', geometry::STGeomFromText('POLYGON ((1 2, 1 3, 2 5, 4 6, 1 2))', 4326)) , ('poly3', geometry::STGeomFromText('POLYGON ((7 9, 8 7, 9 6, 7 9))', 4326)) SELECT t1.n , t2.n FROM dbo.t t1 INNER JOIN dbo.t t2 ON t1.i.STIntersects(t2.i) = 1 WHERE t1.n <> t2.n;
The output looks like:
However, I'd like only a single row for brevity. i.e. because poly1 overlaps poly2 and poly2 overlaps poly1 I'm getting two rows returned where I'd like only one, as in: