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;

split at intersection

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.

  • Of course it fails, ST_UNION aggregates the rows and ST_DUMP returns a set. You need to split each line seperately only by the other geometries it touches (and my lunch break is over so I can't help more). – Jakub Kania Sep 12 '13 at 12:50
  • Hi Jakub, I am new user , I dont have much experience on postgis/postgres. I will try to split gerometries one by one using touches. – Amit Sep 12 '13 at 13:12

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


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


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

  • Hi Micha, I tried above query but it gives error and also i need to attach attribute so i tried following query SELECT r1.road_type,r1.road_name,ST_Dump((ST_Split(r1.geom, ST_Intersection(r1.geom, r2.geom)))) FROM roads AS r1 JOIN roads AS r2 ON ST_Intersects(r1.geom,r2.geom) WHERE r1.gid <> r2.gid it gives error as ERROR: Splitting a Line by a MultiPoint is unsupported – Amit Sep 16 '13 at 5:48
  • Hi Micha, I have updated query like this SELECT distinct((ST_Dump((ST_Split(r1.geom, ST_Intersection(r1.geom, r2.geom))))).geom),r1.road_type,r1.road_name,r1.gid FROM roads AS r1 JOIN roads AS r2 ON ST_Intersects(r1.geom,r2.geom) WHERE r1.gid <> r2.gid and st_geometrytype(ST_Intersection(r1.geom, r2.geom))='ST_Point'; But it skips maximum records – Amit Sep 16 '13 at 9:37
  • Hi Micha, is there any other way which can split road. I tried this query but it misplaced the attributes at crosses and also skip the attributes SELECT r2.geom,r1.road_type,r1.road_name,r1.gid FROM roads AS r1 ,(select (st_dump(st_union(geom))).geom from roads) AS r2 WHERE ST_intersects(r1.geom,r2.geom) and ST_touches(r1.geom,r2.geom) = false ; Do you have any better solution? .... ,Thanks – Amit Sep 16 '13 at 16:47
  • The "Split by MultiPoint is not supported" error is probably due to one line crossing another more than once. If you do (as in your 2nd comment) GeometryType='POINT' then you will loose those lines, as you pointed out. The only idea that comes to mind is outside of PostGIS: importing the road layer into GRASS will automatically enforce topology, and thus split all lines at every intersection. May be worth trying... – Micha Sep 17 '13 at 5:28

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.