Split edges at intersections in PostGIS

Question

I'm attempting to do a walking distance analysis using OpenStreetMap data (osm2pgrouting) and PostGIS and following underdark's blog posts, however I need to exclude residential and service roads that cross primary and secondary roads, as they are unsafe to cross. To do this I think my first step is to split edges at intersections. I'm guessing I'll want to use the ST_Split function, but I'm not sure how to put it all together in one SELECT statement.

(A later question will be how to remove such crossing edges based on the highway=* tag, but I need to figure this one out first.)

Answer 1

Pedestrian routing is quite a challenge and requires a lot from the data to work properly. Ideally, you would know which roads have sidewalks and whether they only exist on one side of the road. Also, it would be good to know the locations of signaled pedestrian crossings.

I need to exclude residential and service roads that cross primary and secondary roads, as they are unsafe to cross

Do you also consider it unsafe to walk along those primary/secondary roads?

If you exclude those residential and service roads, you can't start or end a route there. Or are you just planning to route from node to node?

Instead of excluding the roads, you could add turn restrictions that make it impossible to cross unsafe roads.

---

Anyway, splitting can be done using ST_Split as you already mentioned. I haven't tested this, but it should work something like this:

SELECT ST_Dump(ST_Split(s.the_geom, u.the_geom))
FROM save AS s, unsave AS u

Where save and unsave are views dividing the road table into two classes. Don't forget spatial indexes to speed things up.

ST_Split seems to require PostGIS 2.0.

I'm surprised osm2pgrouting does not take care of splitting network edges at crossings though.