Soumis par Mathieu Bossaert le
Pour le congrès de Dunkerque d'octobre 2015 et le travail d'analyse de la contribution des CEN à la connaissance naturaliste, nous avons entrepris de faire remonter les deonnées du réesau au format "DEE" du SINP.
Ce travail nous a permis de consolider prés de 2000000 de données.
Voici la rquête SQL qui nous a peris cette mise en forme depuis la base sicen.
SELECT ROW_NUMBER() OVER (ORDER BY (lpad(saisie_observation.id_obs::text, 7, '0'::text))) AS gid, CASE saisie_observation.determination::text WHEN 'Vu'::text THEN 'te'::text WHEN 'Entendu'::text THEN 'te'::text WHEN 'Indice de présence'::text THEN 'te'::text WHEN 'Cadavre'::text THEN 'te'::text WHEN 'Capture'::text THEN 'te'::text WHEN 'Collection'::text THEN 'Co'::text WHEN 'Littérature'::text THEN 'Li'::text ELSE 'te'::text END AS statutsource, CASE saisie_observation.determination::text WHEN 'Littérature'::text THEN 'à préciser'::text ELSE NULL::text END AS referencebiblio, lpad(lot_donnee.id_lot::text, 4, '0'::text) AS jddid, 'SICEN-LR:00-175'::text AS jddcode, lpad(saisie_observation.id_obs::text, 7, '0'::text) AS identifiantorigine, NULL::text AS identifiantpermanent, CASE WHEN lot_donnee.libelle::text ~~* 'bénévolat%'::text THEN 'Pr'::text WHEN saisie_observation.id_etude = ANY (ARRAY[39, 51]) THEN 'NSP'::text ELSE 'Ac'::text END AS dspublique, NULL::text AS codeidcnpdispositif, 'CEN L-R'::text AS organismestandard, CASE WHEN LOWER(saisie_observation.type_effectif) = 'absence'::text THEN 'No'::text ELSE 'Pr'::text END AS statutobservation, btrim(concat(saisie_observation.nom_vern, ' / ', saisie_observation.nom_complet), ' / '::text) AS nomcite, saisie_observation.cd_nom::INTEGER AS cdnom, taxref.cd_ref::INTEGER AS cdref, 'non'::text AS sensible, COALESCE(saisie_observation.effectif, saisie_observation.effectif_min, 1::BIGINT)::INTEGER AS denombrementmin, COALESCE(saisie_observation.effectif, saisie_observation.effectif_max)::INTEGER AS denombrementmax, CASE WHEN saisie_observation.type_effectif ~~* ANY (ARRAY['abondance%'::text, 'classe%'::text]) THEN 'Es'::text ELSE 'Co'::text END AS typedenombrement, CASE WHEN saisie_observation.type_effectif IS NOT NULL THEN 'In'::text ELSE NULL::text END AS objetdenombrement, CASE WHEN saisie_observation.observateur = '20'::text THEN 'NSP'::text ELSE md.liste_nom_auteur(saisie_observation.observateur, ', '::text) END AS identiteobservateur, CASE md.liste_nom_structure(saisie_observation.STRUCTURE, ', '::text) WHEN 'Pas de structure'::text THEN 'indépendant'::text ELSE md.liste_nom_structure(saisie_observation.STRUCTURE, ', '::text) END AS organismeobservateur, 'CEN L-R'::text AS organismegestionnairedonnees, CASE WHEN saisie_observation.observateur <> '20'::text THEN md.liste_nom_auteur(saisie_observation.observateur, ', '::text) ELSE NULL::text END AS determinateur, btrim(concat(validateur.nom, ' ', validateur.prenom)) AS validateur, saisie_observation.remarque_obs AS commentaire, COALESCE(saisie_observation.date_obs, saisie_observation.date_debut_obs) AS datedebut, COALESCE(saisie_observation.date_obs, saisie_observation.date_fin_obs) AS datefin, NULL::TIME WITHOUT TIME zone AS heuredebut, NULL::TIME WITHOUT TIME zone AS heurefin, NULL::DATE AS datedeterminationobs, saisie_observation.elevation::NUMERIC AS altitudemin, saisie_observation.elevation::NUMERIC AS altitudemax, NULL::NUMERIC AS profondeurmin, NULL::NUMERIC AS profondeurmax, NULL::text AS codehabitat, NULL::text AS refhabitat, st_asgml(saisie_observation.geometrie) AS geometrie, CASE saisie_observation."precision" WHEN 'GPS'::saisie.enum_precision THEN 10 WHEN '0 à 10m'::saisie.enum_precision THEN 10 WHEN '10 à 100m'::saisie.enum_precision THEN 100 WHEN '100 à 500m'::saisie.enum_precision THEN 500 ELSE 1000 END AS "precision", CASE WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%POINT%'::text THEN 'St'::text WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%POLYGON%'::text OR st_geometrytype(saisie_observation.geometrie) ~~* '%LINE%'::text THEN 'In'::text ELSE 'NSP'::text END AS natureobjetgeo, COALESCE(saisie_observation.code_insee, commune.code_insee::text) AS codecommune, commune.nom AS nomcommune, sites_cen_inpn_2014.id_mnhn::text AS codeen, NULL::text AS typeen, NULL::text AS codemaille, st_x(st_centroid(saisie_observation.geometrie)) AS st_x, st_y(st_centroid(saisie_observation.geometrie)) AS st_y FROM saisie.saisie_observation JOIN inpn.taxref_v8 taxref USING (cd_nom) JOIN ign_bd_topo.commune ON st_intersects(commune.geometrie, saisie_observation.geometrie) LEFT JOIN referentiels_divers.sites_cen_inpn_2014 ON st_intersects(sites_cen_inpn_2014.geometrie, saisie_observation.geometrie) LEFT JOIN md.lot_donnee ON saisie_observation.id_etude = lot_donnee.id_etude AND saisie_observation.id_protocole = lot_donnee.id_protocole AND (( CASE WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Point%'::text THEN 'point'::text WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%LineString%'::text THEN 'ligne'::text WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Polygon%'::text THEN 'perimetre'::text ELSE NULL::text END || '_'::text) || CASE saisie_observation.regne WHEN 'Plantae'::text THEN 'espece'::text WHEN 'Animalia'::text THEN 'espece'::text WHEN 'Fungi'::text THEN 'espece'::text WHEN 'Habitat'::text THEN 'habitat'::text ELSE NULL::text END) = lot_donnee.type_donnee::text LEFT JOIN md.personne validateur ON saisie_observation.validateur = validateur.id_personne WHERE taxref.cd_nom::text ~ '^[\d+]'::text AND (saisie_observation.regne = ANY (ARRAY['Animalia'::text, 'Plantae'::text, 'Fungi'::text])) AND (md.liste_nom_structure(saisie_observation.STRUCTURE, ', '::text) = ANY (ARRAY['CEN LR'::text, 'Pas de structure'::text])) AND saisie_observation.cd_nom <> '000000'::text;