5

I have a LINESTRING from a pgRouting query. The start point and/or end poit of this line does not connect exactly the points I've clicked on the road and give me all the road segment of that edge (passing the point to next vertice) or stops in the nearest vertice (before the point).

I need to change the points to connect the line result (red lines on the images) near the points I've clicked on the map (blue circles).

My Kung-pgRouting-fu is like this tutorial http://workshop.pgrouting.org/chapters/wrapper.html#return-route-with-network-geometry (function pgr_fromAtoB()) and is working fine.

The function pgr_fromAtoB() in tutorial gets the nearest roads (edges) from the points (clicked on web interface an can be arbitrary locations) and connect its vertices using pgRouting.

enter image description here enter image description here

EDIT: I'm testing this info now: How to find the nearest point projected on the road network?. I think this could help me.

  • Could you give more info and where the red lines come from? – John Powell May 25 '16 at 11:20
  • What more can I tell? Don't focus on the red line layer. It is just a LineString layer came from a pgRouting query ( go to tutorial link and you'll see ). Keep the focus on how to change the start and end points of a LineString geometry (even without any graphic interface). pgRouting connects only vertices and I must complete the line until a point in the next edge (or previous) because I've selected the middle of a road (edge). In the second image, you can see pgRouting is correct by connecting the closest vertices of my points but I need to complete the path to the points. – Magno C May 25 '16 at 11:42
  • Long roads may cause proplems because it may connect vertices too far from the exact point I selected. I have the blue circles coordinates ( a POINT geometry ) – Magno C May 25 '16 at 11:45
  • Idea! I'll try to create a line from the points to the ends of the LineString... duuuh! But.... how to create a line from the point that follow the road until it reaches the line? – Magno C May 25 '16 at 11:51
  • Regarding how to create a line from the point that follows the road: you can use ST_Line_Locate_Point to find out how far along the line your point is. Then you can use ST_Line_SubString to extract that portion of the line. – amball May 26 '16 at 3:52
1

This statement should help:

SELECT ST_LineSubstring(geom, ST_LineLocatePoint(ST_LineMerge(geom), ST_SetSRID(ST_MakePoint(-90, 30),4326)),1)
FROM roads
WHERE id=1234

It returns the partial geometry of a nearby line from a point.

Note: The geom in my data is a MULTILINESTRING, so it had to be simplified to a LINESTRING in order to pass into ST_LineLocatePoint().

  • Still working on this. Too much work. Will check and accept soon. – Magno C May 10 '17 at 11:15

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.