Return to Answer

added 1376 characters in body

Source Link

edited Jan 12, 2015 at 18:17

3.4k
16
20

Edit As per your comments, polygons may exist along the line. There is a number of ways of doing this query, but unless you know the maximum amount intersections that will happen having the output rows in the format that you have specified will be dificult. Here's a couple of options

-- One row per intersection
SELECT l.id line_id, p.id polygon_id
FROM Lines l
    LEFT OUTER JOIN Polygons p ON ST_Intersects(p.Geom, ST_Buffer(l.Geom,0.05));

-- One row per line with an array of intersections ids
SELECT l.id line_id, array_agg(p.id) polygon_id
FROM Lines l
    LEFT OUTER JOIN Polygons p ON ST_Intersects(p.Geom, ST_Buffer(l.Geom,0.05))
GROUP BY l.id;

-- Crosstab query
SELECT line_id, 
    MAX(CASE WHEN r = 1 THEN polygon_id END) polygon_id_1,
    MAX(CASE WHEN r = 2 THEN polygon_id END) polygon_id_2,
    MAX(CASE WHEN r = 3 THEN polygon_id END) polygon_id_3,
    MAX(CASE WHEN r = 4 THEN polygon_id END) polygon_id_4,
    MAX(CASE WHEN r = 5 THEN polygon_id END) polygon_id_5
    -- ...
FROM (
    SELECT l.id line_id, p.id polygon_id, ROW_NUMBER() OVER (PARTITION BY l.id ORDER BY p.id) r 
    FROM Lines l
        LEFT OUTER JOIN Polygons p ON ST_Intersects(p.Geom, ST_Buffer(l.Geom,0.05))
    ) a
GROUP BY line_id;

-- One row per intersection
SELECT l.id line_id, p.id polygon_id
FROM Lines l
    LEFT OUTER JOIN Polygons p ON ST_Intersects(p.Geom, ST_Buffer(l.Geom,0.05));

-- One row per line with an array of intersections ids
SELECT l.id line_id, array_agg(p.id) polygon_id
FROM Lines l
    LEFT OUTER JOIN Polygons p ON ST_Intersects(p.Geom, ST_Buffer(l.Geom,0.05))
GROUP BY l.id;

-- Crosstab query
SELECT line_id, 
    MAX(CASE WHEN r = 1 THEN polygon_id END) polygon_id_1,
    MAX(CASE WHEN r = 2 THEN polygon_id END) polygon_id_2,
    MAX(CASE WHEN r = 3 THEN polygon_id END) polygon_id_3,
    MAX(CASE WHEN r = 4 THEN polygon_id END) polygon_id_4,
    MAX(CASE WHEN r = 5 THEN polygon_id END) polygon_id_5
    -- ...
FROM (
    SELECT l.id line_id, p.id polygon_id, ROW_NUMBER() OVER (PARTITION BY l.id ORDER BY p.id) r 
    FROM Lines l
        LEFT OUTER JOIN Polygons p ON ST_Intersects(p.Geom, ST_Buffer(l.Geom,0.05))
    ) a
GROUP BY line_id;

Source Link

created Jan 11, 2015 at 20:43

MickyT

3.4k
16
20

Working on the assumption that the polygons are only ever going to be at the end of lines, your query should return each polygon from your diagram, but not in the format you have indicated. This makes me think that you have a small gap between your polygon and the end of the line. I think the tolerance is 0.00001 for ST_Intersects.
Rather than using ST_Intersects to determine the relationship, you could use ST_Distance. Another option would be to buffer the line (or endpoints) slightly using ST_Buffer. The buffer is probably the best option performance wise.

Here's an example of a query to get back the results as you have indicated

SELECT l.id Line_ID, p1.id Intersected_Polygons_id_1, p2.id Intersected_Polygons_id_2
FROM Lines l 
  LEFT OUTER JOIN Polygons p1 ON 
    ST_Intersects(p1.Geom, ST_Buffer(ST_StartPoint(l.Geom),0.05))  
--    ST_Distance(p1.Geom,ST_StartPoint(l.Geom)) <= 0.05
  LEFT OUTER JOIN Polygons p2 ON 
    ST_Intersects(p2.Geom, ST_Buffer(ST_EndPoint(l.Geom),0.05))  
--    ST_Distance(p2.Geom,ST_EndPoint(l.Geom)) <= 0.05
;

I have also put in the ST_Distance join clause in as a comment.