PostGIS Get the POINT that is x meters away from a fixed point on the route (LINESTRING)

Question

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:
"POINT(-1.3333297 50.9183351)"

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.

Answer 1

If I understand correct, your question consists of 2 parts: 1. How to get many linestring to merge to 1 2. How to get the point 1000 km further down the road from point 1

Part 1 needs some more explanation from you. It seems you already did a linemerge and that works for me as well with the data you give. Maybe you can post a new stackexchange question with what precisely is not working for you. Merging linestrings from a line spaghetti can be difficult!

Part 2 I'll answer here.

Your query was already well on the way but needed some cleanup and the use of the 'measurement' option in postgis. One more important part of it is that you first have to reproject your dataset to something else than lat/lon, because you would get distance in degrees otherwise. Here is what I made of it:

    WITH lines AS (
    --CREATE THE INITIAL DATASET WITH LINESTRINGS
    SELECT ST_LineFromText('LINESTRING(-1.3326397 50.9174932,-1.3319842 50.9166939)',4326) geom
    UNION ALL
    SELECT ST_LineFromText('LINESTRING(-1.3333297 50.9183351,-1.3332187 50.9182141,-1.3330436 50.9179954,-1.3326397 50.9174932)',4326) geom
    UNION ALL
    SELECT ST_LineFromText('LINESTRING(-1.3338982 50.9186728,-1.333581 50.9185143,-1.3333297 50.9183351)',4326) geom
    UNION ALL
    SELECT ST_LineFromText('LINESTRING(-1.3341242 50.9187719,-1.3338982 50.9186728)',4326) geom
    UNION ALL
    SELECT ST_LineFromText('LINESTRING(-1.335291 50.919197,-1.3346072 50.918916,-1.3341242 50.9187719)',4326) geom
)
,route AS (
    --MERGE THEM INTO ONE LINE AND TRANSFROM TO MERCATOR PROJECTION
    SELECT ST_Transform(ST_MakeLine(geom),900913) geom FROM lines
)
,routem AS (
    --ADD A MEASUREMENT ALONG THE LINE FROM 0 TO THE TOTAL LENGTH AND STORE THE LENGTH (for later use)
    SELECT ST_AddMeasure(route.geom, 0, ST_Length(route.geom)) geom, ST_Length(route.geom) l FROM route
)
,point AS (
    --CREATE OUR INITIAL POINT AND TRANSFORM TO MERCATOR PROJECTION
    SELECT ST_Transform(ST_PointFromText('POINT(-1.3333297 50.9183351)',4326),900913) geom
)

SELECT
    ST_AsText(point.geom) as startpoint,
    ST_AsText(
        --GET THE SECOND POINT ACCORDING TO DISTANCE ALONG LINE
        ST_LocateAlong(routem.geom,
            --GET THE DISTANCE OF THE FIRST POINT AND ADD SOME DISTANCE (here 100m)
            ST_LineLocatePoint(routem.geom, point.geom) * l + 100)
        ) endpoint
FROM 
      routem,
      point

I'm sure there is more than 1 way to get this done, but I believe this is the easiest and shortest. The linear referencing tools in postgis can be a little bit confusing, since some of them work with fractions and others with real measurements. Reading the docs a couple of times helped for me ;)

Hope it serves you.

Best, Tom