If I have a cable route linear referenced and stored in a LinestringM with M value is the length (in meters) from the start of the route - for example:
LINESTRING M (0 0 0, 10 0 20, 12 0 40, 20 0 50, 21 0 70)
Now I know the cable route is broken at 45 meters from start point, that would be in the middle of two points (12 0 40) and (20 0 50) (see the M value). With Postgis, how can I get the broken point coordinate - which should be POINT (16 0) in this case?
The function needed is ST_LocateAlong
From the manual page example, I thought it just works with measures that already exists in the LinestringM but after testing, this works:
SELECT ST_AsEWKT((ST_Dump(the_geom)).geom)
FROM
(SELECT ST_LocateAlong(
ST_GeomFromEWKT('LINESTRINGM (0 0 0, 10 0 20, 12 0 40, 20 0 50, 21 0 70)'), 45) As the_geom) As foo;
Returns:
"POINTM(16 0 45)"
Here is an example working for a line with only two points in it. If you have more than 2 points in your line, you will have to loop through the points and sum the Z value.
Anyway, You can use ST_Line_Interpolate_Point to get the point from a fraction of the total distance. Here you can calculate 10 / the_Z_dimension_of_the_end_point:
SELECT
ST_AsText(
ST_Line_Interpolate_Point(
geom,
(10 / ST_Z(ST_EndPoint(geom)))
)
)
FROM
(SELECT ST_LineFromText('LINESTRING(0 0 0, 10 0 20)') as geom) as yourline
This will give you:
"POINT Z (5 0 10)"
PS: Note that in 2.1 the function seems to have been renamed ST_LineInterpolatePoint
