locked
How to find intersections with multiple shapes RRS feed

  • 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

Answers

  • 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 Fanny Liu 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)) = 1
    WHERE 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)) = 1
    INNER 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 Fanny Liu Monday, August 5, 2013 12:10 PM
    Thursday, August 1, 2013 12:13 PM