Last active
November 5, 2023 15:15
-
-
Save Gro-Tsen/748d35d92ad8ae925741ea99fee8b201 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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);' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment