13

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. enter image description here

17

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: enter image description here

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
  • 2
    @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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.