OpenStreetMap schema griping

OpenStreetMap is all kinds of awesome, but boy the data schema is a mess. planet_osm_roads doesn’t really contain all the roads, you want planet_osm_line. (Yes, one name is singular, one is plural). planet_osm_line sure contains a lot of lines, what are they all? The “highway” column tells you. here’s the most popular types of “highway” in the San Francisco metro extract.


select
  count(*) as cnt, highway
from planet_osm_line
group by highway
order by cnt desc;

  cnt  |        highway        
-------+-----------------------
 29165 | residential
  8897 | service
  6307 | 
  5252 | footway
  1870 | motorway_link
  1841 | tertiary
  1765 | secondary
  1150 | motorway
  1113 | unclassified
  1063 | path
   959 | track
   890 | primary
   712 | steps
   594 | cycleway
   191 | trunk
   150 | primary_link
   116 | pedestrian
    65 | trunk_link
    44 | construction
    39 | road
    35 | secondary_link
    24 | tertiary_link
     3 | footway_unconstructed
     2 | raceway
     2 | proposed
     2 | bridleway
     1 | service; residential
     1 | platform
     1 | crossing
     1 | living_street
     1 | abandoned
(31 rows)

It’s not terrible; things that are correctly classified really are. But “all roads” requires identifying several classes, there’s some ambiguity, and some stuff clearly doesn’t belong there. Still the data exists and is free, so I shouldn’t complain too much!

(Migurski’s High Road is a set of SQL views that present a cleaner schema of roads. But just roads, nothing else.)