Netherlands Open Map Data
Netherlands administrative boundaries and road network
The data is organised across two themes.
Administrative: This data set contains the polygons for Netherlands administrative boundaries at different levels. It includes the name of the administrative area, the quadkey, and the geo coordinates.
Road: This data set covers the road network of the Netherlands, e.g. motorways, residential streets, cycle ways, national roads etc. It includes the length of road, its usage type, the speed limit and a lot more.
The Road theme is organised along the following classes and subclasses
Classes
- cycleway
- local
- motorway
- parking
- pedestrian
- primary
- recreation
- secondary
- service
- tertiary
Subclasses
- access_aisle
- alley
- bicycle
- bridleway
- crossing
- driveway
- footway
- living_street
- motorway
- motorway_link
- parking
- parking_aisle
- path
- pedestrian_mall
- primary
- primary_link
- raceway
- residential
- road
- secondary
- secondary_link
- service
- sidewalk
- square
- steps
- tertiary
- tertiary_link
- track
- trunk
- trunk_link
- unclassified
Tables
V_ADMINISTRATIVE
V_ROAD
Administrative Table
Below is a list of the the most important columns in the Administrative table
| Column | Description |
|---|---|
| ID | Unique identifier of the form (n/w/r) + osm id + @ (osm version). Example, version 22 of node id=1 : n1@22 |
| CLASS | Class of the feature |
| SUBCLASS | Subclass of the feature |
| QUADKEY | The zoom level 15 quadkey that contains the centroid of the feature. |
| NAMES | JSON-formatted key/value pairs containing the place name in different languages. Keys include local (the common name used in the place) and ISO language codes like en, en-US, and de. |
| ORIGINAL_SOURCE_TAGS | This specifies the primary OSM id and its properties. JSON-formatted key/value pairs of original OSM tags |
| GEO_CORDINATES | Either of the two lines of latitude and longitude whose intersection determines the geographical point of a place |
| DISPUTE_SOURCE | Actual source of the disputed boundary. |
| ADMIN_LEVEL | admin level of the boundary (if a capital city) |
| BORDER_TYPE | Is a key which has frequently been filled by automated data imports, and has also been approved to specify the type of maritime boundary. |
| DISPUTED_NAME | This specifies the disputed boundary names. |
| WIKIDATA | Wikidata ID (if present) |
| DESIGNATION | Designation is used to record the legal classification of an object such as unclassified_county_road, permissive_footpath etc. |
| CLAIMED_BY | This specify the parties who claim the boundary |
| BOUNDARY | This shows the type of the administrative boundary |
| CONTROLLED_BY | This specify the party that actually controls the boundary |
| DISPUTED_BY | This specify the parties who dispute the boundary |
| RECOGNIZED_BY | This specifies the administrative boundary is recognised by which country. |
Road Table
Below is a list of the most important columns in the Road table and a description
| COLUMNS | DESCRIPTION |
|---|---|
| ID | Unique identifier of the form (n/w/r) + osm id + @ (osm version). Example, version 22 of node id=1 : n1@22 |
| CLASS | Class of the feature e.g. motorway, pedestrian, parking etc. It is the superset of the subclass. |
| SUBCLASS | Subclass of the feature. Such as motorway_link belongs to motorway class. |
| QUADKEY | The zoom level 15 quadkey that contains the centroid of the feature. |
| NAMES | JSON-formatted key/value pairs containing the place name in different languages. Such as Prince Of Wales Drive in English, promenade Prince Of Wales in French. |
| GEO_CORDINATES | Either of the two lines of latitude and longitude whose intersection determines the geographical point of a place |
| MAXSPEED | Value of maxspeed |
| SURFACE | Value of Surface such as unpaved, asphalt, paved etc. |
| IS_TUNNEL | True if tunnel in (building_passage, covered, yes) |
| LANES | Value of lanes |
| LEVEL | Value of level |
| IS_COVERED | True if covered=yes |
| IS_ONEWAY | True if oneway in (yes, 1) and false if oneway=no |
| LENGTH_M | Length of feature in meters (if a line) |
| IS_PEDESTRIAN | True if (foot is not NULL and foot != no) or (bicycle is not NULL and bicycle != no) |
| IS_BRIDGE | True if bridge in (aqueduct, boardwalk, cantilever, covered, low_water_crossing, movable, trestle, viaduct, yes) |
| SURFACE_AREA_SQ_M | Area of the feature in square metres (if a polygon) |
Use cases and sample Queries
Use cases
Look up geo coordinates in the Netherlands against Netherlands administrative boundaries. Roll up and drill down into different levels of administrative boundaries.
Use the polygons of the administrative boundaries in the Netherlands to drill down and roll up on geo-coordinates.
Analyse the road network in the Netherlands by administrative boundaries
Sample queries
Compare the types of building and square metres between the Amsterdam and Rotterdam
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
WITH BUILDING AS( SELECT A.NAMES['local']::VARCHAR AS NAME ,B.Class ,B.subclass ,count(9) num_buidling ,SUM(B.surface_area_sq_m) sum_surface_area_sq_m FROM "NETHERLANDS"."V_ADMINISTRATIVE" A INNER JOIN(SELECT Class ,subclass ,surface_area_sq_m ,GEO_CORDINATES FROM "NETHERLANDS"."V_BUILDING" WHERE SUBSTR(QUADKEY ,0 ,8) IN ('12020210', '12020211')) B ON ST_COVERS(A.GEO_CORDINATES ,B.GEO_CORDINATES) WHERE A.id IN('r324431@56' ,'r47811@69') GROUP BY A.NAMES['local'] ,B.Class ,B.subclass ORDER BY B.class ,B.subclass) SELECT CLASS, SUBCLASS, MAX(CASE WHEN NAME='Amsterdam' THEN sum_surface_area_sq_m END) AS SUM_SURFACE_AREA_SQ_M_AMSTERDAM, MAX(CASE WHEN NAME='Rotterdam' THEN sum_surface_area_sq_m END) AS SUM_SURFACE_AREA_SQ_M_ROTTERDAM, MAX(CASE WHEN NAME='Amsterdam' THEN num_buidling END) AS NUM_OF_BUILDING_AMSTERDAM, MAX(CASE WHEN NAME='Rotterdam' THEN num_buidling END) AS NUM_OF_BUILDING_ROTTERDAM FROM BUILDING GROUP BY CLASS,SUBCLASS ORDER BY CLASS,SUBCLASS; |
Compare length of road in meters between Amsterdam and Rotterdam
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
WITH ROAD AS( SELECT A.NAMES['local']::VARCHAR AS NAME ,R.Class ,R.subclass ,SUM(R.length_m) AS SUM_OF_LENGTH FROM "NETHERLANDS"."V_ADMINISTRATIVE" A INNER JOIN(SELECT Class ,subclass ,length_m ,GEO_CORDINATES FROM "NETHERLANDS"."V_ROAD" WHERE SUBSTR(QUADKEY ,0 ,8) IN ('12020210', '12020211')) R ON ST_COVERS(A.GEO_CORDINATES ,R.GEO_CORDINATES) WHERE A.id IN('r324431@56' ,'r47811@69') GROUP BY A.NAMES['local'] ,R.Class ,R.subclass ORDER BY R.class ,R.subclass) SELECT CLASS, SUBCLASS, MAX(CASE WHEN NAME='Amsterdam' THEN SUM_OF_LENGTH END) AS AMSTERDAM, MAX(CASE WHEN NAME='Rotterdam' THEN SUM_OF_LENGTH END) AS ROTTERDAM FROM ROAD GROUP BY CLASS,SUBCLASS ORDER BY CLASS,SUBCLASS; |
