# Eliminate extra rows that intersect

## All we need is an easy explanation of the problem, so here it is.

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:

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

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

Then you need an < or > instead of a <>.

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 < t2.n;
GO
``````
```n     | n
:---- | :----
poly1 | poly2
```

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