Import de données dans SICEN

Soumis par 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));

Tags: 

Les contenus des sites sig.cenlr.org et si.cenlr.org sont mis à disposition selon les termes de la licence Creative Commons Attribution 3.0 France