[GIS] How to change start and end point of a linestring

pgroutingpostgis

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.

Best Answer

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().

Related Question