We displayed a set of ways imported from Open Street Map in SQL Server Management Studio. It looked like this:
Now, as I stated previously, OSM ways don’t equate to roads. Ways can be any arbitrary series of nodes, so although at first glance it may appear to be so, the map above does not represent a roadmap. A single way may represent several roads, or only a single segment of one road. Many ways are nothing to do with roads at all – they may be rivers, or railway lines. Ways may also denote the boundaries of an area, such as a county, a park, or a building.
To create a dataset of OSM roads (or footpaths, tracks etc.) suitable for routefinding or display in a road map, it is necessary to retrieve only those ways that contain a tag element with a k attribute of “highway”. The corresponding v attribute describes the type of highway. Examples of possible values include:
- cycleway
- footway
- motorway
- path
- pedestrian
- primary
- residential
- road
- secondary
- service
- steps
- tertiary
- track
- unclassified
Note that this list isn’t exhaustive – the design of the OSM schema means that editors can tag ways or nodes with any values, but this is a list of some of the commonly-used tags.
There are many reasons why you might want to categorise each of these highway types separately.
- Consider access for different modes of transport, for example; clearly, a car can’t go down a cycleway. Nor can a tractor go on a motorway, or a cycle go down steps (unless you’re planning some sort of stunt bike ride).
- If you’re designing a route-finding application, you might want to consider and compare the relative costs of travelling down different routes. Motorway segments generally have a higher speed limit than primary roads, which in turn have higher average speed than secondary roads, etc. Therefore, a route that maximises the percentage travelled on higher-status roads may well be shorter in time, even if it covers a longer distance.
- When drawing features onto a map, it’s usual to display different categories of highways with different styles (e.g. motorways coloured blue, primary roads thicker than secondary, tracks as dotted/dashed lines).
As a simple example, to style the Spatial Results tab view of my OSM map to show different categories of roads with different thicknesses I created a table to attach a weight to each highway type, as follows:
CREATE TABLE RoadWeights (
highwaytype varchar(32),
roadweight int
);
INSERT INTO RoadWeights VALUES
(‘motorway’, 15),
(‘motorway_Link’, 15),
(‘trunk’, 10),
(‘trunk_Link’, 10),
(‘primary’, 8),
(‘primary_Link’, 8),
(‘secondary’, 5),
(‘tertiary’, 3),
(‘residential’, 1),
(‘unclassified’, 0);
Then, I edited my SELECT query to only display rows from my Ways table that were tagged with one of my chosen highway types, and buffered the geography LineString representing each road by the corresponding weight from the RoadWeights table:
SELECT
w.wayid,
wt.TagValue AS highwaytype,
w.geog4326.STBuffer(ISNULL(roadweight,0))
FROM
ways w
INNER JOIN waytags wt ON w.wayid = wt.wayid AND wt.TagName = ‘Highway’
LEFT JOIN RoadWeights rw ON wt.TagValue = rw.highwaytype
WHERE
wt.TagValue IN (‘motorway’, ‘motorway_Link’, ‘trunk’, ‘trunk_Link’, ‘primary’, ‘primary_Link’, ‘secondary’, ‘tertiary’, ‘residential’, ‘unclassified’)
Even when viewed in the SSMS Spatial Results tab, the map already becomes much cleaner than the result shown at the top of this post – with the Norwich inner ring road and its primary arterial roads now clearly visible (and also, the train tracks coming into the railway station on the south east of the city are no longer shown)
Clearly you wouldn’t normally optimise your dataset just for the purposes of display in the SSMS spatial results tab, but you can apply this same technique to attach any other properties that correspond to the type of road – the average road speed limit, accessibility, or styling options that should be passed to a front-end display, for example.
