In PostGIS: how to split linestrings into their individual segments?

Question

I have one table with many linestrings and I want to make a new table containing the individual segments of all the linestrings, no matter if they are grouped line per line or not.

This is my table, really simple:

table myline (
  the_geom geometry(LineString,4326),
  id integer,
)

I already tried with no results code like this:

SELECT ST_AsText( ST_MakeLine(sp,ep) )
FROM
-- extract the endpoints for every 2-point line segment for each linestring
(SELECT
  ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,
  ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep
FROM
   -- extract the individual linestrings
  (SELECT (ST_Dump(ST_Boundary(ll.the_geom))).geom
   FROM mylines ll
   -- eliminate 0 length linestring 
    where st_x(st_startpoint(ll.the_geom))<> st_x(st_endpoint(ll.the_geom))
   ) AS linestrings
) AS segments;

and this:

select st_astext(st_makeline(st_pointn(a.line, a.idx), st_pointn(a.line, a.idx+1))) as txt
from (select the_lines as line, traj_id as idx from mylines) as a

I have to say that I created the lines from a set of GPS points I have from the dataset, so another solution could be create segment lines from the points directly.

John Powell · Answer

You are going about it the right way, using ST_PointN and generate_series. One way of doing this, using dummy data (substitute your own in initial CTE) would be:
WITH 
  sample(geom, id) AS 
     (VALUES
       (ST_MakePoint(0,0), 1), 
       (ST_MakeLine(
            ARRAY[ST_MakePoint(0, 0), ST_MakePoint(10,10),
                  ST_MakePoint(50, 50), ST_MakePoint(100,100)]), 2)
     ),
  line_counts (cts, id) AS 
        (SELECT ST_NPoints(geom) -1 , id FROM sample),
  series (num , id) AS 
        (SELECT generate_series(1, cts), id FROM line_counts)
  SELECT ST_MakeLine(
               ST_PointN(geom, num), 
               ST_PointN(geom, num + 1)) as geom, 
        sample.id 
  FROM series 
  INNER JOIN sample ON series.id = sample.id;

Create some geometries
Count the number of segments per line segment (points minus 1) per geometry id
Use generate_series to create series up to one less than the number of points in line segments
Create the segments for each geometry using id from input geometry (sample, step 1) and series generated (step 3).

The return from this is:

geom                      |  id
LINESTRING(0 0,10 10)     |  2
LINESTRING(10 10,50 50)   |  2
LINESTRING(50 50,100 100) |  2

as expected, nothing from the point, and 3 line segments from the original linestring with 4 points.
There are many other ways of doing this, but I prefer this approach with just one call to generate_series for the indexing into points.

liap307 · Answer

I found this high performance alternative:
WITH segments AS (
SELECT gid, ST_AsText(ST_MakeLine(lag((pt).geom, 1, NULL) OVER (PARTITION BY gid ORDER BY gid, (pt).path), (pt).geom)) AS geom
  FROM (SELECT gid, ST_DumpPoints(geom) AS pt FROM lines) as dumps
)
SELECT * FROM segments WHERE geom IS NOT NULL;

source: http://blog.cleverelephant.ca/2015/02/breaking-linestring-into-segments.html

In PostGIS: how to split linestrings into their individual segments?

2 Answers

Add your own answers!

Ask a Question