How can I transform polylines into points every n metres in PostGIS?

Question

Question in pretty much the same as this, but I need to use PostGIS.

I have a number of polylines, and want to trasform them into points every n metres along these lines.

edit: Thanks a lot to Stefan. My final query was a bit different, probably due to older version of PostGIS (1.5), but in the end I got what was needed. Points were combined into a heatmap of roads density.

Answer 1

This query should do the trick:

WITH line AS 
    (SELECT
        your_polylinestring_id,
        (ST_Dump(geom)).geom AS geom
    FROM your_polylinestring_table),
linemeasure AS
    (SELECT
        ST_AddMeasure(line.geom, 0, ST_Length(line.geom)) AS linem,
        generate_series(0, ST_Length(line.geom)::int, 10) AS i
    FROM line),
geometries AS (
    SELECT
        i,
        (ST_Dump(ST_GeometryN(ST_LocateAlong(linem, i), 1))).geom AS geom 
    FROM linemeasure)

SELECT
    i,
    ST_SetSRID(ST_MakePoint(ST_X(geom), ST_Y(geom)), 31468) AS geom
FROM geometries

1. At first you select the distinct linestrings from your polylinestring column with ST_Dump

2. Then you have to define the measure elements with ST_AddMeasure, starting with 0 (begin of the linestring) and the end of the linestring (same as the length of the linestring). Generate_series creates a series over the this measurement by the step of 10. Here you can define "n metres" (in this example 10 meters). The i value begins anew for every linestring.

3. With ST_LocateAlong and ST_GeometryN you create a multidimensional point geometry.

4. Finally you have to extract the X and Y values of this geometry and create a point from it.

The result could look like this:

EDIT

To be fair, I want to add this information: The idea and the query in this answer is extracted from a query that I'm using to create drape lines from DEMs in PostGIS. The excellent implementation of this is explained in this article from Mathieu Leplatre.