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?
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
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 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;
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).