1

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?

3

Try using ST_PointN and generate_series.

enter image description here

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

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).

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.