# How to find intersections with multiple shapes

• ### Question

• Before I start, my disclaimer is that I am new to the spatial data types.

I have a table containing lat and long. I have figured out how to draw a 2 mile (3218.69 meter) buffer (circle) around each point and find intersections when two of these shapes touch. I need to figure out how to find when n shapes overlap. I need to find which rows overlap.

Here is my current code:

DROP TABLE #GeoTable
CREATE TABLE #GeoTable (
GeoID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Points geography NOT NULL)

INSERT INTO #GeoTable
(  Points )
VALUES
(
geography::Point(47.6500, -122.3500, 4326)  -- Points - geography
)

INSERT INTO #GeoTable
(  Points )
VALUES
(
geography::Point(47.6800, -122.3500, 4326)  -- Points - geography
)

INSERT INTO #GeoTable
(  Points )
VALUES
(
geography::Point(47.6300, -122.3500, 4326)  -- Points - geography
)

INSERT INTO #GeoTable
(  Points )
VALUES
(
geography::Point(47.6300, -121.3500, 4326)  -- Points - geography
);

-- I would like to get all of the intersecting rows: (Set 1,2; set 1,3; set 2,3; set 1,2,3):

WITH cte_UniqueRows AS (SELECT a.GeoID
FROM #GeoTable a
INNER JOIN #GeoTable b ON b.GeoID <> a.GeoID AND
a.Points.STBuffer(3218.69).STIntersects(b.Points.STBuffer(3218.69)) = 1
GROUP BY a.GeoID)
SELECT a.Points.STBuffer(3218.69)
FROM cte_UniqueRows ur INNER JOIN #GeoTable a ON ur.GeoID = a.GeoID

-- This will give me the 3 rows, but not if multiple rows intersect. (see spatial data in result set).

Thanks,

Rob

Tuesday, July 30, 2013 9:07 PM

• Yes, unfortunately, this only works if there are three points. I need it to work for n points.

I did figure out a solution, it involves temp tables and looping, which I hate to do in SQL.

Basically, I take a point, draw a buffer around it and find all points with a buffer that intersects my point's buffer. These go into a temp table. Then I loop through each related point in the temp table to see if it overlaps other points within the temp table. My pseudocode:

1. Draw a 2 mile buffer around my point (in this case a prospective property)
2. Create a 2 mile buffer around all properties in my property table and return all rows where the buffers intersect. These are store in a temp table
3. Loop through each row in the temp table finding any existing properties that intersect one another.

I was hoping for a way to do this all relationally, but I could not.

• Marked as answer by Monday, August 5, 2013 12:10 PM
Thursday, August 1, 2013 12:13 PM

### All replies

• Hello,

The issue caused by the CTE query in your statement, please refer to the following statement:

```WITH cte_UniqueRows AS (SELECT a.GeoID as id1,b.GeoID as id2, null as id3
FROM #GeoTable a
INNER JOIN #GeoTable b ON b.GeoID <> a.GeoID AND
a.Points.STBuffer(3218.69).STIntersects(b.Points.STBuffer(3218.69)) = 1WHERE a.GeoID< b.GeoID
GROUP BY a.GeoID,b.GeoID
UNION
SELECT a.GeoID as id1,b.GeoID as id2, c.GeoID as id3
FROM #GeoTable a
INNER JOIN #GeoTable b ON b.GeoID <> a.GeoID AND
a.Points.STBuffer(3218.69).STIntersects(b.Points.STBuffer(3218.69)) = 1INNER JOIN #GeoTable c ON c.GeoID <> a.GeoID AND
a.Points.STBuffer(3218.69).STIntersects(c.Points.STBuffer(3218.69)) = 1
WHERE a.GeoID< b.GeoID and b.GeoID<c.GeoID
GROUP BY a.GeoID,c.GeoID, b.GeoID)
SELECT a.Points.STBuffer(3218.69).STAsText()
FROM cte_UniqueRows ur INNER JOIN #GeoTable a ON ur.id1 = a.GeoID
```

Regards,
Fanny Liu

If you have any feedback on our support, please click  here.

Fanny Liu
TechNet Community Support

Thursday, August 1, 2013 8:46 AM
• Yes, unfortunately, this only works if there are three points. I need it to work for n points.

I did figure out a solution, it involves temp tables and looping, which I hate to do in SQL.

Basically, I take a point, draw a buffer around it and find all points with a buffer that intersects my point's buffer. These go into a temp table. Then I loop through each related point in the temp table to see if it overlaps other points within the temp table. My pseudocode:

1. Draw a 2 mile buffer around my point (in this case a prospective property)
2. Create a 2 mile buffer around all properties in my property table and return all rows where the buffers intersect. These are store in a temp table
3. Loop through each row in the temp table finding any existing properties that intersect one another.

I was hoping for a way to do this all relationally, but I could not.

• Marked as answer by Monday, August 5, 2013 12:10 PM
Thursday, August 1, 2013 12:13 PM