3

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);

enter image description here

  • Are the polygons only ever going to be on the end of the lines? If not, how do you want multiple polygons (>2) handled? Do the lines overlap into the polygons or just touch the edge? – MickyT Jan 11 '15 at 18:55
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;
|improve this answer|||||
  • Hey Micky, thanks for your help. The ST_Buffer works well. But not all polygons are at the end of lines is, there might be some in between, that seems to be problem then...do you know how to handle this. It would also be possible to define that a line has always a polygone at the startpoint and at the endpoint, therefore I would have to edit the lines table, but it doesnt sound simple :D whats your idea? – Felix Jan 12 '15 at 15:57
  • @Felix I have made an edit to the answer. Hope it gives you a viable option. – MickyT Jan 12 '15 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.