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

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.

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
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.

• Can you clarify, b/c I'm not familiar with these SQL constructs: where `WITH line AS` is used? I don't see 'line' elsewhere. – culebrón Mar 1 '14 at 17:46
• @culebrón The `WITH Query` provides the use of temporary tables for larger queries. Here are some information: postgresql.org/docs/8.4/static/queries-with.html. `line` is used in the next subquery `linemeasure`. The selected data in this subquery is based on the selections made in the `line` subquery. They are build upon one another. Hope that helped! – Stefan Mar 1 '14 at 21:20
• In PostGIS older than 2.0 the function is `st_locate_along_measure`. – culebrón Mar 3 '14 at 12:46