## 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 🙂**

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0