Plotting speed limits in Copenhagen using public data, Power Query and Power Map

My hometown Copenhagen is sharing a lot of public data via – http://data.kk.dk/ – and one of the latest is a dataset with speed limits for the different roads in Copenhagen – and the dataset is shared as a geojson resource – http://data.kk.dk/dataset/trafikhastigheder

So why not try to see how the Power BI tools in Excel 2013 could visualize this information.

First – Power Query

 

Copy the geojson url – http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:parkeringstaelling_i_zoner&outputFormat=json&SRSNAME=EPSG:4326

And activate Power Query and choose to import from Web

Power Query will then show you the information about the json document

And then navigate to the list – features

 

Then convert the list to a table and expand the different elements in the document

 

In order to get the Latitude and Longitude we need to extract the values from the geometry.coordinates field – by adding two new calculated columns – it can be done like this

Then I do some renaming of columns and remove unwanted columns I end up with a relative simple dataset

 

The full query is listed here

let

Source = Json.Document(Web.Contents(“http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:trafikhastigheder&outputFormat=json&SRSNAME=EPSG:4326”)),

features = Source[features],

#”Table from List” = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Expand Column1″ = Table.ExpandRecordColumn(#”Table from List”, “Column1”, {“type”, “id”, “geometry”, “geometry_name”, “properties”}, {“Column1.type”, “Column1.id”, “Column1.geometry”, “Column1.geometry_name”, “Column1.properties”}),

#”Expand Column1.geometry” = Table.ExpandRecordColumn(#”Expand Column1″, “Column1.geometry”, {“type”, “coordinates”}, {“Column1.geometry.type”, “Column1.geometry.coordinates”}),

#”Expand Column1.geometry.coordinates” = Table.ExpandListColumn(#”Expand Column1.geometry”, “Column1.geometry.coordinates”),

#”Expand Column1.properties” = Table.ExpandRecordColumn(#”Expand Column1.geometry.coordinates”, “Column1.properties”, {“id”, “bydel”, “vejid”, “vejnavn”, “frastation”, “tilstation”, “hastighedsgraense”, “anb_hastighedsgraense”}, {“Column1.properties.id”, “Column1.properties.bydel”, “Column1.properties.vejid”, “Column1.properties.vejnavn”, “Column1.properties.frastation”, “Column1.properties.tilstation”, “Column1.properties.hastighedsgraense”, “Column1.properties.anb_hastighedsgraense”}),

#”Added Custom” = Table.AddColumn(#”Expand Column1.properties”, “Custom”, each [Column1.geometry.coordinates]{1}),

#”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Custom.1”, each [Column1.geometry.coordinates]{0}),

#”Renamed Columns” = Table.RenameColumns(#”Added Custom1″,{{“Custom”, “Latitude”}, {“Custom.1”, “Longitude”}}),

#”Removed Columns” = Table.RemoveColumns(#”Renamed Columns”,{“Column1.properties.anb_hastighedsgraense”, “Column1.type”, “Column1.id”, “Column1.geometry.type”, “Column1.geometry.coordinates”, “Column1.geometry_name”, “Column1.properties.id”, “Column1.properties.vejid”, “Column1.properties.frastation”, “Column1.properties.tilstation”}),

#”Changed Type” = Table.TransformColumnTypes(#”Removed Columns”,{{“Column1.properties.hastighedsgraense”, Int64.Type}, {“Latitude”, type number}, {“Longitude”, type number}}),

#”Renamed Columns1″ = Table.RenameColumns(#”Changed Type”,{{“Column1.properties.hastighedsgraense”, “SpeedLimit”}, {“Column1.properties.vejnavn”, “Roadname”}, {“Column1.properties.bydel”, “CityArea”}})

in

#”Renamed Columns1″

 

I return the data to the datamodel and we are ready to activate Power Map.

 

And by using the filter function I can find the roads where the limit is 60 km/h or higher

 

You can download a copy of the file – here