Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
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 Answers 2

up vote 1 down vote accepted

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

share|improve this answer
    
Thanks you very much.... –  Amit Sep 18 '13 at 7:27

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.