Getting points along line at defined distance using PostGIS?

Question

I was reading a lot of similar questions here but no one ask about PostGIS (or I didn't find it). I have a line in PostGIS and I want to get the point along the line at defined distance.

Is it possible?

I found st_locate_point but it doesn't work for I want to do.

Answer 1

Paolo Corti provides an excellent answer on the postgis mailing list

Use ST_Segmentize and then ST_DumpPoints, like this:

SELECT ST_AsText((dp).geom) As wkt_geom 
FROM (
    SELECT ST_DumpPoints(
        ST_Segmentize(
            ST_GeomFromText('LINESTRING(1 30, 15 30)',28992),
            -- this is the defined distance  
            1
        )
   ) AS dp
) AS foo

Answer 2

Have you tried ST_Line_Interpolate_Point?

Normally it should returns a point interpolated along a line according to the fraction of total length of linestring. The example below shows how it works. You probably need to compute the linestring length before to get the fraction of total length.

--Return point 20% along 2d line
SELECT ST_AsEWKT(ST_Line_Interpolate_Point(the_line, 0.20))
    FROM (
        SELECT ST_GeomFromEWKT('LINESTRING(25 50, 100 125, 150 190)') as the_line)
        AS foo
----------------
POINT(51.5974135047432 76.5974135047432)