Calculate distance between two groups of geometries with GeoPackage/SQLite

README.md

README

First download and unzip CBS Postcode4 GeoPackage and then open with sqlite3:

wget https://service.pdok.nl/cbs/pc4/atom/v1_0/downloads/cbs_pc4_2020.gpkg.zip
unzip cbs_pc4_2020.gpkg.zip
sqlite3 cbs_pc4_2020.gpkg

Then execute the following queries:

select load_extension('mod_spatialite')
create view locaties as SELECT * FROM cbs_pc4_2020 ORDER BY RANDOM() LIMIT 5; -- selecteer 5 willekeurige postcodes als locaties
create view klanten as SELECT * FROM cbs_pc4_2020, locaties WHERE cbs_pc4_2020.postcode <> locaties.postcode ORDER BY RANDOM() LIMIT 50; -- selecteer 50 willekeurige postcodes (die niet al in locaties view zitten) als klanten
SELECT locaties.postcode as locatie_postcode, klanten.postcode as klant_postcode, ST_Distance(ST_Centroid(GeomFromGPB(locaties.geom)), ST_Centroid(GeomFromGPB(klanten.geom))) as distance  FROM locaties, klanten GROUP BY klant_postcode HAVING MIN(distance) ORDER BY distance;