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'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.)

share|improve this question
Please start accepting answers if they solved your problems. With accept rate of 0% people are less likely to want to answer your questions. –  underdark Aug 13 '11 at 14:42
When I saw that '0% accept rate', I reviewed my prior questions, and none of them really were a solid "completely answers the question". I went through them again just now, and decided two were 'close enough', but I don't think it's right to accept answers that aren't really answers! Btw, thanks for all the help you've provided here and on your blog. –  joshdoe Aug 13 '11 at 20:28
As this is part of a larger (personal) research project, I thought I'd link to it here. I hope to create a Safe Routes to School Mapping Toolkit using OpenStreetMap, pgRouting, QGIS/Mapnik, and who knows what else open source software. While I'm very interested in this idea, I don't have much free time, so I'd love if anyone else wanted to get involved, or even take off with the idea, so long as it's OSS ;). –  joshdoe Aug 14 '11 at 0:25

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
That might be the case if you use the Shapefile extracts, e.g. from CloudMade, however OpenStreetMap itself definitely uses a topological model. Tools like osm2pgrouting, osm2pgsql, etc, should all preserve the network connections. For my case I have a service road crossing a secondary road, and they share a vertex, however the service road continues for some time. I wish to allow routing along the rest of the service road, but make it impossible to cross the secondary road. If I can split at intersections, then I should be able to exclude just the short connecting portion that crosses. –  joshdoe Aug 15 '11 at 12:38
I've just been using whatever is available on the latest Ubuntu, which I'm guessing is not 2.0, though I'm not at the machine right now. –  joshdoe Aug 15 '11 at 12:38
ST_Split seems to require 2.0 that's why I asked. –  underdark Aug 15 '11 at 13:07
about the topological model, I guess you are right. –  underdark Aug 15 '11 at 13:18
Ah, didn't notice ST_Split required 2.0. I believe I'm running 1.5, oh well. I guess my question now is how to split without that function, or how to create a cost which is a function of the attributes of intersecting edges. The latter belongs in another question. If no one can answer the former, than I'll accept your answer in a day or so. Thanks! –  joshdoe Aug 15 '11 at 13:37

Your Answer


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.