We have a route network stored in the routes table (LINESTRINGs). Basically every sedment (between two vertexes) is inserted as a separate row in this table.
Among that we have a locations table (type POINT) which contains the coordinates.
What I want to achieve is to get the distance between two points over the road. I have the geometries for points and plenty of linestrings for every road.
What I have done so far after searching is the following query (not working):
SELECT ST_Length(ST_Line_Substring(line, ST_Line_Locate_Point(line, ST_ClosestPoint(line, fpt.the_geom)), ST_Line_Locate_Point(line, ST_ClosestPoint(line, tpt.the_geom)))) As length_m, ST_AsText(tpt.the_geom) as to_point, ST_AsText(fpt.the_geom) as from_point FROM ST_Segmentize((SELECT ST_LineMerge(ST_Collect(the_geom)) FROM (SELECT the_geom FROM routes WHERE day='2015-12-30') AS sq), 0.00001) As line, (SELECT * FROM ways_vertices_pgr WHERE id=3017) AS fpt, (SELECT * FROM ways_vertices_pgr WHERE id=18207) AS tpt
The routes represent the routes driven by a car so basically some routes can be used multiple times. Can this be a problem anyhow?
What bothers me are 2 things: - firstly, how to "merge" plenty of those linestrings to get a full linestring on which I can then get the infromation from functions like ST_Line_Locate_Point where you get (and somewhere set the floating values representing the position on the total lenght of the linestring) - how to build the query then to get point on the line that is exactly x kilometers away from another point (which is the fixed one for every case). I was playing here to get the distances between two sample points on the line, but sadly cannot move further on.
I have really spent quite some time on this issue and cannot move forward. What is interesting in the above query: If I limit the select from routes to 1, it seems to be working, but of course only one part of the routes is added, otherwise I get the: ERROR: line_locate_point: 1st arg isn't a line error.
The sample data sets are:
Linestrings representing small fraction of routes:
"LINESTRING(-1.3326397 50.9174932,-1.3319842 50.9166939)"
"LINESTRING(-1.3333297 50.9183351,-1.3332187 50.9182141,-1.3330436 50.9179954,-1.3326397 50.9174932)"
"LINESTRING(-1.3338982 50.9186728,-1.333581 50.9185143,-1.3333297 50.9183351)"
"LINESTRING(-1.3341242 50.9187719,-1.3338982 50.9186728)"
"LINESTRING(-1.335291 50.919197,-1.3346072 50.918916,-1.3341242 50.9187719)"
fpt (from point - the starting point) coordinates:
The final goal is to get the tpt coordinates for the point that is on the linestrings (on the route) that is x, let's say 100 meters, away from the fpt over the route.