integration_donnees:externes:diren

Intégration des données du serveur cartographique de la DIREN L-R à notre base de données

mai 2009

Données géographiques du serveur cartographique de la DIREN L-R (NATURA 2000 ZNIEFF, APB, PNR, Rn…)

la liste des fichiers concernés est la suivantes :

apb_lr.shp
parc_national_cevennes_zone_centrale.shp
parc_national_cevennes_zone_peripherique.shp
pnr_languedoc_roussillon.shp
psic_lr.shp
ramsar_lr.shp
reserves_biologiques_lr.shp
rnv_lr.shp
rn_lr.shp
sage_lr2.shp
scot_lr.shp
sic_lr.shp
sites_lr_sc_2.shp
sites_lr_si_2.shp
sites_lr_zp.shp
zico_lr.shp
znieff_lr_typ1.shp
zps_lr.shp
zps_lr_en_mer.shp
zsc_lr.shp

Télécharger les données sur le serveur carto. Dézipper l'archive et se rendre à l'intérieur (commande cd)

Transformation des shapes en sql dans un sous dossier appellé “sql” :

for /r %F IN (*.shp) DO shp2pgsql -s 27572 -g geometrie "%F" "integration_%~nF">"./sql/integration_%~nF.sql"

Chargement dans la base de données :

for /R %F IN (*.sql) DO psql -f "%F" -h 192.168.1.40 -U nom_utilisateur nom_base

Ventilation des données et suppression des tables temporaires :

Les tables créées sont toutes ou presque de structure différente. Le script d'insertion des données de ces différentes tables rtemporaires à notre table perimetre ne peut donc se faire de façcon générique. En tout cas pas autant qu'on le siouhaiterait.

BEGIN;
DELETE FROM perimetre WHERE type_perimetre='apb';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie)
SELECT id, 'apb', toponyme_d, id, NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie)))
FROM integration_apb_lr
GROUP BY id, toponyme_d, id;
SELECT dropgeometrytable('integration_apb_lr');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='pnc_zc';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie)
SELECT 'pnc_zc_'||gid::text, 'pnc_zc', nom_parc, 'pnc_zc_'||gid::text, NULL, '2009-05-27', geometrie
  FROM integration_parc_national_cevennes_zone_centrale;
SELECT dropgeometrytable('integration_parc_national_cevennes_zone_centrale');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='pnc_zp';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie)
SELECT 'pnc_zp_'||gid::text, 'pnc_zp', nom_parc, 'pnc_zp_'||gid::text, NULL, '2009-05-27', geometrie
  FROM integration_parc_national_cevennes_zone_peripherique;
SELECT dropgeometrytable('integration_parc_national_cevennes_zone_peripherique');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='pnr';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT code_pnr, 'pnr', nom_pnr, code_pnr, NULL, '2009-05-27', geometrie, 'date création : '||date_creat
  FROM integration_pnr_languedoc_roussillon;
SELECT dropgeometrytable('integration_pnr_languedoc_roussillon');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='psic';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT code, 'psic', nom_du_sit, code, NULL, '2009-05-27', geometrie, NULL FROM integration_psic_lr;
SELECT dropgeometrytable('integration_psic_lr');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='sic';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT code, 'sic', nom_du_sit, code, NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie))), NULL FROM integration_sic_lr
GROUP BY code, nom_du_sit;
SELECT dropgeometrytable('integration_sic_lr');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='zps';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT code_du_si, 'zps', nom_du_sit, code_du_si, NULL, '2009-05-27'::date, multi(st_union(cleangeometry(geometrie))), NULL FROM integration_zps_lr
GROUP BY code_du_si, nom_du_sit
UNION
SELECT code_site, 'zps', nom, code_site, NULL, '2009-05-27'::date, multi(st_union(cleangeometry(geometrie))), NULL FROM integration_zps_lr_en_mer
GROUP BY code_site, nom;
SELECT dropgeometrytable('integration_zps_lr');
SELECT dropgeometrytable('integration_zps_lr_en_mer');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='zsc';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT code, 'zsc', nom_du_sit, code, NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie))), NULL FROM integration_zsc_lr
GROUP BY code, nom_du_sit;
SELECT dropgeometrytable('integration_zsc_lr');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='ramsar';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT id_diren, 'ramsar', libelle, id_diren, NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie))), NULL FROM integration_ramsar_lr
GROUP BY id_diren, libelle;
SELECT dropgeometrytable('integration_ramsar_lr');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='rnn';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT id, 'rnn', intitule_d, id, NULL, '2009-05-27', geometrie, 'source digit : '||source_dig
  FROM integration_rn_lr;
