Eliminate extra rows that intersect 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 &lt;&gt; t2.n;

The output looks like:




n
n




poly2
poly1


poly1
poly2




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:




n
n




poly1
poly2
Then you need an &lt; or &gt; instead of a &lt;&gt;.
That is similar to when you try to find dupes in your data


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 &lt; t2.n;
GO


n     | n    
:---- | :----
poly1 | poly2


db&lt;&gt;fiddle here