Script de conversion DBF > CSV de la BPE 2016 (INSEE)

bpe_dbf2csv.sh

echo "Téléchargement des données INSEE"
wget https://www.insee.fr/fr/statistiques/fichier/2387803/bpe16_ensemble_xy_dbase.zip
unzip bpe16_ensemble_xy_dbase.zip

echo "Téléchargement des données OpenStreetMap"
wget http://osm13.openstreetmap.fr/~cquest/openfla/export/communes-20160614-shp.zip
unzip communes-20160614-shp.zip

echo "Import dans postgresql"
pgdbf bpe_ensemble_xy.dbf -s iso8859-1 | sed 's/,\([0-9][0-9][0-9][0-9]\)/.\1/g' | psql
pgdbf varmod_ensemble_xy.dbf -s CP850  | psql

ogr2ogr -f postgresql PG:dbname=cquest communes-20160614.shp -nlt geometry

psql -c "
-- ajout colonne géométrie sur BPE
ALTER TABLE bpe_ensemble_xy ADD geom geometry;
-- géométries sur Métropole (Lambert 93)
UPDATE bpe_ensemble_xy set geom=st_transform(st_setsrid(st_makepoint(lambert_x, lambert_y),2154),4326) where depcom<'97';
-- géométries sur DOM dans les projections locales - https://fr.wikipedia.org/wiki/Syst%C3%A8me_de_coordonn%C3%A9es_(cartographie)#Les_codes_EPSG
UPDATE bpe_ensemble_xy set geom=st_transform(st_setsrid(st_makepoint(lambert_x, lambert_y),4558),4326) where dep='971';
UPDATE bpe_ensemble_xy set geom=st_transform(st_setsrid(st_makepoint(lambert_x, lambert_y),4558),4326) where dep='972';
UPDATE bpe_ensemble_xy set geom=st_transform(st_setsrid(st_makepoint(lambert_x, lambert_y),4624),4326) where dep='973';
UPDATE bpe_ensemble_xy set geom=st_transform(st_setsrid(st_makepoint(lambert_x, lambert_y),4627),4326) where dep='974';
UPDATE bpe_ensemble_xy set geom=st_transform(st_setsrid(st_makepoint(lambert_x, lambert_y),4470),4326) where dep='976';
"

echo "Export CSV"
psql -c "\copy (select b.reg, b.dep, b.depcom, b.dciris, b.an, b.typequ, lambert_x,lambert_y,qualite_xy,
  st_x(geom) as longitude, st_y(geom) as latitude, case when c.insee=depcom then 'ok' else '' end as qualite_geo,
  modlibelle as libequ
  FROM bpe_ensemble_xy b
  LEFT JOIN communes_20160614 c on (st_intersects(geom, wkb_geometry))
  JOIN varmod_ensemble_xy l on (variable='TYPEQU' and modalite=typequ))
to bpe2016.csv with (format csv, header true)"