2022-01-20 10:48:13 +00:00
DB = ./afdelingen.sqlite3
2021-12-20 23:12:10 +00:00
install :
pip install xlsx2csv
2021-12-26 23:39:32 +00:00
drop_all :
2022-01-20 11:13:22 +00:00
rm -f ${ DB }
2021-12-26 23:39:32 +00:00
2021-12-26 20:11:25 +00:00
download_municipalities :
2021-12-26 23:39:32 +00:00
wget -O ./raw/gemeenten.xlsx https://www.cbs.nl/-/media/_excel/2020/47/gemeenten-alfabetisch-2021.xlsx
2021-12-26 20:11:25 +00:00
2021-12-26 23:39:32 +00:00
download_map_continental :
wget -O ./raw/gemeenten.svg https://upload.wikimedia.org/wikipedia/commons/3/30/Nederland_gemeenten_2021.svg
download_map_caribbean :
wget -O ./raw/caribbean.svg https://upload.wikimedia.org/wikipedia/commons/a/ab/Dutch_Caribbean_location_map.svg
2021-12-26 20:11:25 +00:00
# https://www.cbs.nl/nl-nl/maatwerk/2020/39/buurt-wijk-en-gemeente-2020-voor-postcode-huisnummer
download_pc7 :
2021-12-26 23:39:32 +00:00
wget -O ./raw/pc7.zip https://www.cbs.nl/-/media/_excel/2020/39/2020-cbs-pc6huisnr20200801-buurt.zip
2021-12-26 20:11:25 +00:00
download_regex_plugin :
2021-12-20 23:12:10 +00:00
wget -O re.so https://github.com/nalgeon/sqlean/releases/download/0.15.1/re.so
2021-12-26 23:39:32 +00:00
download_all : download_municipalities download_map_continental download_map_caribbean download_pc 7 download_regex_plugin
2021-12-20 23:12:10 +00:00
2021-12-26 23:39:32 +00:00
convert_gemeenten :
xlsx2csv ./raw/gemeenten.xlsx > ./raw/gemeenten.csv
convert_pc7 :
unzip ./raw/pc7.zip -d ./raw/pc7; \
rm ./raw/pc7.zip; \
./scripts/pc7.sh
convert_airtable :
dos2unix ./source/leden-Alles.csv
convert_all : convert_gemeenten convert_pc 7 convert_airtable
2021-12-24 22:33:07 +00:00
2021-12-25 11:34:55 +00:00
import_afdelingen :
2022-01-20 11:19:27 +00:00
sqlite3 ${ DB } 'DROP TABLE IF EXISTS afdelingen;' ; \
2022-01-20 10:48:13 +00:00
sqlite3 ${ DB } -separator ";" '.import ./source/afdelingen.csv afdelingen'
2021-12-25 11:34:55 +00:00
import_gemeenten_2020 :
2022-01-20 11:19:27 +00:00
sqlite3 ${ DB } 'DROP TABLE IF EXISTS gemeenten_2020;' ; \
2022-01-20 10:48:13 +00:00
sqlite3 ${ DB } -separator ";" '.import ./out/gemeenten2020_2021.csv gemeenten_2020'
2021-12-25 11:34:55 +00:00
import_gemeenten :
2022-01-20 11:19:27 +00:00
sqlite3 ${ DB } 'DROP TABLE IF EXISTS gemeenten;' ; \
2022-01-20 10:48:13 +00:00
sqlite3 ${ DB } -separator "," '.import ./raw/gemeenten.csv gemeenten'
2021-12-25 11:34:55 +00:00
import_pc6_huisnr :
2022-01-20 11:19:27 +00:00
sqlite3 ${ DB } 'DROP TABLE IF EXISTS pc6_huisnr;' ; \
2022-01-20 10:48:13 +00:00
sqlite3 ${ DB } -separator ";" '.import ./raw/pc7/pc6hnr20200801_gwb.csv pc6_huisnr'
2021-12-25 11:34:55 +00:00
import_airtable_leden :
2022-01-20 11:19:27 +00:00
sqlite3 ${ DB } 'DROP TABLE IF EXISTS airtable_leden;' ; \
2022-01-20 10:48:13 +00:00
sqlite3 ${ DB } -separator "," '.import ./source/leden-Alles.csv airtable_leden'
2021-12-20 23:12:10 +00:00
2021-12-25 00:27:02 +00:00
import_bij1_gebieden :
2022-01-20 11:19:27 +00:00
sqlite3 ${ DB } 'DROP TABLE IF EXISTS bij1_gebieden;' ; \
2022-01-20 10:48:13 +00:00
sqlite3 ${ DB } -separator "," '.import ./source/bij1-gebieden.csv bij1_gebieden'
2021-12-25 00:27:02 +00:00
2021-12-25 11:34:55 +00:00
import_all : import_afdelingen import_gemeenten_ 2020 import_gemeenten import_pc 6_huisnr import_airtable_leden import_bij 1_gebieden
2021-12-20 23:12:10 +00:00
# join source with our annotations
join_gemeenten :
2023-01-05 16:44:35 +00:00
sqlite3 ${ DB } 'DROP VIEW IF EXISTS afdelingsgebieden; CREATE VIEW afdelingsgebieden AS SELECT Gemeentecode, gemeenten.GemeentecodeGM, gemeenten.Gemeentenaam, Provinciecode, ProvinciecodePV, Provincienaam, is_kern_gemeente, afdeling, benodigde_ondersteuningsverklaringen, verzamelde_ondersteuningsverklaringen FROM gemeenten LEFT JOIN bij1_gebieden ON gemeenten.GemeentecodeGM = bij1_gebieden.GemeentecodeGM;'
2021-12-20 23:12:10 +00:00
join_pc7 :
2022-01-20 10:48:13 +00:00
sqlite3 ${ DB } 'DROP VIEW IF EXISTS pc7; CREATE VIEW pc7 AS SELECT PC6, Huisnummer, Gemeentenaam2020 FROM pc6_huisnr LEFT JOIN gemeenten_2020 ON pc6_huisnr.Gemeente2020 = gemeenten_2020.Gemcode2020;'
2021-12-20 23:12:10 +00:00
join_pc7_bij1 :
2022-01-20 10:48:13 +00:00
sqlite3 ${ DB } 'DROP VIEW IF EXISTS pc7_bij1; CREATE VIEW pc7_bij1 AS SELECT * FROM pc7 LEFT JOIN afdelingsgebieden ON pc7.Gemeentenaam2020 = afdelingsgebieden.Gemeentenaam;'
2021-12-24 16:40:22 +00:00
2021-12-26 23:39:32 +00:00
# CREATE VIEW leden_pc7 AS
join_airtable_pc7 :
2022-01-20 10:48:13 +00:00
sqlite3 ${ DB } " SELECT load_extension(' ${ PWD } /re') " "DROP VIEW IF EXISTS leden_pc7; SELECT *, REPLACE(UPPER(airtable_leden.Postcode), ' ', '') AS PC6, regexp_replace(airtable_leden.Straat, '[^0-9]+', '') AS Huisnummer FROM airtable_leden;"
2021-12-24 16:40:22 +00:00
2021-12-20 23:12:10 +00:00
join_airtable :
2022-01-20 10:48:13 +00:00
sqlite3 ${ DB } "DROP VIEW IF EXISTS leden_afdeling; CREATE VIEW leden_afdeling AS SELECT * FROM leden_pc7 LEFT JOIN pc7_bij1 ON airtable_leden.PC6 = pc7_bij1.PC6 AND airtable_leden.Huisnummer = pc7_bij1.Huisnummer;"
2021-12-24 16:40:22 +00:00
2021-12-26 23:39:32 +00:00
join_all : join_gemeenten join_pc 7 join_pc 7_bij 1 join_airtable_pc 7 join_airtable
dump_afdelingsgebieden :
2022-01-20 10:48:13 +00:00
sqlite3 -header -csv ${ DB } "select * from afdelingsgebieden;" > ./out/afdelingsgebieden.csv
2021-12-26 23:39:32 +00:00
dump_leden :
2022-01-20 10:48:13 +00:00
sqlite3 -header -csv ${ DB } "select * from leden_afdeling;" > ./out/leden_afdeling.csv
2021-12-26 23:39:32 +00:00
dump_all : dump_afdelingsgebieden dump_leden
2021-12-24 19:57:21 +00:00
svg_tooltips :
2021-12-26 23:39:32 +00:00
cp ./raw/gemeenten.svg ./generated/gemeenten-tooltipped.svg; \
./scripts/svg-tooltip.sh
2021-12-24 22:33:07 +00:00
2021-12-25 00:27:02 +00:00
svg_afdelingen :
2021-12-26 23:39:32 +00:00
cp ./generated/gemeenten-tooltipped.svg ./generated/gemeenten-afdelingen.svg; \
./scripts/svg-afdelingen.sh
2021-12-25 00:27:02 +00:00
2021-12-26 23:39:32 +00:00
svg_ondersteuningsverklaringen :
cp ./generated/gemeenten-tooltipped.svg ./generated/gemeenten-ondersteuningsverklaringen.svg; \
./scripts/svg-ondersteuningsverklaringen.sh
2021-12-26 20:06:02 +00:00
2022-01-20 10:52:19 +00:00
svg_all : svg_tooltips svg_afdelingen svg_ondersteuningsverklaringen
2021-12-26 20:11:25 +00:00
2021-12-26 23:39:32 +00:00
run_all : install drop_all download_all convert_all import_all join_all dump_all svg_all
2022-01-20 11:04:21 +00:00
bij1_to_afdelingen_svg : import_gemeenten import_bij 1_gebieden join_gemeenten dump_afdelingsgebieden svg_afdelingen
2022-01-06 15:24:39 +00:00
2022-01-20 11:04:21 +00:00
bij1_to_ondersteuningsverklaringen_svg : import_gemeenten import_bij 1_gebieden join_gemeenten dump_afdelingsgebieden svg_ondersteuningsverklaringen