ST_INTERSECT of each element of two tables (polygons, lines)

Question

Im new to postgis and trying to solve the following problem:

I have two tables: 1. containing polygons 2. containing lines

I need to find out the id of all polygons on each line and fill them in new columns. So for each intersection there should be one new column in the table of lines with the id of the intersecting polygon. there wont be not more than three polygons on lines.

This is the code I already have, but it just gives just the first polygon that intersects with the line.

SELECT a.osm_id, b.osm_id 
FROM lines a  LEFT JOIN
polygons b  ON ST_Intersects(b.way,a.way);

Answer 1

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.

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;