Uli Bethke
The data is based on OpenStreetMap and enriched with third party sources. It is organised across eleven themes. Each theme is organised by classes and subclasses. You can drill down from the theme into the classes and from there into subclasses.
- Administrative: This data set contains the polygons for administrative boundaries at different levels. It includes the name of the administrative area, the quadkey, and the geo coordinates.
- Building: Contains the type of building and its surface area.
- Building Detail: Contains additional information on buildings such as building material, construction type, building colour, roof shape and materials used
- Infrastructure: The infrastructure theme contains information on infrastructure such as bridges, towers, communication (cell towers etc), power stations, towers.
- Land: The land theme contains information such as forests, volcanoes, grass, wetland etc.
- Landuse: The landuse theme contains information on the way land is used, e.g. for entertainment, commercial, agriculture etc. purposes.
- Placename: This theme includes named locations from cities, towns, villages etc.
- POI: This theme includes points of interests such as amenities, restaurants, cinemas, schools etc.
- Road: This theme set covers the road network of the UK, 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.
- Transit: This theme covers airports, railways, and buses.
- Water: This theme covers anything related to water such as oceans, rivers, lakes etc.
Views
Each theme is represented by a database View in the data set.
V_ADMINISTRATIVE
V_ROAD
V_POI
V_PLACENAME
V_BUILDING
V_LAND
V_LANDUSE
V_TRANSIT
V_INFRASTRUCTURE
V_WATER
V_BUILDING_DETAIL
We have compiled the data dictionary for three of the themes / Views in the data set
V_Administrative
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 : [email protected] |
| 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. |
V_Road
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 : [email protected] |
| 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) |
V_POI
| Column | Description |
|---|---|
| ID | Unique identifier of the form (n/w/r) + osm id + @ (osm version). Example, version 22 of node id=1 : [email protected] |
| CLASS | Class of the feature |
| SUBCLASS | Subclass of the feature |
| QUADKEY | Zoom level 15 quadkey (Bing Tile) that contains this feature. |
| NAMES | Local name of the point of interest |
| 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 |
| AMENITY | The value of the amenity tag. |
| BUILDING_LOCATION | This provides the building location such as coordinates, GPS location, street address etc. |
| SHOP | Value of shop tag in source |
| HEIGHT_M | Height in meters: value of height in source if present (converted if in feet), or building:levels * 3.42 (a default height for one level) |
| WIKIDATA | Wikidata ID (if present) |
| IS_AREA | True/False if this point is the centroid of an OSM polygonal feature |
| BUILDING_LEVELS | The building:levels tag is used for marking the number of above-ground levels of a building ( building =*) or part of a building ( building:part =*). The underground levels and the roof do not count as levels here. However, levels that are part-way underground do count. |
| IS_BUILDING | True/False/Name of building type |
| TOURISM | Value of tourism tag in source |
| LAYER | Value of layer tag in source |
| MIN_HEIGHT_M | Minimum height in meters: value of min_height in source if present, or building:min_level * 3.42 (a default height for one level) |
| SURFACE_AREA_SQ_M | Area of feature in square metres (if a polygon) |
Use cases and sample Queries
Use cases
Logistics and supply chain management: Road data can be used to analyse transportation routes and optimise delivery schedules.
Urban planning: Road data can be used to analyse the accessibility of different neighbourhoods and identify areas with inadequate transportation infrastructure.
Real estate: Road data can be used to analyse the impact of road proximity and traffic noise on property values.
Market analysis: Administrative boundary data can aid in market analysis by segmenting markets based on geographical regions.
Trade area analysis: The data can be used to define trade areas around existing stores or business locations.
Store location analysis: The data can aid businesses in determining optimal locations for new stores or branch offices
Service coverage analysis: Infrastructure data can be utilised to assess service coverage areas for communication networks.
Real estate market analysis: Building data, particularly square metre footage, is crucial for assessing property values and conducting market analysis.
Location-based marketing: Amenity data can be used for location-based marketing campaigns. By analysing the distribution of amenities, businesses can target their marketing efforts to specific areas based on the presence of relevant amenities.
Retail site selection: Amenity data is valuable for retail site selection.
Property valuation and rental pricing: Amenity data can be used in property valuation and rental pricing analysis.
Sample queries
POI density
We want to analyse which area has the highest density of POIs.
We can use the quadkey in the POI theme to count the number of fast food restaurants per quadkey and then plot them on a map.
For this query we use quadkeys at zoom level 15.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT substr(quadkey ,0 ,5) ,substr(quadkey ,0 ,6) ,COUNT(9) FROM V_POI WHERE subclass='fast_food' GROUP BY substr(quadkey ,0 ,5) ,substr(quadkey ,0 ,6) ORDER BY COUNT(9) DESC; |
Road network analysis
We will analyse the road network in the London boroughs of Camden and Croyden.
The following query will return the geo coordinates for both of those London boroughs
|
1 2 3 4 5 6 7 |
SELECT A.quadkey ,A.geo_cordinates ,A.NAMES['local'] ,A.ID FROM V_ADMINISTRATIVE A WHERE LOWER(A.NAMES['local']) LIKE '%borough of camden%' OR LOWER(A.NAMES['local']) LIKE '%borough of croydon%'; |
In a next step we can lookup the various classes and subclasses of roads from the ROAD table against the geo coordinates of Camden and Croyden.
We want to compare the types of roads that exist in both boroughs and the length in metres of the road network in each.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT A.NAMES['local'] ,B.Class ,B.subclass ,SUM(B.length_m) FROM V_ADMINISTRATIVE A INNER JOIN(SELECT Class ,subclass ,length_m ,GEO_CORDINATES FROM V_ROAD WHERE SUBSTR(QUADKEY ,0 ,8) IN('03131313' ,'12020202')) B ON ST_COVERS(A.GEO_CORDINATES ,B.GEO_CORDINATES) GROUP BY A.NAMES['local'] ,B.Class ,B.subclass ORDER BY B.class ,B.subclass; |
Building analysis
Comparing the types of building and square metres between the London boroughs of Camden and Croyden.
|
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 |
SELECT A.NAMES['local'] ,B.Class ,B.subclass ,COUNT(9) num_buidling ,SUM(B.surface_area_sq_m) sum_surface_area_sq_m FROM V_ADMINISTRATIVE A INNER JOIN(SELECT Class ,subclass ,surface_area_sq_m ,GEO_CORDINATES FROM V_BUILDING WHERE SUBSTR(QUADKEY ,0 ,8) IN('03131313' ,'12020202')) B ON ST_COVERS(A.GEO_CORDINATES ,B.GEO_CORDINATES) GROUP BY A.NAMES['local'] ,B.Class ,B.subclass ORDER BYB.class ,B.subclass ,A.NAMES['local']; |
Infrastructure analysis
Analyse the number of bridges, power stations, communication facilities between the London boroughs of Camden and Croyden
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT A.NAMES['local'] ,B.Class ,B.subclass ,COUNT(9) num_infrastructure FROM V_ADMINISTRATIVE A INNER JOIN(SELECT Class ,subclass ,GEO_CORDINATES FROM V_INFRASTRUCTURE WHERE SUBSTR(QUADKEY ,0 ,8) IN('03131313' ,'12020202')) B ON ST_COVERS(A.GEO_CORDINATES ,B.GEO_CORDINATES) GROUP BY A.NAMES['local'] ,B.Class ,B.subclass ORDER BYB.class ,B.subclass ,A.NAMES['local']; |
