Street struggles with Postgis en Openstreetmap data

How can I label the most important streets of a middlesized town based on Openstreetmap data? The obvious way is to use the highway tag/field, which contains values like ‘primary’, ‘secondary’, ‘tertiary’ etc.. I found out that other values like ‘residential’, ‘services’, ‘unclassified’, etc.. are not 100 % useful for distinguishing the most important streets below the ‘tertiary’ level. An approach for those ‘smaller’ streets would be to use the length of a street: the longer, the more important it should be. How do you get the length of any street? Postgis comes to the rescue, as my OSM-data is stored in a postgresql/postgis database. But it’s not without its complications.

First of all, all streets are made up of line segments. Take for example this SQL statement (used in QGis’s DBManager):

SELECT "name", highway, way
FROM planet_osm_line
WHERE (way && ST_MakeEnvelope(202248, 501898, 205180, 504072, 28992)) AND "name" <> ''
ORDER BY "name"

Query all the line segments within a bounding box, which is more or less the Dutch town of Zwolle

If we focus on two streets, the Turfmarkt and the Willem Barentszstraat , we find 5 and 13 line segments respectively. See below a Google Maps view and a line view in QGis with all the different segments.

One complication is the fact that not all streets are constructed as one line. The Turfmarkt, which is a more or less open space with a parking lot in the middle, has two parallel lines and parts of the Willem Barentszstraat  are constructed as a double lane. What is the true length of these streets? Lets deal with these questions later and try just to get the total length of the different segments per street.

st_makeline

We want to somehow merge all the segments in a single feature. The first Postgis function that I came across is st_makeline, but that’s not a good idea. It creates more lines than the actual streetsegments. See code below

SELECT* FROM (SELECT "name", st_makeline(way) AS streetway, st_length(st_makeline(way)) AS streetlength
FROM planet_osm_line
WHERE (way && ST_MakeEnvelope(202248, 501898, 205180, 504072, 28992)) AND "name" <> ''
GROUP BY "name" ORDER BY "name") AS streets WHERE streets.streetlength > 300

It uses st_makeline and st_length and limits the selection by streets that are longer then 300 metres).

Below you see that st_makeline creates extra (the dotted) line segements. This adds a false number of meters, so the lengths of the Turfmarkt and the Willem Barentszstraat are over 845 and 1751 m respectively!

St_makeline seems to make a line from points mainly, so it uses the start- and end vertices of linesegments to make the line and adding lines if necessary along the way. Not a good way to go. A better option seems st_connect.

St_connect

Given the SQL statement:

SELECT * FROM (SELECT "name", st_collect(way) AS way1, st_length(st_collect(way)) AS streetlength
FROM planet_osm_line
WHERE (way && ST_MakeEnvelope(202248, 501898, 205180, 504072, 28992)) AND "name" <> ''
GROUP BY "name ORDER BY "name") AS streets WHERE streets.streetlength > 300

…. the length of both streets result in 369 and 715 meters respectively.

St_linemerge

A proposed solution of more or less the same problem is to be found at: http://stackoverflow.com/questions/34756556/combine-two-linestrings-in-different-rows-using-postgis

which recommends combining the st_collect with the st_linemerge function:

SELECT * from (SELECT "name", st_linemerge(st_collect(way)) AS streetway, st_length(st_linemerge(st_collect(way))) as streetlength
FROM planet_osm_line
WHERE (way && ST_MakeEnvelope(202248, 501898, 205180, 504072, 28992)) AND "name" <> ''
GROUP BY "name" ORDER BY "name" ) AS streets WHERE streets.streetlength > 300

Focussing still on both streets, it results in the same amount of meters. The only difference I found is that st_connect results in only multilinestrings whereas the st_linemerge tries to convert the multilinestrings into simple linestrings if the linesegments form one straight line. It doesn’t affect the length of the streets.

Some thought on complications

The aforementioned ‘double lane’ complication isn’t a big deal for the result I am looking for: double laned streets are probably always important streets. Also the Turfmarkt with its parallel linesegments is essentially a big square and therefore important enough to be labeled.

However, a second complication arises when you consider the disconnected parts of the Turfmarkt. If you have streetsegments with the same name, but in a different town or neigbourhood, you’re in trouble. The SQL code returns only one feature per name, so you get the total length of what are really different streets with a common name. For a single townmap, such as my map of Zwolle, that’s not a problem.

Last complication arises when you want to style the labels based on the highway tag/field, e.g. different font size). In order for you to do that, you have to include the highway tag/field in the subquery, along with the name. The returned recordset consists of features not of streets, but of streets splitted by highway value. See below for the Burgemeester Drijbersingel. I wonder if there is a solution for this?

Ultimately, for my map, I used one layer for motorways, primary, secondary and tertiary roads, as stored in the highway tag and a seperate layer where the length of all streets with the highway tag values:  ‘unclassified’ OR ‘roads’ OR ‘services’ OR ‘residential’ OR ‘pedestrian’, is computed.