I have a PostGIS table of polylines and I need to intersect them with a table of polygons (conveniently buffers around points). In real world polylines are cables, and points are poles. I need to know for each cable what poles are along this cable in order

What is very important is the resulting intersections have to be in order as I read the polyline from one end to the other.

enter image description here

  • Welcome to Geographic Information Systems! Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Your questions should as much as possible describe not just what you want to do, but precisely what you have tried and where you are stuck trying that. Please check out our short tour for more about how the site works. – Ian Turton Sep 8 at 8:04
  • what have you tried so far? – Ian Turton Sep 8 at 8:04
  • I know how to intersect each polyline with the buffers. What I don't know is getting the result in order as I read the polyline from one end to the other. My Question is how to order the results from that intersection.... – Cosmin Sep 8 at 8:23
  • Do you still have the points used to create the buffers? Do either the points or the buffers share an id that links them to the polyline they belong to, and if, with what relation (1:1, 1:n, n:m)? Can we assume that each point (not the buffer) is valid (i.e. in proximity to the polyline), or do they need to get checked for distance? – geozelot Sep 8 at 8:24
  • please use the edit link to improve your question with the query you are using so far – Ian Turton Sep 8 at 8:28

You don't really need a buffer here I think, you can just select by distance. If you want to order them I would go with ST_LineLocatePoint, which gives you a value between 0 (start of line) and 1 (end of line) of the closest point of the pole along the cable.

For exemple something like this:

SELECT c.id_cable,
       ST_Distance(c.geom, p.geom) as distance_of_pole,
       ST_LineLocatePoint(c.geom, p.geom) as index_position_along_the_cable
FROM cables AS c
LEFT JOIN poles As p ON ST_DWithin(c.geom, p.geom, 50)  -- For each cable, get all the poles closer than 50m
ORDER BY ST_LineLocatePoint(c.geom, p.geom), ST_Distance(c.geom, p.geom) -- order them by the position of the closest point along the cable, if equality by distance
| improve this answer | |

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.