I have roads of a country having 899371 features, which were in shapefile and I had imported to PostGIS. I am trying to intersect line at every junction. I have used following commands:
CREATE TABLE line_intersection AS SELECT DISTINCT (ST_DUMP(ST_INTERSECTION(a.geom, b.geom))).geom AS ix from roads_with_type a INNER JOIN roads_with_type b ON ST_INTERSECTS(a.geom, b.geom) WHERE geometrytype(st_intersection(a.geom, b.geom)) = 'POINT';
CREATE INDEX ON line_intersection USING gist(ix);
CREATE TABLE line_union AS SELECT ST_UNION(geom) as geom FROM roads_with_type;
CREATE INDEX on line_union USING gist(geom);
All above commands run successfully. How ever the last command I run, it takes more then 12 hours and still running. My last command is :
CREATE TABLE line_segments AS SELECT(ST_DUMP(ST_SPLIT(a.geom,b.ix))).geom FROM line_union a INNER JOIN line_intersection b ON ST_INTERSECTS(a.geom, b.ix);
Is there any faster way to do this? I am using PostgreSQL 12 and PostGIS 3.0.