20231105.admin-express-postgis

sudo apt-get install postgresql-13-postgis-3 postgresql-13-postgis-3-scripts
createdb admin-express
sudo -u postgres psql -d admin-express -c 'CREATE EXTENSION postgis ;'
sudo -u postgres psql -d admin-express -c 'GRANT ALL ON TABLE public.spatial_ref_sys TO david ;'
## Go to <URL: https://geoservices.ign.fr/adminexpress >
## and download ADMIN-EXPRESS_3-2__SHP_LAMB93_FXX_2023-10-16.7z to /data/FTP
cd /data/tmp
7z x /data/FTP/ADMIN-EXPRESS_3-2__SHP_LAMB93_FXX_2023-10-16.7z
cd /data/tmp/ADMIN-EXPRESS_3-2__SHP_LAMB93_FXX_2023-10-16/ADMIN-EXPRESS/1_DONNEES_LIVRAISON_2023-10-16/ADE_3-2_SHP_LAMB93_FXX
ogr2ogr -f postgresql PG:"host=localhost dbname=admin-express" REGION.shp -nlt PROMOTE_TO_MULTI
ogr2ogr -f postgresql PG:"host=localhost dbname=admin-express" DEPARTEMENT.shp -nlt PROMOTE_TO_MULTI
ogr2ogr -f postgresql PG:"host=localhost dbname=admin-express" COLLECTIVITE_TERRITORIALE.shp -nlt PROMOTE_TO_MULTI
ogr2ogr -f postgresql PG:"host=localhost dbname=admin-express" ARRONDISSEMENT.shp -nlt PROMOTE_TO_MULTI
ogr2ogr -f postgresql PG:"host=localhost dbname=admin-express" CANTON.shp -nlt PROMOTE_TO_MULTI
ogr2ogr -f postgresql PG:"host=localhost dbname=admin-express" COMMUNE.shp -nlt PROMOTE_TO_MULTI
# Check that it's basically sensible:
psql admin-express
SELECT id , nom , insee_reg , ST_AsText(ST_Centroid(ST_Transform(wkb_geometry,4326)::geography)) AS centre FROM region ;
SELECT id , nom , insee_com , ST_AsText(ST_Centroid(ST_Transform(wkb_geometry,4326)::geography)) AS centre FROM commune ORDER BY population DESC LIMIT 100 ;
SELECT id , nom , insee_com FROM commune WHERE ST_Contains(wkb_geometry, ST_Transform(ST_GeomFromText('POINT(2.19931 48.71259)',4326),2154)) ;
\q

# Compute centroids of French regions:
psql admin-express -c '\copy (SELECT nom , ST_AsText(ST_Centroid(ST_Transform(wkb_geometry,4326)::geography)) AS centre FROM region ) to stdout csv' | perl -ne 'die unless m/^(.*?),POINT\((\S+)\s+(\S+)\)/; $name=$1; $lon=$2+0; $lat=$3+0; printf("%s: https://www.openstreetmap.org/?mlat=%.6f\&mlon=%.6f#map=6/46.206/1.771\n", $name, $lat, $lon);'

# Compute centroid of France:
psql admin-express
SELECT 'FRANCE' AS nom , ST_Union(wkb_geometry) AS wkb_geometry INTO TEMPORARY TABLE "france" FROM region ;
SELECT nom , ST_AsText(ST_Centroid(ST_Transform(wkb_geometry,4326)::geography)) AS centre FROM france ;
\q

# Compute centroids of Île-de-France departments:
psql admin-express -c '\copy (SELECT nom , ST_AsText(ST_Centroid(ST_Transform(wkb_geometry,4326)::geography)) AS centre FROM departement WHERE insee_reg=11::text ) to stdout csv' | perl -ne 'die unless m/^(.*?),POINT\((\S+)\s+(\S+)\)/; $name=$1; $lon=$2+0; $lat=$3+0; printf("%s: https://www.openstreetmap.org/?mlat=%.6f\&mlon=%.6f#map=9/48.7625/2.5388\n", $name, $lat, $lon);'