Geospatial Technologies Master's Thesis

Advances of my master thesis for the Erasmus Mundus Masters in Geospatial Technologies.

The final document is here. The final presentation is here

Topic

Validating a bike network analysis score based on open data as a connectivity measure of urban cycling infrastructure adapted for European cities.

• Basic Idea
• Products
• BNA score for UK and NL
  • How to use it?
  • Data
  • Example cities
• Tasks

Basic idea

Reproduce the BNA score developed by People for Bikes, validate it and adapt it to compute bike network connectivity in European cities.

My thesis proposal can be accessed here. Keep in mind, it will be constantly updated.

Products

1. BNA score translated into R and SQL, to be applied in European cities.

2. A series of scripts that come together into an R-Markdown report to calculate the BNA score for cities in the UK (England and Wales) and the Netherlands.

Calculate BNA for cities in the UK (England and Wales) and the Netherlands.

How to use it?

0. Install PostgreSQL here. For version 14 the installation process also includes pgadmin4, and let's you add the PostGIS extension on the Stack Builder application. Make sure osm2pgsql and osm2pgrouting are also installed.
1. Create a spatial database with pgadmin4. To test connection an empty table on the database called test should be created on the public schema
   • Tip: Run FULL VACUUM/ANALYZE frequently on the database to improve performance
   • Follow the tips on this presentation to increase performance of the database
2. Set path variables for osm2pgsql and osm2pgrouting
3. Create a password file on %APPDATA%/postgresql/pgpass.conf with the format hostname:port:database:username:password
4. Clone this repository
5. Edit the R-Markdown report with your study area, and run the R-Notebook locally
6. Wait for a while, keep in mind larger and more crowded areas take longer
7. Check out your results as an HTML file

Data

The BNA score bases its methodology on OSM data, however job and population data is accessed from other open data sources. Particularly for this project, this datasets were used:

• Europe:

  • Population grid per squared kilometer for 2011 from GEOSTAT - EUROSTAT
  • No data available for the whole continent with high spatial resolution

• UK (England and Wales)

  • Population estimates for mid-2017 at LSOA and MSOA level from the Office of National Statistics - ONS
  • Flow commute data (travel to work) for the 2011 Census at LSOA and MSOA level from the Office of National Statistics - ONS, where the total number of trips per destination super-output area where aggregated, and assumed as the total number of jobs in the area.

• The Netherlands

  • District and neighborhood map with associated key figures like population for 2017 from the Centraal Bureau voor de Statistiek - CBS
  • Jobs per municipality from LISA (National Information System for Workplaces) dataset. To estimate jobs at neighborhood level a spatial interpolation was made with the number of jobs in the whole municipality hafly weighted by the fraction of the neighborhood area, and hafly weighted by the fraction of companies in the neighborhood, taken from the CBS data. This approach needs to be validated, however it is one way to approximate it, given that LISA data at this aggregation level has to be bought and is not open data

Example cities

Tasks

• [X] Translate BNA score developed by People for Bikes scripts into R and SQL architecture.
• [X] Include data for population and jobs for European cities (Population data available for Europe, UK and NL, job data available for UK)
• [ ] (Optional) Include data sources to compute the score in the US
• [ ] (Optional) Create a shiny app to show the computed results so far
• [X] Validate the level of traffic stress classification
• [X] Validate the destination's weights
• [X] Find an alternative set of destinations that cover people's needs when they commute by bike to use in European cities