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.

Improve this question
5
  • Could you give more info and where the red lines come from?
    – John Powell
    Commented May 25, 2016 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
    Commented May 25, 2016 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
    Commented May 25, 2016 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
    Commented May 25, 2016 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
    Commented May 26, 2016 at 3:52

1 Answer 1

Reset to default
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().

Improve this answer
1
  • Still working on this. Too much work. Will check and accept soon.
    – Magno C
    Commented May 10, 2017 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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