7

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.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Amit
  • 403
  • 1
  • 4
  • 14
  • 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

2 Answers2

7

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

leplatrem
  • 244
  • 1
  • 2
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

Micha
  • 15,555
  • 23
  • 29
  • 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 05: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 09: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 05:28