552901 (2) [Avatar] Offline
[ 43 KB ]
I have 2 geometry table, 1 consists of GPS points (point data with timestamp) that is snapped to roadlinks (linestring). I would like to create a linestringM format from these 2 tables.

I have tried:

1) Split the linestring with points.

SELECT st_astext(ST_CollectionExtract(ST_Split(ST_Snap(n.geom, p.geom, 1), p.geom),2)) AS geom
INTO TEST3 FROM line n, point p LIMIT 1;
2) Add M coordinate

I couldn't get linestringM.

Thanks in advance
regina.leo (265) [Avatar] Offline
This question is probably best asked on postgis-users or gis.stackexchange.com since it's not specific to a chapter in the book.

However since you asked here, here is my answer.

I think what you want is to start with your gps points and use the time to order them and I presume you want the time to be the M dimension.


SELECT ST_MakeLine(ST_MakePointM(ST_X(n2.geom), ST_Y(n2.geom), p.gps_time) ORDER BY p.gps_time) AS geom 
INTO test3
FROM line n INNER JOIN point p ON ST_DWithin(n.geom, p.geom, 1)
    CROSS JOIN LATERAL ST_Snap(p.geom, n.geom, 1) AS n2(geom);

So what the above does is snaps the point to the closest point on the line (which is n2(geom) ).
Note in your example, you were snapping the the line to the point, which I don't think was you intent.

order is ST_Snap(a, b, tol) -> new a snapped to b.

Unfortunately there is no ST_AddMeasure in PostGIS for points, so that's why I am using ST_MakeM to rebuild.

Since I presume you want your gps path snapped to the road, you really want your points ordered in the order of time travel (not necessarily the orientation of the road).

thus the need for ST_MakeLine (point ORDER BY time)

I also added in a join clause between your line and point since I presume you only want lines that are within tolerance of the point to be considered.