Soumis par Mathieu Bossaert le
Intégrer des données externes à notre propre base de données peut se révéler fastidieux. pour faciliter la chose, nous allons créer une table qui décrit pour chaque table de données à intégrer, la liste des champs à y ajouter pour pouvoir ensuite les intégrer à sicen. Nous décrirons dans cette table comment le champ "sicen" doit être généré à partir des colonnes de la table à intégrer.
CREATE TABLE donnees_externes.insertion_sicen ( gid serial NOT NULL, table_schema text, --le nom du schéma contenant la table à importer table_name text, --le nom de cette table à importer sicen_id_obs text, sicen_cd_nom text, sicen_date_textuelle text, sicen_observateurs text, sicen_structure text, sicen_remarque text, sicen_date_integration text, sicen_date_obs text, sicen_precision text, sicen_geom text, sicen_date_debut text, sicen_date_fin text, sicen_effectif text, sicen_effectif_min text, sicen_effectif_max text, sicen_effectif_textuel text, sicen_type_effectif text, sicen_remarque_localisation text, sicen_statut_validation text, CONSTRAINT insertion_sicen_pkey PRIMARY KEY (gid) );
Une fois cette table créée et renseignée, la requête ci-dessous génère les commandes SQL de création des champs nécessaires à SICEN dans la table à importer, les met à jour.
SELECT * FROM ( SELECT DISTINCT concat(' ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_id_obs INTEGER; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_cd_nom text; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_date_obs date; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_date_debut date; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_date_fin date; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_date_textuelle text; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_observateurs text; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_structure text; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_remarque text; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_date_integration date; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_precision saisie.enum_precision; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_remarque_localisation text; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_statut_validation saisie.enum_statut_validation; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_effectif integer; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_effectif_min integer; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_effectif_max integer; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_effectif_textuel text; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_type_effectif text; ALTER TABLE ',table_schema,'."',table_name,'" ADD COLUMN sicen_geom geometry(geometry,2154);' ) FROM donnees_externes.insertion_sicen WHERE table_name ='_2018_flore_patrim_tuchan_2015' UNION SELECT DISTINCT concat(' UPDATE ',table_schema,'."',table_name,'" SET sicen_id_obs = nextval(''saisie.saisie_observation_id_obs_seq''::regclass); UPDATE ',table_schema,'."',table_name,'" SET sicen_cd_nom = ',sicen_cd_nom,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_date_integration = now()::date; UPDATE ',table_schema,'."',table_name,'" SET sicen_date_obs = ',sicen_date_obs,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_date_debut = ',sicen_date_debut,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_date_fin = ',sicen_date_fin,'; PDATE ',table_schema,'."',table_name,'" SET sicen_date_textuelle = ',sicen_date_textuelle,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_observateurs = ',sicen_observateurs,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_structure = ',sicen_structure,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_remarque = ',sicen_remarque,'; UPDATE ',table_schema,'."',table_name,'" SET date_integration = ',sicen_date_integration,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_precision = ',sicen_precision,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_geom = ',sicen_geom,';') FROM donnees_externes.insertion_sicen WHERE table_name ='_2018_flore_patrim_tuchan_2015' --ORDER BY table_name, ordinal_position UNION -- nécessaire car le nombre de paramètre passé à la fonction concat() est limité SELECT DISTINCT concat(' UPDATE ',table_schema,'."',table_name,'" SET sicen_effectif = ',sicen_effectif,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_effectif_min = ',sicen_effectif_min,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_effectif_max = ',sicen_effectif_max,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_effectif_textuel = ',sicen_effectif_textuel,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_type_effectif = ',sicen_type_effectif,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_remarque_localisation = ',sicen_remarque_localisation,'; UPDATE ',table_schema,'."',table_name,'" SET sicen_statut_validation = ',sicen_statut_validation,'; ') FROM donnees_externes.insertion_sicen WHERE table_name ='_2018_flore_patrim_tuchan_2015' ) foo ORDER BY 1
Le code généré est celui-ci (on export le résultat dans un fichier texte sinon pgadmin le tronque à l'affichage.
ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_id_obs INTEGER; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_cd_nom text; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_date_obs date; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_date_debut date; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_date_fin date; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_date_textuelle text; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_observateurs text; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_structure text; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_remarque text; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_date_integration date; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_precision saisie.enum_precision; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_remarque_localisation text; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_statut_validation saisie.enum_statut_validation; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_effectif integer; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_effectif_min integer; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_effectif_max integer; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_effectif_textuel text; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_type_effectif text; ALTER TABLE donnees_externes."_2018_flore_patrim_tuchan_2015" ADD COLUMN sicen_geom geometry(geometry,2154); UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_id_obs = nextval('saisie.saisie_observation_id_obs_seq'::regclass); UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_cd_nom = code_nom; UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_date_integration = now()::date; UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_date_obs = CASE WHEN outils.isdate(CONCAT(annee,'-',lpad(mois::text,2,'0'),'-',lpad(jour::text,2,'0'))) THEN CONCAT(lpad(jour::text,2,'0'),'-',lpad(mois::text,2,'0'),'-',annee)::date ELSE NULL END; UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_date_debut = CASE WHEN outils.isdate(CONCAT(annee,'-',lpad(mois::text,2,'0'),'-',lpad(jour::text,2,'0'))) THEN CONCAT(lpad(jour::text,2,'0'),'-',lpad(mois::text,2,'0'),'-',annee)::date ELSE NULL END; UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_date_fin = NULL; UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_date_textuelle = CONCAT(lpad(jour::text,2,'0'),'-',lpad(mois::text,2,'0'),'-',annee); UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_observateurs = trim(CONCAT(obs_1, ' & ', obs_2, ' & ',obs_3),'& '); UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_structure = structure; UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_remarque = NULL; UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_precision = CASE WHEN length(code_insee) = 5 THEN 'commune' ELSE 'supra-communal' END::saisie.enum_precision; UPDATE donnees_externes."_2018_flore_patrim_tuchan_2015" SET sicen_geom = st_transform(geom, 2154);
Il ne me reste plus qu'à faire ma reqête d'insertion, à adapter selon les cas :
INSERT INTO saisie.saisie_observation(id_obs, date_obs, date_debut_obs, date_fin_obs, date_textuelle, regne, nom_vern, nom_complet, cd_nom, effectif, effectif_min, effectif_max, effectif_textuel,longitude, latitude, localisation, numerisateur, code_insee, diffusable, "precision", statut_validation, geometrie, nom_bdd, observateurs_pour_tri, structures_pour_tri) SELECT sicen_id_obs, sicen_date_obs::date, sicen_date_debut, sicen_date_fin, sicen_date_textuelle, regne, nom_vern, taxref.nom_complet, sicen_cd_nom, sicen_effectif, sicen_effectif_min, sicen_effectif_max, sicen_effectif_textuel, st_x(st_centroid(st_transform(geom,4326))),st_y(st_centroid(st_transform(geom,4326))), sicen_remarque_localisation, 7, CASE WHEN length(code_insee::text)=5 THEN code_insee ELSE NULL END, false, sicen_precision::saisie.enum_precision, sicen_statut_validation, sicen_geom, 'ENS11', sicen_observateurs, sicen_structure FROM donnees_externes."_2018_flore_patrim_tuchan_2015" JOIN inpn.taxref ON sicen_cd_nom::text = cd_nom JOIN ign_bd_topo.commune ON st_intersects(commune.geometrie, st_centroid(geom));
