=====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 [[:wiki:http://carto.languedoc-roussillon.ecologie.gouv.fr|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" :__

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

__Chargement dans la base de données :__

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

__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.//

<code sql>
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;

</code>

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