How to split OSM roads into individual segments at intersections?

Question

I want to create a road-network for use with pgRouting using OpenStreetMap data. I loaded a shapefile from GeoFabrik into a Postgres table (with PostGIS enabled). However, one problem I had was that the roads do not always end at intersections, so I decided to split them all at every intersection or crossing.

To identify all the interesections where roads crossed or intersected I used the following SQL (similar to a previous question):

CREATE TABLE split_points as
SELECT DISTINCT    
   ST_GeometryN(ST_Intersection(a.geom, b.geom),1) as geom      
FROM
   roads as a,
   roads as b
WHERE
    ST_Touches(a.geom, b.geom)
OR
    ST_Crosses(a.geom, b.geom)    
    AND a.gid != b.gid
GROUP BY
   ST_Intersection(a.geom, b.geom);

I now want to split the roads using these points. I used the following approach:

CREATE TABLE split_roads as
SELECT     
    ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom))).geom) As geom,
    generate_series(1,ST_NumGeometries((ST_Split(g.geom, blade.geom)))) as gid
FROM    
    split_points as blade,
    roads as g
WHERE
    ST_Intersects(g.geom, blade.geom);

The problem with this split approach is that the full road length remains in addition to all of the split pieces. To remove these un-split road geometries that were included I used the ST_Equals() function to identify them, and to delete them:

DELETE FROM split_roads USING roads
WHERE ST_Equals(split_roads.geom, roads.geom)

However, this approach does not remove all of the original unsplit geometries (although it does remove some of them). Is there a better approach for deletion (or overall) so that I have only the split geometries in a table?

According to documentation, ST_Split does not return the original, unsplit geometry. What is that extra '.geom' hanging out at the last closing parenthesis in the first line of your SELECT statement? Might be as simple as removing that. — Scro, Oct 05 '12 at 20:05
I guess technically, that would be the second line of the SELECT statement. Also I'm referring to the "split_roads" table creation. It's the line that ends with '))).geom) as geom'. — Scro, Oct 05 '12 at 20:19
hmm, I get an error when I do that. ERROR: function st_geomfromewkb(geometry_dump) does not exist LINE 4: ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom))))... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. — djq, Oct 05 '12 at 20:25
Did the entire line look like this: ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom)))) As geom, — Scro, Oct 05 '12 at 20:38
Sorry, I was confused. The .geom is needed because ST_Dump returns geom,path. Still, ST_Split is not supposed to return original geometry. — Scro, Oct 05 '12 at 20:47
No problem. I thought I might be getting the original geometry back because I have a point at the end of each line. — djq, Oct 05 '12 at 20:51

underdark · Accepted Answer · 2013-12-15T10:52:30.583

14

Simple answer: Don't. You shouldn't do it that way.

From the OSM road Shapefiles, it is impossible to distinguish between intersections and over/underpasses. You'll create intersections that don't exist in reality if you split all seemingly crossing roads.

You'll need to get your hands dirty with the original OSM file, if you don't want to use existing tools such as osm2pgrouting (where the network is small enough) or osm2po.

edited Dec 15 '13 at 10:52

answered Oct 08 '12 at 12:54

underdark

84,148
21
231
413

1

Exactly. This is also another mistake some people do when they process navteq and teleatlas data. Underpass/Overpasses are a pain but a reality. – Ragi Yaser Burhum Oct 08 '12 at 19:10
1

Agree. Welcome to GIS where data is allways more or less bad – simpleuser001 Oct 09 '12 at 12:59

score 7 · Answer 2 · answered Oct 01 '12 at 22:08

7

Not a real solution to your problem, but try osm2po ... it creates perfect SQL code for routing in pgrouting: http://osm2po.de/

answered Oct 01 '12 at 22:08

GIS Student

141
4

1

Thanks for the suggestion. I have tried osm2pgrouting but it requires more memory than my server has and it terminates without finishing. – djq Oct 01 '12 at 22:09
2

@djq osm2po can handle much bigger files than osm2pgrouting. It can even load planet.osm – underdark Oct 04 '12 at 16:27
ah, I originally thought osm2po was a typo. Is there a straightforward of installing it in ubuntu? – djq Oct 04 '12 at 16:44
It's a ready compiled JAR file (java). Just run it as explained on the website. – GIS Student Oct 07 '12 at 01:52

score 3 · Answer 3 · answered Oct 02 '12 at 09:24

3

Another "not real solution to your problem", but our OSM converter splits at intersections while it converts from OSM to SHP. It is more efficient that way, since it can compare the ID of the nodes, rather than doing geometric calculations.

answered Oct 02 '12 at 09:24

Uffe Kousgaard

2,543
15
24

score 3 · Answer 4 · edited Jun 11 '20 at 15:27

About your general problem, using pgRouting: I think @Uffer, @GisStudent and others that are showing how to use "OSC & etc.", they are right. Go following the clues of of "best practices" and "standards"...

About your question: "split roads into individual segments at intersections" or "how to remove all of the original unsplit geometries". I can help if you show here your results here, step-by-step...

First step: topology analysis

 CREATE TABLE split_points_topo as
  SELECT     
    a.gid as gid_a, b.gid  as gid_b, ST_Relation(a.geom, b.geom) as DE9IM_code
  FROM
    roads as a,
    roads as b
  WHERE a.gid != b.gid AND a.geom && b.geom;
SELECT DISTINCT st_geometryType(geom) FROM roads;
 SELECT DISTINCT DE9IM_code FROM split_points_topo;
 -- list here the results o these two queries!  ... after we can continue.

score 1 · Answer 5 · answered Oct 08 '12 at 12:46

1

One way to solve it algorithmically would be to add the start and end point of each whole road to the set of "intersections", so that you can be certain that every segment is between two intersections.

answered Oct 08 '12 at 12:46

relet