7

I have zero experience with PostGIS and SQL, but I'm trying to create points along the line at specific distance. I am trying this method: Getting points along line at defined distance using PostGIS? but I cannot create an actual point table with geometry using my line table. I was trying something like this:

CREATE TABLE point_table AS    
     SELECT gid, ST_AsText((dp).geom) As wkt_geom 
         FROM (
             SELECT ST_DumpPoints(
        ST_Segmentize(
            ST_GeomFromText('LINESTRING(1 30, 15 30)',5514),
            1
        )
   ) AS dp
) AS geom from my_line;

But I am not successful at all. Can some of you explain to me how to do this basic task since I am a complete noob?

1
  • 1
    Does the select statement work?
    – Ian Turton
    Oct 17 at 18:22
5

Unfortunately in this case, ST_Segmentize only inserts vertices into the linear component of a geometry, and only when the distance between consecutive points is longer than the given max_segment_length - a point dump will contain the initial vertices as well.

If that is undesirable, use a ST_LineInterpolatePoint sequence instead; note that the actual issue that you are facing (apart from a few syntax issues) is resulting from ST_AsText, which, quite literally, returns a WTK string (type TEXT) from the given GEOMETRY - I included the proper way to also register the new GEOMETRY(POINT) column in the system:

CREATE TABLE point_table AS ( 
  SELECT n,
         ST_LineInterpolatePoint(geom, LEAST(n*(<desired_distance>/ST_Length(geom)), 1.0))::GEOMETRY(POINT, <SRID>) AS geom
  FROM   <line_expression|table>
  CROSS JOIN
         Generate_Series(0, CEIL(ST_Length(geom)/<desired_distance>)::INT) AS n
);

Here

  • Generate_Series creates a sequence n over all needed points
  • the LEAST expression returns an incremental fraction of line length, or 1.0 for the last point

This also works for the GEOGRAPHY type (e.g. per CAST), using meter as unit (for <desired_distance>).

Note that the returned points will always contain the start point as well as the end point, even if the distance to the second last point is shorter than <desired_distance> - so counting lengths begins at the start point. This could easily be altered to e.g. return the point sequence spread evenly over the length of the line (i.e. having a calculated offset), or to return a fixed number of segments with calculated equal distance

4

Using QGIS

Since you added QGIS tag to your question, you can use the very simple processing tool called Points along geometry.

Points along geometry processing tool

It allows you to create points along the line.

points along line using QGIS

Using Postgis (first attempt)

Using Postgis, you can use the query you already wrote.To make it more usable, I've created another (temporary) table with the result.

My query is:

create table outros.pointalongline as 
select (foo.dp).path[2], (foo.dp).geom 
from (select ST_DumpPoints(ST_Segmentize(geom, 500)) as dp 
from outros.curvas_isobatimetricas ci where id = 295) as foo;

Adding the result to QGIS (along side with the previous points created with QGIS processing tool), I got the result:

points along line using Postgis

The solution differs from QGIS. The reason is well explained by @geozelot in another answer.

Using Postgis (based on @geozelot answer)

Using ST_LineInterpolatePoint, we can get the same result.

create table outros.pointalongline4 as 
  SELECT n, ST_LineInterpolatePoint(st_linemerge(geom), LEAST(n*(500/ST_Length(geom)), 1.0))::GEOMETRY(POINT, 5016)
  FROM   outros.curvas_isobatimetricas
  CROSS JOIN
         Generate_Series(0, CEIL(ST_Length(geom)/500)::INT) AS n
  where id = 295;

The result is identical to QGIS.

enter image description here

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.