A couple weeks ago, I had been interested to know how how many miles of roads there are in Cleveland with all of the complaining that people have about the amount of potholes there are in Cleveland and because, well, I was curious.
By roads, I am referring to roads that are publicly accessible to vehicles.
I have been an active contributor to OpenStreetMap (OSM), a global geographic data that anyone can edit (think the wikipedia of google maps) in the Cleveland area, so Cleveland’s data was very current: I had the updated all of the changes the InnerBelt and other semi-permanent road closures in Cleveland, and a few new streets and re-openings (the roundabout in the flats for example, West 3rd Bridge). It is likely the most current database of Cleveland roads that exists, even compared to the county’s own database.
The following will guide how to do the same analysis for your city. Note, some cities may not be as current as Cleveland is in OSM.
(This assumes you know how to use postgis and osm2pgsql, and are able to create a osm2pgsql database that only contains your city. To learn how to create a postgis database of OSM data only containing your city via osm2gpsql, read this tutorial that I wrote: https://www.openstreetmap.org/user/skorasaurus/diary/15250
This tutorial is also ideal for people who are learning how to do some basic queries in postgis/postgresql from OSM data as I explain what columns and functions I use.
So, once you created your database of your city:
First, I crafted this query after a lot of trial and error to ensure that I was selecting all of the roads in Cleveland:
select highway, name, way, st_length(st_transform(way,3637)) AS length FROM planet_osm_line WHERE highway NOT IN ('construction', 'footway', 'path', 'steps', 'track', 'cycleway', 'pedestrian', 'abandoned', 'disused') AND (service NOT IN ('parking_aisle', 'driveway') OR service is null) AND (access NOT IN ('no', 'private') or access is null)
HOORAH!
So, what does this SQL all mean?
select highway, name, way, st_length(st_transform(way,3637)) AS length FROM planet_osm_line
I selected the highway and name columns. These columns are created in osm2pgsql, filled with values of each node/line in OSM (In OSM, objects are represented with tags, which is written out as “key=value”) for example, the street that the Simpsons live on could have the tags: highway=residential and name=Evergreen Terrace)
planet_osm_line is the name of the table (generated by osm2pgsql) that contains all of this data. The ‘way’ column contains the geography (the coordinates of where these lines are located). This ‘way’ column is generated by osm2pgsql.
You can use this query to browse some different streets in your city and make sure that all of the streets that you wish to include are listed..
Don’t worry if you see a street listed twice, it’s not that it was included one too many times, but a road that a single, straight way in real life may consist of 2 or 3 connected highways each with the same name in OSM, depending on a variety of factors (For example, part of the road may be on a bus route, part of the road may be one-way)
st_length measures the geometry that is inside the parentheses. What does it measure in centimeters? nautical miles? The units it measures is based on the coordinate system that is inside it (in our case, meters). At first, I just tried st_length(way) but I was using a test/sample road near where I grew up to ensure my results would be correct). With st_length(way), I was receiving 862 meters… not good.
I had measured my test/sample road in google maps and in Josm, using its measurement plugin.. My test/sample road was 646 meters with josm’s measurement plugin.
my test/sample road query:
select highway, service, way, st_length(st_transform(way,3637)) AS length FROM planet_osm_line WHERE name in ('Name of the street Ave');
Back to the st_length… I realized that’s because st_length was using the projection that osm2pgsql had my database in, 900913 (this is set by default).. So, I had to change the geometry to another projection system. ..I found that I could do this by using st_transform – http://postgis.org/docs/ST_Transform.html , and inputting the column with the geometry and the coordinate system.
I knew that Northern Ohio was 3637 (EPSG), so I decided to transform to that http://spatialreference.org/ref/epsg/3637/ …. and I was right on the money! 647.247 meters. A little more than 1 meter off on 600 meters.. I’ll take that margin of error :)
WHERE highway NOT IN ('construction', 'footway', 'path', 'steps', 'track', 'cycleway', 'pedestrian', 'abandoned', 'disused')
This selects all lines that have the “highway” key are not tagged as highway=construction, footway, path, track, cycleway, pedestrian, abandoned, or disused. Steps, dirt paths, sidewalks (known as footways in OSM lingo), cycleway (Dedicated bike paths), and other very specific tags are not included in my query and I was not trying to measure these.
As mentioned earlier, I just wanted to measure ways that were open to the public, ones that people would drive on, that are publically maintained.
AND (service NOT IN ('parking_aisle', 'driveway') OR service is null) AND (access NOT IN ('no', 'private') or access is null)
I didn’t want to include ways that were driveways (which are tagged as service=driveway) or the roads withing parking lots (known as service=parking_aisle in OSM). In most instances, a highway will not have a service tag, so I also included or service is null. I also didn’t want roads that were not accessible to the public (roads inside of inside industrial plants in Cleveland’s industrial valley).
So… My selection of roads is what I want, so I now had taken the task of how to add them all up together…
Now to find the length of these:
I didn’t know the proper syntax for sum, so I tried a couple things like:
select highway, service, way, st_length(st_transform(way,3637)) AS length, sum(length) FROM planet_osm_line
and
select highway, service, way, sum(st_length(st_transform(way,3637)))
– received message in pgadmin that “column “planet_osm_line.highway” must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select highway, service, way, sum(st_length(st_transform(way…
^
After reading about aggregate functions, I learned that I couldn’t include highway, service, and way in the first part of the query,
and I realized, I didn’t need to at this point; I just wanted the sum.
select sum(st_length(st_transform(way,3637))) from planet_osm_line where highway NOT IN ('construction', 'footway', 'path', 'steps', 'track') AND (service NOT IN ('parking_aisle', 'driveway') OR service is null) AND (access NOT IN ('no', 'private') or access is null)
And this query worked! It gives the sum, 2,361,057 meters which is 1467.09 miles.
But if you want to break it down by what kinds of roads there are, you need to use a group by.
select highway, sum(st_length(st_transform(way,3637))) from planet_osm_line where highway NOT IN (‘construction’, ‘footway’, ‘path’, ‘steps’, ‘track’, ‘cycleway’, ‘pedestrian’, ‘abandoned’, ‘disused’) AND (service NOT IN (‘parking_aisle’, ‘driveway’) OR service is null) AND (access NOT IN (‘no’, ‘private’) or access is null) group by highway
I’m grouping by the highway column, which is the from the highway tag in osm.
highway | sum
—————-+——————
unclassified | 91555.5506847939
primary | 79289.4305883909
secondary | 108610.45598608
motorway | 148936.906333119
tertiary | 190819.531876563
tertiary_link | 1749.67663661471
motorway_link | 104634.677558715
secondary_link | 2884.04084138583
primary_link | 372.479453999729
service | 140106.53820064
residential | 1480741.60563354
(11 rows)
(in metric):
Or in other terms:
Freeways: 92.54 miles
Freeway on/off ramps: 65.01
service roads (alleys): 87 miles
residential streets: 920 miles
main arteries (example: Chester, Lorain, East 55th, Pearl), all of these roads in Cleveland are 3+ lanes): 238.43
unclassified (two lane roads with no/few houses on them): 57 miles
Results and caveats:
I was bit surprised that freeway on/off ramps were that long.
Roads that have a physical separation between them (Chester, Euclid) are counted twice, because they are classified as two separate roads in the OSM database.
These results include roads within cemeteries! I’m working to update my results so they are not included.