README.md

Flowmap demo Relocations

This repository lets you test flowmap.blue with relocation data from Statistics Netherlands. Read this manual for all the steps that are needed to achieve this result by @rug_geo. You can also find the necessary sql in flowmap.sql

Step 1: get the data

1. Go to Statistics Netherlands and choose Downloads -> Onbewerkte dataset (raw dataset).
2. Choose "Regio van Vestigingen" (destinations) -> "Alle Gemeenten" (all municipalities).
3. Choose "Regio van Vertrek" (departures) -> "Alle Gemeenten" (all municipalities).
4. Choose "Periodes" -> 2017
5. Download csv

Step 2: prepare google sheet

Follow the steps from the flowmap.blue manual.

Step 3: prepare database

In this case we used PostgreSQL with Postgis. This migt work with sqlite or mysql too, apart from the coordinates transformation.

1. Create relocation data table:

create table relocations(
	id text,
	from text,
	to text,
	period text,
	count text
	)
;

2. Import the downloaded csv Use your preferred way of imporing data, in my case psql:

\copy relocations from Downloads/relocations.csv csv header delimiter ';';

3. If you don't have the Dutch municipalities in your database, download them from Statistics Netherlands and import into your database. E.g. via Qgis DB Manager or ogr2ogr

Step 4: export data

1. Export a list of municipalities and their centroids in lat/long (for labeling).

select 
	substring(gm_code,3) as id,
	gm_naam as municipality_name, 
	st_y(st_transform(st_centroid(geom),4326)) y,
	st_x(st_transform(st_centroid(geom),4326)) x 
from 
	cbs_gemeente_2017
;

Copy the result into the locations tab in your google sheet

2. Export the flows between municipalities

The municipal code is trimmed to end up with a numerical identifier. The count is trimmed to get rid of any spaces.

select 
	substring(from,3)::text from, 
	substring(to,3)::text to, 
	count::text
from 
	relocations 
where 
	from is not null 
	and 
	to is not null 
	and 
	count is not null 
	and 
	trim(count) != '0'
;

Copy the result into the flows tab in your google sheet and check out your map!