Breaking lines in PostGIS?

Question

I have a roads table and I want to break the lines in the intersections like the v.clean tool from GRASS (I'm talking about how the break tool in the parameters works).

How can I do the same process in PostGIS?

Answer 1

Try using ST_PointN and generate_series.

You could look through tutorial here from @Paul Ramsey Breaking a Linestring into Segments

CREATE TABLE lines ( 
  gid integer primary key, 
  geom geometry(Linestring, 4326)
);

INSERT INTO lines VALUES (1, 'SRID=4326;LINESTRING(1 1, 2 2, 3 3, 4 4)');
INSERT INTO lines VALUES (2, 'SRID=4326;LINESTRING(0 1, 0 2, 0 3, 0 4)');

WITH 
dumps AS ( 
  SELECT gid, ST_DumpPoints(geom) AS pt FROM lines
), 
pts AS (
  SELECT gid, (pt).geom, (pt).path[1] AS vert FROM dumps
) 
SELECT a.gid, ST_AsText(ST_MakeLine(ARRAY[a.geom, b.geom])) AS geom, 
       a.vert, b.vert 
FROM pts a, pts b 
WHERE a.gid = b.gid AND a.vert = b.vert-1 AND b.vert > 1;

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

Answer 2

You could use ST_Node for this. It will node geometries on intersections but note the comment in the docs that you should use Geos 3.3.2 or higher (and PostGIS 2.x).