Split Line at intersection and attach attributes

Question

I want to slit line at intersection and attach attributes and export I have roads table with columns id,road_name,road_type,geom with 2115 records. I try using this query - select st_astext((st_dump(st_union(geom))).geom) from roads; it successfully splits the line gives 5114 records but when i am trying to attach attributes it fails select id,road_name,road_type,st_astext((st_dump(st_union(geom))).geom) from roads;

I need to split line at intersection also keep attributes means line 1 divide in 3 having same id is 1 , road_name is abc similar to line 2 divide in 2 having same id is 2 , road_name is xyz and so on.

Answer 1

You could also use PostGIS topologies, they do just that.

http://blog.mathieu-leplatre.info/use-postgis-topologies-to-clean-up-road-networks.html

You can join your road table on topology edges, using this query:

SELECT r.road_type, r.road_name, e.geom
FROM roads_topo.edge e,
     roads_topo.relation rel,
     roads r
WHERE e.edge_id = rel.element_id
  AND rel.topogeo_id = (r.topo_geom).id

I will add that part to the article. Thanks for insisting btw, I could dig into topology internal tables and attributes :)

Answer 2

In PostGIS 2.0+ there's ST_Split(), so you could probably do something like: (not tested)

SELECT ST_AsText(ST_Dump(ST_Split(r1.geometry, ST_Intersection(r1.geometry, r2.geometry))).geom)
FROM roads AS r1 JOIN roads AS r2 ON ST_Intersects(r1.geometry,r2.geometry)
WHERE r1.id <> r2.id

I think this might fail in cases where one road crosses another more than once... HTH, Micha