70 lines
2.0 KiB
Python
70 lines
2.0 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-.
|
|
|
|
from sqlalchemy import create_engine #, text
|
|
from sqlalchemy.engine import URL
|
|
import geopandas as gpd
|
|
from pycen.zh import zh
|
|
zh = zh()
|
|
|
|
file = '/home/colas/Documents/9_PROJETS/1_ZH/SIMBY_zonage_Belledonne.txt'
|
|
# 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)
|
|
|
|
if file :
|
|
lst_site = gpd.pd.read_csv(file)
|
|
lst_site = [*lst_site.site]
|
|
|
|
sql = """
|
|
SELECT * FROM zones_humides.v_zoneshumides
|
|
"""
|
|
sql = sql + " WHERE site_code IN ('%s')" % "','".join(lst_site)
|
|
VZH = gpd.read_postgis(sql,zh.con,geom_col='geom')
|
|
|
|
sql = """
|
|
SELECT * FROM saisie.saisie_observation s
|
|
WHERE ST_Intersects(s.geometrie,'SRID=2154;%s')
|
|
""" % VZH.unary_union
|
|
|
|
saisie = gpd.read_postgis(sql,con,geom_col='geometrie')
|
|
|
|
tmp = gpd.sjoin(VZH[['site_code','geom']], saisie)
|
|
ss = tmp[['site_code',
|
|
'phylum','regne','classe','ordre','famille',
|
|
'nom_valide','nom_vern','nom_complet','cd_nom']].copy()
|
|
ss.drop_duplicates(inplace=True)
|
|
ss.nom_valide = ss.nom_valide.replace([' \(#ref\)',],[''],regex=True)
|
|
ss.nom_complet = ss.nom_complet.replace([' \(#ref\)',],[''],regex=True)
|
|
ss.sort_values('nom_valide', inplace=True)
|
|
sp_enjeux = [
|
|
'Carex brunnescens (Pers.) Poir., 1813',
|
|
'Coenagrion mercuriale (Charpentier, 1840)',
|
|
'Drosera rotundifolia L., 1753',
|
|
'Juncus squarrosus L., 1753',
|
|
'Lycopodium annotinum L., 1753',
|
|
'Lycopodium clavatum L., 1753',
|
|
'Neottia cordata (L.) Rich., 1817',
|
|
'Pinguicula grandiflora subsp. rosea (Mutel) Casper, 1962',
|
|
# 'Pinguicula grandiflora Lam., 1789',
|
|
'Pinguicula grandiflora subsp. grandiflora Lam., 1789',
|
|
]
|
|
ss.nom_valide.unique()
|
|
|
|
ss[ss.nom_valide.isin(sp_enjeux)].to_excel('/home/colas/Documents/tmp/espèced_enjeux_belledonne.xlsx')
|
|
ss[ss.nom_complet.isin(sp_enjeux)].to_file('/home/colas/Documents/tmp/espèced_enjeux_belledonne.xlsx')
|
|
|
|
|
|
|