SELECT dropgeometrytable('integration_rn_lr');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='rnr';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT id, 'rnr', intitule_d, id, NULL, '2009-05-27', geometrie, 'source digit : '||source_dig
  FROM integration_rn_lr;
SELECT dropgeometrytable('integration_rnv_lr');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE lower(type_perimetre) IN (SELECT lower(ccod_trb) FROM integration_reserves_biologiques_lr);
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT lower(ccod_trb)||'_'||lpad(ccod_rb::text,4,'0'::text), lower(ccod_trb), llib_rb, lower(ccod_trb)||'_'||lpad(ccod_rb::text,4,'0'), NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie))), 'ymaj_rb : '||ymaj_rb
  FROM integration_reserves_biologiques_lr
  GROUP BY lower(ccod_trb)||'_'||lpad(ccod_rb::text,4,'0'::text), lower(ccod_trb), llib_rb,'ymaj_rb : '||ymaj_rb;
SELECT dropgeometrytable('integration_reserves_biologiques_lr');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE lower(type_perimetre) IN (SELECT lower(type) FROM integration_sites_lr_sc_2);
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT type||'_'||cd_sig, lower(type), max(toponyme_d), cd_sig, NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie))), max('date de création : '||date_creat||'. Nature protection : '||nature_pro)
  FROM integration_sites_lr_sc_2
  GROUP BY type, cd_sig, lower(type), date_creat
  ORDER BY cd_sig;
SELECT dropgeometrytable('integration_sites_lr_sc_2');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE lower(type_perimetre) IN (SELECT lower(type) FROM integration_sites_lr_si_2);
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT type||'_'||cd_sig, lower(type), max(toponyme_d), cd_sig, NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie))), max('date de création : '||date_creat||'. Nature protection : '||nature_pro)
  FROM integration_sites_lr_si_2
  GROUP BY type, cd_sig, lower(type), date_creat
  ORDER BY cd_sig;
SELECT dropgeometrytable('integration_sites_lr_si_2');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE lower(type_perimetre) IN (SELECT lower(type) FROM integration_sites_lr_zp);
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT type||'_'||cd_sig, lower(type), max(toponyme_d), cd_sig, NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie))), max('Nature protection : '||nature_pro)
  FROM integration_sites_lr_zp
  GROUP BY type, cd_sig, lower(type)
  ORDER BY cd_sig;
SELECT dropgeometrytable('integration_sites_lr_zp');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE lower(type_perimetre)='zico';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT 'ZICO_'||id, 'zico', nom, id, NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie))), NULL
  FROM integration_zico_lr
  GROUP BY id, nom;
SELECT dropgeometrytable('integration_zico_lr');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE lower(type_perimetre)='znieff_g1_t1';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT id, 'znieff_g1_t1', toponyme, id, NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie))), NULL
  FROM integration_znieff_lr_typ1
  GROUP BY id, toponyme;
SELECT dropgeometrytable('integration_znieff_lr_typ1');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE lower(type_perimetre)='znieff_g1_t2';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie, remarque)
SELECT id, 'znieff_g1_t1', toponyme, id, NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie))), NULL
  FROM integration_znieff_lr_typ2
  GROUP BY id, toponyme;
SELECT dropgeometrytable('integration_znieff_lr_typ2');
COMMIT;
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='sage';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie)
SELECT 'sage_'||lpad(gid::text,2,'0'::text), 'sage', nom, 'sage_'||lpad(gid::text,2,'0'::text), NULL, '2009-05-27', multi(st_union(cleangeometry(geometrie)))
  FROM integration_sage_lr
GROUP BY 'sage_'||lpad(gid::text,2,'0'::text), nom;
SELECT dropgeometrytable('integration_sage_lr');
COMMIT;
 
 
BEGIN;
DELETE FROM perimetre WHERE type_perimetre='scot';
INSERT INTO perimetre(id_perimetre, type_perimetre, intitule, code_perimetre, echelle_digitalisation, date_validite, geometrie,remarque)
SELECT 'scot_'||lpad(gid::text, 2, '0'::text), 'scot', nom_du_sco, 'scot_'||lpad(gid::text, 2, '0'::text), NULL, '2009-05-27', geometrie, 'état d''avancement : '||etat_avanc
  FROM integration_scot_lr;
SELECT dropgeometrytable('integration_scot_lr');
COMMIT;
 
VACUUM perimetre;

- Pistes de travail

Actualiser ces données à partir des flux WFS émis par le serveur cartographique de la DIREN, en utilisant par exemple SpatialDataIntegrator.