0

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.

  • Hi Matej, it would help enormously in testing if you could rewrite the query in your post including some data (include the points as ST_MakePoint and maybe some line segments as a UNION). Also, your query might be simplified with the use of WITH statements – tilt Jan 27 '16 at 6:14
  • Matej, I have done a quick edit of your query to make it more readable -- you need to put spaces after commas, so that you can indent properly. However, I agree with @tilt, you could make it easier to read with some WITHs also. – John Powell Jan 27 '16 at 8:41
  • tilt, John Barça, thank you for replies. in general the situation is: a table of linestrings (generated from OSM data). Those linestrings are interconnected on edges and those two points lay right on one of those linestrings (not neccessarily on the same linestring), but are interconnectable (there is a way between them). I will prepare the test-data for testing. – user263416 Jan 28 '16 at 0:08
  • I have added the data-sets to the initial question. – user263416 Jan 28 '16 at 20:44
2

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

  • Great, this basically solves my problem! Thank you very much. I just have one more question regarding the result: why is the resulting point returned as multipoint? Thank you very much! Matej – user263416 Jan 30 '16 at 12:23
  • Good question, I'm not sure. I seems unlikely that a geometry returns more than a point a 1 specific M value, but probably I'm overseeing something. Why not ask another question about this? I'm also curious. Anaway, you can use an ST_Dump to get the normal point. Please check the manual for an example: postgis.net/docs/manual-2.2/ST_LocateAlong.html – tilt Jan 30 '16 at 18:22

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.