CEN38/Python_scripts
2
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Python_scripts/1_SICEN/import_donneesaisieTOsicen.py

333 lines
17 KiB
Python
Raw Permalink Blame History

#!/usr/bin/env python3
# -*- coding: UTF-8 -*-.
from sqlalchemy import create_engine, text
from sqlalchemy.engine import URL
from datetime import datetime as dt
import pandas as pd
import geopandas as gpd
nom_numerateur = 'GEIER'
pnom_numerator = 'Colas'
today = dt.now().date().isoformat()
# Parametres bdd
user = 'cen_admin'
pwd = '#CEN38@venir'
adr = '91.134.194.221'
port = '5432'
base = 'sicen2'
url = URL.create('postgresql+psycopg2',
username=user,
password=pwd,
host=adr,
database=base,
)
con = create_engine(url)
nom_numerateur = nom_numerateur.upper()
pnom_numerator = pnom_numerator[0].upper() + pnom_numerator[1:]
query_get_numerator = """
SELECT * FROM md.personne
WHERE nom = '%s'
AND prenom = '%s'
AND id_structure = 1
""" % (nom_numerateur,pnom_numerator)
numerateur = pd.read_sql_query(
sql = query_get_numerator,
con = con
)
select_import = """
SELECT * FROM import.import_data_cen38;
"""
data_import = pd.read_sql_query(
sql = select_import,
con = con)
check_datageom = """
DROP VIEW IF EXISTS import.test_geom_prestation;
CREATE OR REPLACE VIEW import.test_geom_prestation as (
SELECT ROW_NUMBER() OVER (ORDER BY date_obs ASC) AS gid, localisation,
CASE WHEN latitude::numeric > 6000000
THEN ST_GeomFromText('POINT('||longitude::double precision||' '||latitude::double precision||')',2154) -- coordonnées en lambert93
WHEN latitude::numeric > 50 AND latitude::numeric < 3000000
THEN ST_transform(ST_GeomFromText('POINT('||longitude::double precision||' '||latitude::double precision||')',27572),2154) -- coordonnées en Lambert II étendu
--WHEN latitude is null THEN st_pointonsurface(b.geom) -- pas de coordonnées XY on récupère la geom du centroide du site CenRA si elle existe et si une partie du nom du site est renseignée dans la colonne LOCALISATION
ELSE ST_transform(ST_GeomFromText('POINT('||longitude::double precision||' '||latitude::double precision||')',4326),2154) -- coordonnées en WGS84
END as geometrie
FROM import.import_data_cen38
--LEFT JOIN import.sites_serena_cren_mu_2015 b ON lower(b.nom_site) like '%'||lower(trim(localisation,'s'))||'%' -- on joint la table des sites CenRA pour récupérer éventuellement le centroide d'un site si pas de XY
WHERE cd_nom is not null and (date_obs is not null or date_debut_obs is not null)) ; -- pour ignorer les lignes vides qui traineraient dans le csv
SELECT * FROM import.test_geom_prestation;
"""
df_check_datageom = gpd.read_postgis(
sql = text(check_datageom),
con = con,
geom_col='geometrie')
if df_check_datageom.geometry.is_valid.all() \
and df_check_datageom.crs.srs == 'epsg:2154':
drop_datageom = """DROP VIEW IF EXISTS import.test_geom_prestation;"""
with con.begin() as cnx:
cnx.execute(drop_datageom)
create_table_pers = """
DROP TABLE IF EXISTS md.temp_import_personne;
CREATE TABLE md.temp_import_personne AS
WITH personnel AS (
SELECT a.id_personne,a.nom,a.prenom,b.id_structure,b.nom_structure
FROM md.personne a
JOIN md.structure b USING (id_structure)
)
SELECT DISTINCT
-- personne
CASE WHEN observateur2 is null THEN (c.nom||' '||c.prenom)::text
WHEN observateur2 is not null AND observateur3 is null THEN (c.nom||' '||c.prenom)||'&'||(d.nom||' '||d.prenom)
WHEN observateur3 is not null AND observateur4 is null THEN (c.nom||' '||c.prenom)||'&'||(d.nom||' '||d.prenom)||'&'||(e.nom||' '||e.prenom)
WHEN observateur4 is not null AND observateur5 is null THEN (c.nom||' '||c.prenom)||'&'||(d.nom||' '||d.prenom)||'&'||(e.nom||' '||e.prenom)||'&'||(f.nom||' '||f.prenom)
ELSE c.nom_structure||'&'||d.nom_structure||'&'||(e.nom||' '||e.prenom)||'&'||(f.nom||' '||f.prenom)||'&'||(g.nom||' '||g.prenom)
END AS personne,
-- nom_structure
CASE WHEN observateur2 is null THEN c.nom_structure::text
WHEN observateur2 is not null AND observateur3 is null AND c.nom_structure <> d.nom_structure
THEN c.nom_structure||'&'||d.nom_structure
WHEN observateur2 is not null AND observateur3 is null AND c.nom_structure = d.nom_structure
THEN c.nom_structure
WHEN observateur3 is not null AND observateur4 is null AND c.nom_structure <> d.nom_structure AND d.nom_structure <> e.nom_structure
AND c.nom_structure <> e.nom_structure
THEN c.nom_structure||'&'||d.nom_structure||'&'||e.nom_structure
WHEN observateur3 is not null AND observateur4 is null AND c.nom_structure = d.nom_structure AND d.nom_structure <> e.nom_structure
THEN c.nom_structure||'&'||e.nom_structure
WHEN observateur3 is not null AND observateur4 is null AND c.nom_structure <> d.nom_structure AND d.nom_structure = e.nom_structure
THEN c.nom_structure||'&'||d.nom_structure
WHEN observateur4 is not null AND observateur5 is null AND c.nom_structure <> d.nom_structure
THEN c.nom_structure||'&'||d.nom_structure||'&'||e.nom_structure||'&'||f.nom_structure
ELSE c.nom_structure||'&'||d.nom_structure||'&'||e.nom_structure||'&'||f.nom_structure||'&'||g.nom_structure
END AS nom_structure,
-- observateur
CASE WHEN observateur2 is null THEN c.id_personne::text
WHEN observateur2 is not null AND observateur3 is null THEN c.id_personne||'&'||d.id_personne
WHEN observateur3 is not null AND observateur4 is null THEN c.id_personne||'&'||d.id_personne||'&'||e.id_personne
WHEN observateur4 is not null AND observateur5 is null THEN c.id_personne||'&'||d.id_personne||'&'||e.id_personne||'&'||f.id_personne
ELSE c.id_personne||'&'||d.id_personne||'&'||e.id_personne||'&'||f.id_personne||'&'||g.id_personne
END AS observateur,
structure
FROM import.import_data_cen38 a
LEFT JOIN personnel c ON (c.nom||' '||c.prenom) = trim(a.observateur1::text) AND lower(c.nom_structure) = lower(a.structure)
LEFT JOIN personnel d ON (d.nom||' '||d.prenom) = trim(a.observateur2::text) AND lower(d.nom_structure) = lower(a.structure)
LEFT JOIN personnel e ON (e.nom||' '||e.prenom) = trim(a.observateur3::text) AND lower(e.nom_structure) = lower(a.structure)
LEFT JOIN personnel f ON (f.nom||' '||f.prenom) = trim(a.observateur4::text) AND lower(f.nom_structure) = lower(a.structure)
LEFT JOIN personnel g ON (g.nom||' '||g.prenom) = trim(a.observateur5::text) AND lower(g.nom_structure) = lower(a.structure)
;
""".format(id_creator = numerateur.id_personne.item())
with con.begin() as cnx:
cnx.execute(create_table_pers)
select_pers = 'SELECT * FROM md.temp_import_personne'
df_pers = pd.read_sql_query(
sql = select_pers,
con = con)
with_dataTOinsert = """
WITH personnel AS (
SELECT a.id_personne,a.nom,a.prenom,b.id_structure,b.nom_structure
FROM md.personne a
JOIN md.structure b USING (id_structure)
),
t as (
SELECT
CASE WHEN a.heure IS NULL THEN '00:00:00'
ELSE a.heure END heure,
a.date_obs, a.date_debut_obs, a.date_fin_obs, a.duree as date_textuelle,
CASE WHEN b.regne IS NULL THEN bb.regne
ELSE b.regne END regne,
CASE WHEN b.phylum IS NULL THEN bb.phylum
ELSE b.phylum END phylum,
CASE WHEN b.classe IS NULL THEN bb.classe
ELSE b.classe END classe,
CASE WHEN b.ordre IS NULL THEN bb.ordre
ELSE b.ordre END ordre,
CASE WHEN b.famille IS NULL THEN bb.famille
ELSE b.famille END famille,
CASE WHEN b.nom_valide IS NULL THEN bb.nom_valide
ELSE b.nom_valide END nom_valide,
CASE WHEN b.nom_vern IS NULL THEN bb.nom_vern
ELSE b.nom_vern END nom_vern,
CASE WHEN b.nom_complet IS NULL THEN bb.nom_complet
ELSE b.nom_complet END nom_complet,
CASE WHEN b.cd_nom IS NULL THEN bb.cd_nom::text
ELSE b.cd_nom END cd_nom,
a.abondance_dominance as effectif_textuel, a.effectif_min, a.effectif_max,
CASE WHEN strate::text is not null THEN strate::text
ELSE age::text END AS type_effectif,
CASE WHEN phenologie is not null THEN phenologie
ELSE sexe END AS phenologie,
CASE WHEN latitude::numeric > 6000000
THEN ST_X(st_transform(ST_GeomFromText('POINT('||longitude::double precision||' '||latitude::double precision||')',2154),4326)) -- coordonnée X en lambert93
WHEN latitude::numeric > 50 AND latitude::numeric < 3000000
THEN ST_X(st_transform(ST_GeomFromText('POINT('||longitude::double precision||' '||latitude::double precision||')',27572),4326)) -- coordonnées en Lambert II étendu
-- WHEN latitude is null
-- THEN ST_X(st_transform(st_pointonsurface(i.geom),4326)) -- pas de coordonnées XY on récupère la geom du centroide du site CenRA si elle existe et si une partie du nom du site est renseignée dans la colonne LOCALISATION
ELSE longitude::double precision -- coordonnées en WGS84
END as longitude,
CASE WHEN latitude::numeric > 6000000
THEN ST_Y(st_transform(ST_GeomFromText('POINT('||longitude::double precision||' '||latitude::double precision||')',2154),4326)) -- coordonnée X en lambert93
WHEN latitude::numeric > 50 AND latitude::numeric < 3000000
THEN ST_Y(st_transform(ST_GeomFromText('POINT('||longitude::double precision||' '||latitude::double precision||')',27572),4326)) -- coordonnées en Lambert II étendu
-- WHEN latitude is null
-- THEN ST_Y(st_transform(st_pointonsurface(i.geom),4326)) -- pas de coordonnées XY on récupère la geom du centroide du site CenRA si elle existe et si une partie du nom du site est renseignée dans la colonne LOCALISATION
ELSE latitude::double precision
END as latitude,
localisation,
CASE WHEN observateur2 is null THEN c.id_personne::text
WHEN observateur2 is not null AND observateur3 is null THEN c.id_personne||'&'||d.id_personne
WHEN observateur3 is not null AND observateur4 is null THEN c.id_personne||'&'||d.id_personne||'&'||e.id_personne
WHEN observateur4 is not null AND observateur5 is null THEN c.id_personne||'&'||d.id_personne||'&'||e.id_personne||'&'||f.id_personne
ELSE c.id_personne||'&'||d.id_personne||'&'||e.id_personne||'&'||f.id_personne||'&'||g.id_personne
END AS observateur,
{id_numerateur}::integer as numerisateur, -- adapter l'identifiant du numérisateur en fonction de la personne qui réalise l'import en masse
-- cas d'une prestation on ajoute le CenRA à la structure de l'observateur '&1' sinon il faut supprimer
CASE WHEN h.id_structure <> 1 THEN (h.id_structure||'&1')::text
ELSE h.id_structure::text END as structure,
remarque_obs,
CASE WHEN lower(a.structure) like '%CEN Isère%' THEN true::boolean
ELSE false::boolean END as diffusable,
precision::saisie.enum_precision as precision,
-- CASE
-- WHEN latitude is null AND round(st_maxdistance(st_PointOnSurface(i.geom), st_convexhull(i.geom))::numeric,-2) < 101 THEN '10 à 100m'::saisie.enum_precision
-- WHEN latitude is null AND round(st_maxdistance(st_PointOnSurface(i.geom), st_convexhull(i.geom))::numeric,-2) < 501 AND round(st_maxdistance(ST_PointOnSurface(i.geom), st_convexhull(i.geom))::numeric,-2) > 100 THEN '100 à 500m'::saisie.enum_precision
-- WHEN latitude is null AND round(st_maxdistance(st_PointOnSurface(i.geom), st_convexhull(i.geom))::numeric,-2) < 1001 AND round(st_maxdistance(ST_PointOnSurface(i.geom), st_convexhull(i.geom))::numeric,-2) > 500 THEN '500 à 1000m'::saisie.enum_precision
-- WHEN latitude is null AND round(st_maxdistance(st_PointOnSurface(i.geom), st_convexhull(i.geom))::numeric,-2) > 1001 THEN '> 1000m'::saisie.enum_precision
-- ELSE precision::saisie.enum_precision
-- END as precision,
'validée'::saisie.enum_statut_validation as statut_validation,
j.id_etude,
k.id_protocole,
effectif::integer,
CASE WHEN statut_repro::text IS NOT NULL THEN (statut_repro::text)::saisie.enum_reprostatut
ELSE NULL END reprostatut,
CASE WHEN latitude::numeric > 6000000
THEN ST_GeomFromText('POINT('||longitude::double precision||' '||latitude::double precision||')',2154) -- coordonnées en lambert93
WHEN latitude::numeric > 50 AND latitude::numeric < 3000000
THEN ST_transform(ST_GeomFromText('POINT('||longitude::double precision||' '||latitude::double precision||')',27572),2154) -- coordonnées en Lambert II étendu
-- WHEN latitude is null
-- THEN st_pointonsurface(i.geom) -- pas de coordonnées XY on récupère la geom du centroide du site CenRA si elle existe et si une partie du nom du site est renseignée dans la colonne LOCALISATION
ELSE ST_transform(ST_GeomFromText('POINT('||longitude::double precision||' '||latitude::double precision||')',4326),2154) -- coordonnées en WGS84
END as geometrie,
code_releve,
CASE WHEN obs_null::text ilike 'oui' THEN true::boolean
ELSE false::boolean END as obs_null
FROM import.import_data_cen38 a
LEFT JOIN inpn.taxref b ON b.cd_nom::text = a.cd_nom::text
LEFT JOIN inpn.taxref bb ON bb.nom_complet::text = a.nom_complet::text
LEFT JOIN personnel c ON (c.nom||' '||c.prenom) = trim(a.observateur1::text) AND lower(c.nom_structure) = lower(a.structure)
LEFT JOIN personnel d ON (d.nom||' '||d.prenom) = trim(a.observateur2::text) AND lower(d.nom_structure) = lower(a.structure)
LEFT JOIN personnel e ON (e.nom||' '||e.prenom) = trim(a.observateur3::text) AND lower(e.nom_structure) = lower(a.structure)
LEFT JOIN personnel f ON (f.nom||' '||f.prenom) = trim(a.observateur4::text) AND lower(f.nom_structure) = lower(a.structure)
LEFT JOIN personnel g ON (g.nom||' '||g.prenom) = trim(a.observateur5::text) AND lower(g.nom_structure) = lower(a.structure)
JOIN md.structure h ON lower(h.nom_structure) = lower(replace(replace(a.structure,' & ','&'),'CEN38&',''))
LEFT JOIN md.etude j on a.id_etude = j.nom_etude
LEFT JOIN md.protocole k USING (id_protocole)
--LEFT JOIN import.sites_serena_cren_mu_2015 i ON lower(i.nom_site) like '%'||lower(trim(localisation,'s'))||'%' -- on joint la table des sites CenRA pour récupérer éventuellement le centroide d'un site si pas de XY
--JOIN md.etude i ON i.nom_etude = a.id_etude -- à utiliser si les champs id_etude et id_protocole contiennent le libelle plutôt que l'identifiant, modifier aussi au-dessus id_etude en i.id_etude, id_protocole en j.id_protocole
--JOIN md.protocole j ON j.libelle = a.id_protocole
)
""".format(id_numerateur=numerateur.id_personne.item())
select_dataTOinsert = """
SELECT DISTINCT
heure::TIME WITHOUT TIME ZONE as heure_obs, date_obs, date_debut_obs, date_fin_obs, date_textuelle,
regne, nom_vern, nom_complet, cd_nom, effectif_textuel,
effectif_min, effectif_max, type_effectif, phenologie,
longitude, latitude,
localisation,
observateur, numerisateur,
structure, remarque_obs,
c.code_insee,
diffusable, "precision",
statut_validation,
id_etude, id_protocole,
effectif, reprostatut,
t.geometrie,
code_releve id_waypoint,
obs_null
FROM t, ign_bd_topo.commune c
WHERE ST_intersects(t.geometrie ,c.geometrie)
--ORDER BY nom_complet, date_obs, longitude, latitude
;
"""
df_data_insert = gpd.read_postgis(
sql = text(with_dataTOinsert+select_dataTOinsert),
con = con,
geom_col='geometrie')
# pd.read_sql_query(
# sql = text(with_dataTOinsert+select_dataTOinsert),
# con = con,)
if df_data_insert.shape[0] == data_import.shape[0]:
insert_data = """
INSERT INTO saisie.saisie_observation(
heure_obs, date_obs, date_debut_obs, date_fin_obs, date_textuelle, regne, nom_vern, nom_complet, cd_nom, effectif_textuel,
effectif_min, effectif_max, type_effectif, phenologie, longitude, latitude, localisation, observateur, numerisateur, structure, remarque_obs, code_insee, diffusable, "precision",
statut_validation, id_etude, id_protocole, effectif, reprostatut, geometrie, id_waypoint, obs_null)
"""
with con.begin() as cnx:
cnx.execute(
text(with_dataTOinsert+
insert_data+
select_dataTOinsert)
)
update_structure_name = """
UPDATE saisie.suivi_saisie_observation a
SET utilisateur = REPLACE(utilisateur, 'inconnu', '{email_numerator}')
WHERE date_operation > '{today}'
;
""".format(
email_numerator=numerateur.email.item(),
today = today
)
drop_temp_table = """
DROP TABLE IF EXISTS md.temp_import_personne;
"""
with con.begin() as cnx:
cnx.execute(update_structure_name)
cnx.execute(drop_temp_table)
else :
print(data_import[~data_import.index.isin(df_data_insert.index)])
data_import[~data_import.index.isin(data_import.drop_duplicates().index)]
check_datageom = """
DROP VIEW IF EXISTS import.test_geom_prestation;
"""
with con.begin() as cnx:
cnx.execute(check_datageom)
Reference in New Issue View Git Blame Copy Permalink