12

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.

enter image description here

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.

1
  • Can you share maybe a little of what you tried in terms of SQL? That would probably help you find a solution. Have you tried a combination of ST_DumpPoints and ST_Split?
    – SaultDon
    Dec 19, 2015 at 2:59

4 Answers 4

24

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;
  1. Create some geometries
  2. Count the number of segments per line segment (points minus 1) per geometry id
  3. Use generate_series to create series up to one less than the number of points in line segments
  4. 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.

5
  • Does this not work for you in some way? If you leave a comment, I can attempt to fix. Dec 24, 2015 at 9:30
  • What does CTE stand for in this context? Feb 6, 2018 at 11:19
  • 2
    @EvaGroßmann, CTE means common table expression, it is the official term for queries starting with WITH, see the CTE docs. Feb 6, 2018 at 11:58
  • Using a JOIN LATERAL would make the SQL even simpler.
    – dr_jts
    Aug 22, 2020 at 21:21
  • @dr_jts, yes it would, but that was quite new back when I wrote this answer. Feel free to edit the answer. Aug 24, 2020 at 8:39
5

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

2

Pre-PostGIS 3.2 solution

WITH sample_data AS (
    SELECT 1 id, ST_GeomFromText('LINESTRING(0 0,1 0,1 1,7 3)') geom
    UNION
    SELECT 2, ST_GeomFromText('LINESTRING(2 0,2 2,0 2,9 5)')
)
SELECT id,(ST_Dump(ST_Union(geom,geom))).geom
FROM sample_data

Here ST_Union turns the LINESTRING into a MULTILINESTRING which subsequentially gets dumped into segments by ST_Dump. I do not know why this works. I found it by an accident.

1

As of PostGIS version 3.2.0 there is a built in function for this:

geometry_dump[] ST_DumpSegments(geometry geom);

You can find complete description with examples in the official documentation - ST_DumpSegments.

4
  • I wonder if this is related but (at least on PostGIS 3.2) the following works too: SELECT ST_AsText((ST_Dump(ST_Union(geom,geom))).geom) FROM (SELECT ST_GeomFromText('LINESTRING(0 0,1 0,1 1)') geom)_. I do not know why. I found it by an accident.
    – loop
    Mar 29 at 11:44
  • ST_Union turns the LINESTRING into a MULTILINESTRING which subsequentially gets dumped into segments by ST_Dump.
    – loop
    Mar 29 at 11:54
  • 1
    @loop you should make this an answer, this also works on postgis 2.5 :)
    – Cactusbone
    Apr 7 at 12:18
  • @CharlyKoza That's great news! I was worried that the invention came too late and I was hoping that someone could confirm if the method works in older versions. Thank you.
    – loop
    Apr 8 at 17:04

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.