forked from CEN-RA/Plugin_QGIS
852 lines
38 KiB
Python
852 lines
38 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""
|
|
/***************************************************************************
|
|
SICEN
|
|
A QGIS plugin
|
|
Filtre sur données SICEN
|
|
-------------------
|
|
begin : 2014-10-17
|
|
copyright : (C) 2014 by Guillaume COSTES (CENRA)
|
|
email : guillaume.costes@espaces-naturels.fr
|
|
***************************************************************************/
|
|
|
|
/***************************************************************************
|
|
* *
|
|
* This program is free software; you can redistribute it and/or modify *
|
|
* it under the terms of the GNU General Public License as published by *
|
|
* the Free Software Foundation; either version 2 of the License, or *
|
|
* (at your option) any later version. *
|
|
* *
|
|
***************************************************************************/
|
|
"""
|
|
# Import the PyQt and QGIS libraries
|
|
from PyQt4.QtCore import *
|
|
from PyQt4.QtGui import *
|
|
from qgis.core import *
|
|
from qgis.gui import *
|
|
from qgis.utils import iface
|
|
|
|
# Initialize Qt resources from file resources.py
|
|
import resources_rc
|
|
# Import the code for the dialog
|
|
from sicendialog import SICENDialog
|
|
|
|
import os.path
|
|
import psycopg2
|
|
import qgis
|
|
import datetime
|
|
import csv
|
|
|
|
class SICEN:
|
|
|
|
def __init__(self, iface):
|
|
# Save reference to the QGIS interface
|
|
self.iface = iface
|
|
# initialize plugin directory
|
|
self.plugin_dir = os.path.dirname(__file__)
|
|
# initialize locale
|
|
locale = QSettings().value("locale/userLocale")[0:2]
|
|
localePath = os.path.join(self.plugin_dir, 'i18n', 'sicen_{}.qm'.format(locale))
|
|
|
|
if os.path.exists(localePath):
|
|
self.translator = QTranslator()
|
|
self.translator.load(localePath)
|
|
|
|
if qVersion() > '4.3.3':
|
|
QCoreApplication.installTranslator(self.translator)
|
|
|
|
# Create the dialog (after translation) and keep reference
|
|
self.dlg = SICENDialog()
|
|
|
|
def initGui(self):
|
|
self.toolBar = self.iface.addToolBar("SICEN")
|
|
self.toolBar.setObjectName("SICEN")
|
|
|
|
## Create action that will start plugin configuration
|
|
self.action = QAction(
|
|
QIcon(":/plugins/sicen/sicen.png"),
|
|
u"Ouverture des données SICEN", self.iface.mainWindow())
|
|
# connect the action to the run method
|
|
self.action.triggered.connect(self.ouverture)
|
|
|
|
# Add toolbar button and menu item
|
|
self.toolBar.addAction(self.action)
|
|
self.iface.addPluginToMenu(u"SICEN", self.action)
|
|
|
|
## Create action that will start plugin configuration
|
|
self.action = QAction(
|
|
QIcon(":/plugins/sicen/sicen_export.png"),
|
|
u"Export liste d'espèces", self.iface.mainWindow())
|
|
# connect the action to the run method
|
|
self.action.triggered.connect(self.export)
|
|
|
|
# Add toolbar button and menu item
|
|
self.toolBar.addAction(self.action)
|
|
self.iface.addPluginToMenu(u"SICEN", self.action)
|
|
|
|
self.menu = QMenu()
|
|
self.menu.setTitle( QCoreApplication.translate( "SICEN","&SICEN" ) )
|
|
|
|
self.sicen_ouverture = QAction( QIcon(":/plugins/SICEN/sicen.png"), QCoreApplication.translate("SICEN", u"Ouverture des données SICEN" ), self.iface.mainWindow() )
|
|
self.sicen_export = QAction( QIcon(":/plugins/SICEN/sicen_export.png"), QCoreApplication.translate("SICEN", u"Export liste d'espèces" ), self.iface.mainWindow() )
|
|
|
|
self.menu.addActions( [self.sicen_ouverture, self.sicen_export] )
|
|
|
|
menu_bar = self.iface.mainWindow().menuBar()
|
|
actions = menu_bar.actions()
|
|
lastAction = actions[ len( actions ) - 1 ]
|
|
menu_bar.insertMenu( lastAction, self.menu )
|
|
|
|
self.sicen_ouverture.triggered.connect(self.ouverture)
|
|
self.sicen_export.triggered.connect(self.export)
|
|
|
|
def unload(self):
|
|
# Remove the plugin menu item and icon
|
|
self.iface.removePluginMenu(u"&SICEN", self.action)
|
|
self.iface.removeToolBarIcon(self.action)
|
|
|
|
# run method that performs all the real work
|
|
def ouverture(self):
|
|
|
|
### config.txt
|
|
config = "//100.100.100.100/bd_sig/z_QGIS/config.txt" # Chemin du fichier config
|
|
|
|
# Fonction de lecture des lignes du fichier config
|
|
def readline(n):
|
|
with open(config, "r") as f:
|
|
for lineno, line in enumerate(f):
|
|
if lineno == n:
|
|
return line.strip() # Permet d'enlever les retours chariots
|
|
|
|
# Recuperation des donnees
|
|
host = readline(10)
|
|
port = readline(12)
|
|
dbname = readline(14)
|
|
user = readline(16)
|
|
password = readline(18)
|
|
|
|
# Connexion a la BD
|
|
con = psycopg2.connect("dbname="+ dbname + " user=" + user + " host=" + host + " password=" + password + " port=" + port)
|
|
cur = con.cursor()
|
|
|
|
## Creation des listes deroulantes
|
|
# Listing de valeur des champs
|
|
SQL_observateur = u"""SELECT DISTINCT nom || '_' || prenom as nom FROM md.personne WHERE nom LIKE '%CenRA%' ORDER BY nom"""
|
|
SQL_nom_com = u"""SELECT DISTINCT nom FROM saisie.commune ORDER BY nom"""
|
|
SQL_nom_vern = u"""SELECT DISTINCT nom_vern FROM _agregation_ra.observations_table ORDER BY nom_vern"""
|
|
SQL_nom_complet = u"""SELECT DISTINCT nom_complet FROM _agregation_ra.observations_table ORDER BY nom_complet"""
|
|
SQL_ordre = u"""SELECT DISTINCT ordre FROM _agregation_ra.observations_table ORDER BY ordre"""
|
|
|
|
# Generation des listes
|
|
cur.execute(SQL_observateur)
|
|
list_observateur = cur.fetchall()
|
|
|
|
cur.execute(SQL_nom_com)
|
|
list_nom_com = cur.fetchall()
|
|
|
|
cur.execute(SQL_nom_vern)
|
|
list_nom_vern = cur.fetchall()
|
|
|
|
cur.execute(SQL_nom_complet)
|
|
list_nom_complet = cur.fetchall()
|
|
|
|
cur.execute(SQL_ordre)
|
|
list_ordre = cur.fetchall()
|
|
|
|
con.close()
|
|
|
|
|
|
# Ajout des items dans les combobox
|
|
self.dlg.observateur_1.clear()
|
|
i = 0
|
|
while i < len(list_observateur):
|
|
self.dlg.observateur_1.addItems(list_observateur[i])
|
|
i=i+1
|
|
self.dlg.observateur_1.setCurrentIndex(-1) # Pour ne pas commencer la liste au premier item
|
|
|
|
self.dlg.observateur_2.clear()
|
|
i = 0
|
|
while i < len(list_observateur):
|
|
self.dlg.observateur_2.addItems(list_observateur[i])
|
|
i=i+1
|
|
self.dlg.observateur_2.setCurrentIndex(-1)
|
|
|
|
self.dlg.observateur_3.clear()
|
|
i = 0
|
|
while i < len(list_observateur):
|
|
self.dlg.observateur_3.addItems(list_observateur[i])
|
|
i=i+1
|
|
self.dlg.observateur_3.setCurrentIndex(-1)
|
|
|
|
self.dlg.observateur_4.clear()
|
|
i = 0
|
|
while i < len(list_observateur):
|
|
self.dlg.observateur_4.addItems(list_observateur[i])
|
|
i=i+1
|
|
self.dlg.observateur_4.setCurrentIndex(-1)
|
|
|
|
self.dlg.nom_com_1.clear()
|
|
i = 0
|
|
while i < len(list_nom_com):
|
|
self.dlg.nom_com_1.addItems(list_nom_com[i])
|
|
i=i+1
|
|
self.dlg.nom_com_1.setCurrentIndex(-1)
|
|
|
|
self.dlg.nom_com_2.clear()
|
|
i = 0
|
|
while i < len(list_nom_com):
|
|
self.dlg.nom_com_2.addItems(list_nom_com[i])
|
|
i=i+1
|
|
self.dlg.nom_com_2.setCurrentIndex(-1)
|
|
|
|
self.dlg.nom_vern.clear()
|
|
i = 0
|
|
while i < len(list_nom_vern):
|
|
self.dlg.nom_vern.addItems(list_nom_vern[i])
|
|
i=i+1
|
|
self.dlg.nom_vern.setCurrentIndex(-1)
|
|
|
|
self.dlg.nom_complet.clear()
|
|
i = 0
|
|
while i < len(list_nom_complet):
|
|
self.dlg.nom_complet.addItems(list_nom_complet[i])
|
|
i=i+1
|
|
self.dlg.nom_complet.setCurrentIndex(-1)
|
|
|
|
self.dlg.ordre.clear()
|
|
i = 0
|
|
while i < len(list_ordre):
|
|
self.dlg.ordre.addItems(list_ordre[i])
|
|
i=i+1
|
|
self.dlg.ordre.setCurrentIndex(-1)
|
|
|
|
# show the dialog
|
|
self.dlg.show()
|
|
# Run the dialog event loop
|
|
result = self.dlg.exec_()
|
|
# See if OK was pressed
|
|
if result == 1:
|
|
|
|
### config.txt
|
|
config = "//100.100.100.100/bd_sig/z_QGIS/config.txt" # Chemin du fichier config
|
|
|
|
# Fonction de lecture des lignes du fichier config
|
|
def readline(n):
|
|
with open(config, "r") as f:
|
|
for lineno, line in enumerate(f):
|
|
if lineno == n:
|
|
return line.strip() # Permet d'enlever les retours chariots
|
|
|
|
# Recuperation des donnees
|
|
host = readline(10)
|
|
port = readline(12)
|
|
dbname = readline(14)
|
|
user = readline(16)
|
|
password = readline(18)
|
|
|
|
# Requete filtre observateur
|
|
if self.dlg.observateur_1.currentIndex() != -1 :
|
|
sql_obs1 = """ "observateur" LIKE '%""" + self.dlg.observateur_1.currentText() + "%' "
|
|
else :
|
|
sql_obs1 = ''
|
|
|
|
if self.dlg.observateur_2.currentIndex() != -1 :
|
|
sql_obs2 = """ "observateur" LIKE '%""" + self.dlg.observateur_2.currentText() + "%' "
|
|
else :
|
|
sql_obs2 = ''
|
|
|
|
if self.dlg.observateur_3.currentIndex() != -1 :
|
|
sql_obs3 = """ "observateur" LIKE '%""" + self.dlg.observateur_3.currentText() + "%' "
|
|
else :
|
|
sql_obs3 = ''
|
|
|
|
if self.dlg.observateur_4.currentIndex() != -1 :
|
|
sql_obs4 = """ "observateur" LIKE '%""" + self.dlg.observateur_4.currentText() + "%' "
|
|
else :
|
|
sql_obs4 = ''
|
|
|
|
reqwhere_obs = sql_obs1 + 'OR' + sql_obs2 + 'OR' + sql_obs3 + 'OR' + sql_obs4
|
|
|
|
while reqwhere_obs[-2:] == 'OR' :
|
|
reqwhere_obs = reqwhere_obs[:-2]
|
|
while reqwhere_obs[:2] == 'OR' :
|
|
reqwhere_obs = reqwhere_obs[2:]
|
|
|
|
# Requete communes
|
|
# Connexion a la BD
|
|
con = psycopg2.connect("dbname="+ dbname + " user=" + user + " host=" + host + " password=" + password + " port=" + port)
|
|
cur = con.cursor()
|
|
|
|
if self.dlg.nom_com_1.currentIndex() != -1 :
|
|
nom_com = self.dlg.nom_com_1.currentText()
|
|
SQL_code_INSEE = u"""SELECT DISTINCT code_insee FROM saisie.commune WHERE nom = '""" + nom_com + "'"
|
|
cur.execute(SQL_code_INSEE)
|
|
list_code_INSEE = cur.fetchall()
|
|
code_INSEE = list_code_INSEE[0][0]
|
|
|
|
sql_insee_1 = """ "code_insee" = '""" + code_INSEE + "' "
|
|
else :
|
|
sql_insee_1 = ''
|
|
|
|
if self.dlg.nom_com_2.currentIndex() != -1 :
|
|
nom_com = self.dlg.nom_com_2.currentText()
|
|
SQL_code_INSEE = u"""SELECT DISTINCT code_insee FROM saisie.commune WHERE nom = '""" + nom_com + "'"
|
|
cur.execute(SQL_code_INSEE)
|
|
list_code_INSEE = cur.fetchall()
|
|
code_INSEE = list_code_INSEE[0][0]
|
|
|
|
sql_insee_2 = """ "code_insee" = '""" + code_INSEE + "' "
|
|
else :
|
|
sql_insee_2 = ''
|
|
|
|
reqwhere_insee = sql_insee_1 + 'OR' + sql_insee_2
|
|
|
|
while reqwhere_insee[-2:] == 'OR' :
|
|
reqwhere_insee = reqwhere_insee[:-2]
|
|
while reqwhere_insee[:2] == 'OR' :
|
|
reqwhere_insee = reqwhere_insee[2:]
|
|
|
|
con.close()
|
|
|
|
# Requete date
|
|
today = datetime.datetime.now().strftime("%Y-%m-%d")
|
|
date_min = self.dlg.date_min.selectedDate().toString("yyyy-MM-dd")
|
|
date_max = self.dlg.date_max.selectedDate().toString("yyyy-MM-dd")
|
|
|
|
if date_min != today :
|
|
sql_date_min = """ "date_obs" >= '""" + date_min + "' "
|
|
else :
|
|
sql_date_min = ''
|
|
|
|
if date_max != today :
|
|
sql_date_max = """ "date_obs" <= '""" + date_max + "' "
|
|
else :
|
|
sql_date_max = ''
|
|
|
|
reqwhere_date = sql_date_min + 'AND' + sql_date_max
|
|
|
|
while reqwhere_obs[-3:] == 'AND' :
|
|
reqwhere_obs = reqwhere_obs[:-3]
|
|
while reqwhere_obs[:3] == 'AND' :
|
|
reqwhere_obs = reqwhere_obs[3:]
|
|
|
|
# Requete regne
|
|
if self.dlg.Animalia.isChecked() == 1 :
|
|
sql_Animalia = """"regne" = 'Animalia'"""
|
|
else :
|
|
sql_Animalia = ''
|
|
|
|
if self.dlg.Plantae.isChecked() == 1 :
|
|
sql_Plantae = """"regne" = 'Plantae'"""
|
|
else :
|
|
sql_Plantae = ''
|
|
|
|
reqwhere_regne = sql_Animalia + 'OR' + sql_Plantae
|
|
|
|
while reqwhere_regne[-2:] == 'OR' :
|
|
reqwhere_regne = reqwhere_regne[:-2]
|
|
while reqwhere_regne[:2] == 'OR' :
|
|
reqwhere_regne = reqwhere_regne[2:]
|
|
|
|
# Requete ordre
|
|
if self.dlg.ordre.currentIndex() != -1 :
|
|
reqwhere_ordre = """"ordre" = '""" + self.dlg.ordre.currentText() + "'"
|
|
else :
|
|
reqwhere_ordre = ''
|
|
|
|
# Requete sp
|
|
if self.dlg.nom_vern.currentIndex() != -1 :
|
|
sql_nom_vern = """"nom_vern" = '""" + self.dlg.nom_vern.currentText() + "'"
|
|
else :
|
|
sql_nom_vern = ''
|
|
|
|
if self.dlg.nom_complet.currentIndex() != -1 :
|
|
sql_nom_complet = """"nom_complet" = '""" + self.dlg.nom_complet.currentText() + "'"
|
|
else :
|
|
sql_nom_complet = ''
|
|
|
|
reqwhere_sp = sql_nom_vern + 'OR' + sql_nom_complet
|
|
|
|
while reqwhere_sp[-2:] == 'OR' :
|
|
reqwhere_sp = reqwhere_sp[:-2]
|
|
while reqwhere_sp[:2] == 'OR' :
|
|
reqwhere_sp = reqwhere_sp[2:]
|
|
|
|
# Requete filtre patrimonialite
|
|
if self.dlg.LRD_01.isChecked() == 1 :
|
|
sql_lrd01 = """ lrd01 IN ('VU','EN','RE','CR') """
|
|
else :
|
|
sql_lrd01 = ''
|
|
|
|
if self.dlg.LRD_07.isChecked() == 1 :
|
|
sql_lrd07 = """ lrd07 IN ('VU','EN','RE','CR') """
|
|
else :
|
|
sql_lrd07 = ''
|
|
|
|
if self.dlg.LRD_26.isChecked() == 1 :
|
|
sql_lrd26 = """ lrd26 IN ('VU','EN','RE','CR') """
|
|
else :
|
|
sql_lrd26 = ''
|
|
|
|
if self.dlg.LRD_38.isChecked() == 1 :
|
|
sql_lrd38 = """ lrd38 IN ('VU','EN','RE','CR', 'AS-1', 'AS-2', 'AS-3') """
|
|
else :
|
|
sql_lrd38 = ''
|
|
|
|
if self.dlg.LRD_42.isChecked() == 1 :
|
|
sql_lrd42 = """ lrd42 IN ('VU','EN','RE','CR') """
|
|
else :
|
|
sql_lrd42 = ''
|
|
|
|
if self.dlg.LRD_69.isChecked() == 1 :
|
|
sql_lrd69 = """ lrd69 IN ('VU','EN','RE','CR') """
|
|
else :
|
|
sql_lrd69 = ''
|
|
|
|
if self.dlg.LRD_73.isChecked() == 1 :
|
|
sql_lrd73 = """ lrd73 IN ('VU','EN','RE','CR') """
|
|
else :
|
|
sql_lrd73 = ''
|
|
|
|
if self.dlg.LRD_74.isChecked() == 1 :
|
|
sql_lrd74 = """ lrd74 IN ('VU','EN','RE','CR') """
|
|
else :
|
|
sql_lrd74 = ''
|
|
|
|
if self.dlg.LRR_Flore.isChecked() == 1 :
|
|
sql_LRR_Flore = u""" "TAXO_GROUPE" IN ('Plantes') AND (lrr like '%CR%' OR lrr like '%EN%' OR lrr like '%EW%' OR lrr like '%RE%' OR lrr like '%VU%')"""
|
|
else :
|
|
sql_LRR_Flore = ''
|
|
|
|
if self.dlg.LRR_Oiseaux.isChecked() == 1 :
|
|
sql_LRR_Oiseaux = u""" "TAXO_GROUPE" = 'Oiseaux' AND (lrr like '%RE %' OR lrr like '%CR %' OR lrr like '%EN %' OR lrr like '%VU %') """
|
|
else :
|
|
sql_LRR_Oiseaux = ''
|
|
|
|
if self.dlg.LRR_Autre.isChecked() == 1 :
|
|
sql_LRR_Autre = u""" (lrr like '%RE %' OR lrr like '%CR %' OR lrr like '%EN %' OR lrr like '%VU %') AND "TAXO_GROUPE" NOT IN ('Oiseaux', 'Plantes') """
|
|
else :
|
|
sql_LRR_Autre = ''
|
|
|
|
if self.dlg.LRN_Flore.isChecked() == 1 :
|
|
sql_LRN_Flore = u""" "TAXO_GROUPE" ilike 'plantes' AND (lrr like '%CR%' OR lrr like '%EN%' OR lrr like '%EW%' OR lrr like '%RE%' OR lrr like '%VU%') """
|
|
else :
|
|
sql_LRN_Flore = ''
|
|
|
|
if self.dlg.LRN_Oiseaux.isChecked() == 1 :
|
|
sql_LRN_Oiseaux = u""" "TAXO_GROUPE" = 'Oiseaux' AND (lrn like '%RE %' OR lrn like '%CR %' OR lrn like '%EN %' OR lrn like '%VU %') """
|
|
else :
|
|
sql_LRN_Oiseaux = ''
|
|
|
|
if self.dlg.LRN_Ortho.isChecked() == 1 :
|
|
sql_LRN_Ortho = u""" "TAXO_GROUPE" ilike 'orthoptères' AND (lrn like '1%' OR lrn like '2%' OR lrn like '3%') """
|
|
else :
|
|
sql_LRN_Ortho = ''
|
|
|
|
if self.dlg.LRN_Autre.isChecked() == 1 :
|
|
sql_LRN_Autre = u""" (lrr like '%CR%' OR lrr like '%EN%' OR lrr like '%EW%' OR lrr like '%RE%' OR lrr like '%VU%') AND "TAXO_GROUPE" NOT IN ('Oiseaux', 'Plantes', 'Orthoptères') """
|
|
else :
|
|
sql_LRN_Autre = ''
|
|
|
|
if self.dlg.LRE.isChecked() == 1 :
|
|
sql_lre = """ lre IN ('VU','EN','RE','CR', 'EW', 'EX') """
|
|
else :
|
|
sql_lre = ''
|
|
|
|
if self.dlg.UICN.isChecked() == 1 :
|
|
sql_UICN = """ lrm IN ('VU','EN','RE','CR', 'EW', 'EX') """
|
|
else :
|
|
sql_UICN = ''
|
|
|
|
|
|
if self.dlg.PATRIMONIALITE.isChecked() == 1 :
|
|
sql_patrimonialite = """"patrimonialite" IS NOT NULL """
|
|
else :
|
|
sql_patrimonialite = ''
|
|
|
|
if self.dlg.PATRI01.isChecked() == 1 :
|
|
sql_patri01 = """"patri01" IS NOT NULL """
|
|
else :
|
|
sql_patri01 = ''
|
|
|
|
if self.dlg.PATRI07.isChecked() == 1 :
|
|
sql_patri07 = """"patri07" IS NOT NULL """
|
|
else :
|
|
sql_patri07 = ''
|
|
|
|
if self.dlg.PATRI26.isChecked() == 1 :
|
|
sql_patri26 = """"patri26" IS NOT NULL """
|
|
else :
|
|
sql_patri26 = ''
|
|
|
|
if self.dlg.PATRI38.isChecked() == 1 :
|
|
sql_patri38 = """"patri38" IS NOT NULL """
|
|
else :
|
|
sql_patri38 = ''
|
|
|
|
if self.dlg.PATRI42.isChecked() == 1 :
|
|
sql_patri42 = """"patri42" IS NOT NULL """
|
|
else :
|
|
sql_patri42 = ''
|
|
|
|
if self.dlg.PATRI69.isChecked() == 1 :
|
|
sql_patri69 = """"patri69" IS NOT NULL """
|
|
else :
|
|
sql_patri69 = ''
|
|
|
|
if self.dlg.PATRI73.isChecked() == 1 :
|
|
sql_patri73 = """"patri73" IS NOT NULL """
|
|
else :
|
|
sql_patri73 = ''
|
|
|
|
if self.dlg.PATRI74.isChecked() == 1 :
|
|
sql_patri74 = """"patri74" IS NOT NULL """
|
|
else :
|
|
sql_patri74 = ''
|
|
|
|
|
|
if self.dlg.ZNIEFF.isChecked() == 1 :
|
|
sql_znieff = """"znieff" IS NOT NULL """
|
|
else :
|
|
sql_znieff = ''
|
|
|
|
if self.dlg.DH_AnnII.isChecked() == 1 :
|
|
sql_dh_an2 = """"dh_an2" IS NOT NULL """
|
|
else :
|
|
sql_dh_an2 = ''
|
|
|
|
if self.dlg.PD_38.isChecked() == 1 :
|
|
sql_pd38 = """"pd38" IS NOT NULL """
|
|
else :
|
|
sql_pd38 = ''
|
|
|
|
if self.dlg.PD_42.isChecked() == 1 :
|
|
sql_pd42 = """"pd42" IS NOT NULL """
|
|
else :
|
|
sql_pd42 = ''
|
|
|
|
if self.dlg.PN.isChecked() == 1 :
|
|
sql_pn = """"pn" IS NOT NULL """
|
|
else :
|
|
sql_pn = ''
|
|
|
|
if self.dlg.PR.isChecked() == 1 :
|
|
sql_pr = """"pr" IS NOT NULL """
|
|
else :
|
|
sql_pr = ''
|
|
|
|
reqwhere_pat = sql_lrd01 + 'OR' + sql_lrd07 + 'OR' + sql_lrd26 + 'OR' + sql_lrd38 + 'OR' + sql_lrd42 + 'OR' + sql_lrd69 + 'OR' + sql_lrd73 + 'OR' + sql_lrd74 + 'OR' + sql_lre + 'OR' + sql_LRN_Flore + 'OR' + sql_LRN_Oiseaux + 'OR' + sql_LRN_Ortho + 'OR' + sql_LRN_Autre + 'OR' + sql_LRR_Flore + 'OR' + sql_LRR_Oiseaux + 'OR' + sql_LRR_Autre + 'OR' + sql_UICN + 'OR' + sql_pd38 + 'OR' + sql_pd42 + 'OR' + sql_pn + 'OR' + sql_pr + 'OR' + sql_dh_an2 + 'OR' + sql_patri01 + 'OR' + sql_patri07 + 'OR' + sql_patri26 + 'OR' + sql_patri38 + 'OR' + sql_patri42 + 'OR' + sql_patri69 + 'OR' + sql_patri73 + 'OR' + sql_patri74 + 'OR' + sql_patrimonialite
|
|
|
|
while reqwhere_pat.find('OROR') != -1 :
|
|
reqwhere_pat = reqwhere_pat.replace('OROR','OR')
|
|
while reqwhere_pat[-2:] == 'OR' :
|
|
reqwhere_pat = reqwhere_pat[:-2]
|
|
while reqwhere_pat[:2] == 'OR' :
|
|
reqwhere_pat = reqwhere_pat[2:]
|
|
|
|
# Requete geom
|
|
if self.dlg.bouton_geom.isChecked() == 1 :
|
|
|
|
layer = self.iface.activeLayer()
|
|
if layer == None :
|
|
self.iface.messageBar().pushMessage(u"Vous devez sélectionner une table !", level=QgsMessageBar.WARNING, duration=5)
|
|
|
|
else :
|
|
selection = layer.selectedFeatures()
|
|
if layer.selectedFeatureCount() == 1 :
|
|
for feature in selection:
|
|
geom = feature.geometry()
|
|
poly = geom.exportToWkt()
|
|
|
|
buffer = self.dlg.buffer.text()
|
|
reqwhere_geom = " ST_intersects(geometrie,ST_Buffer(ST_geomFromText('" + poly + "',2154), " + buffer + " ))"
|
|
|
|
# Requete finale
|
|
reqwhere_final = '(' + reqwhere_obs + ')' + 'AND' + reqwhere_insee + 'AND' + reqwhere_ordre + 'AND' + reqwhere_sp + 'AND' + '(' + reqwhere_pat + ')' + 'AND' + reqwhere_regne + 'AND' + reqwhere_date + 'AND' + reqwhere_geom
|
|
|
|
# Nettoyage de la requete finale
|
|
while reqwhere_final.find('ANDAND') != -1 :
|
|
reqwhere_final = reqwhere_final.replace('ANDAND','AND')
|
|
while reqwhere_final.find('AND()AND') != -1 :
|
|
reqwhere_final = reqwhere_final.replace('AND()AND','AND')
|
|
while reqwhere_final[-3:] == 'AND' :
|
|
reqwhere_final = reqwhere_final[:-3]
|
|
while reqwhere_final[:5] == '()AND' :
|
|
reqwhere_final = reqwhere_final[5:]
|
|
while reqwhere_final[-5:] == 'AND()' :
|
|
reqwhere_final = reqwhere_final[:-5]
|
|
while reqwhere_final[:3] == 'AND' :
|
|
reqwhere_final = reqwhere_final[3:]
|
|
|
|
## Affichage des tables
|
|
table_name = 'observations'
|
|
|
|
uri = QgsDataSourceURI()
|
|
uri.setConnection(host ,port ,dbname ,user ,password)
|
|
|
|
if self.dlg.centroide.isChecked() == 1 :
|
|
### Centroide
|
|
if reqwhere_final.find('()') != -1 :
|
|
reqwhere_final = ''
|
|
uri.setDataSource("_agregation_ra", "observations_centroide", "geometrie", reqwhere_final, "gid")
|
|
layer = self.iface.addVectorLayer(uri.uri(), table_name + '_centroides', "postgres")
|
|
|
|
else :
|
|
con = psycopg2.connect("dbname="+ dbname + " user=" + user + " host=" + host + " password=" + password + " port=" + port)
|
|
cur = con.cursor()
|
|
|
|
# Creation group de couches
|
|
i = qgis.utils.iface
|
|
legende = i.legendInterface()
|
|
nomgroupe = 'Observations'
|
|
groupe = legende.addGroup(nomgroupe,False)
|
|
|
|
# Récupération de la liste de groupes
|
|
toc = qgis.utils.iface.legendInterface()
|
|
groups = toc.groups()
|
|
index = groups.index(nomgroupe)
|
|
|
|
### Polygone
|
|
reqwhere_final_poly = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%POLYGON%'" + ')'
|
|
if reqwhere_final_poly.find('()') != -1 :
|
|
reqwhere_final_poly = "(GeometryType(geometrie) LIKE '%POLYGON%')"
|
|
|
|
SQL_count_poly = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_poly
|
|
cur.execute(SQL_count_poly)
|
|
list_count_poly = cur.fetchall()
|
|
count_poly = list_count_poly[0][0]
|
|
|
|
if count_poly != 0:
|
|
uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_poly, "gid")
|
|
layer_poly = self.iface.addVectorLayer(uri.uri(), table_name + '_poly', "postgres")
|
|
legende.moveLayer(layer_poly,index)
|
|
|
|
### Point
|
|
reqwhere_final_point = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%POINT%'" + ')'
|
|
if reqwhere_final_point.find('()') != -1 :
|
|
reqwhere_final_point = "(GeometryType(geometrie) LIKE '%POINT%')"
|
|
|
|
SQL_count_point = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_point
|
|
cur.execute(SQL_count_point)
|
|
list_count_point = cur.fetchall()
|
|
count_point = list_count_point[0][0]
|
|
|
|
if count_point != 0:
|
|
uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_point, "gid")
|
|
layer_point = self.iface.addVectorLayer(uri.uri(), table_name + '_point', "postgres")
|
|
legende.moveLayer(layer_point,index)
|
|
|
|
### Ligne
|
|
reqwhere_final_ligne = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%LINE%'" + ')'
|
|
if reqwhere_final_ligne.find('()') != -1 :
|
|
reqwhere_final_ligne = "(GeometryType(geometrie) LIKE '%LINE%')"
|
|
|
|
SQL_count_ligne = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_ligne
|
|
cur.execute(SQL_count_ligne)
|
|
list_count_ligne = cur.fetchall()
|
|
count_ligne = list_count_ligne[0][0]
|
|
|
|
if count_ligne != 0:
|
|
uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_ligne, "gid")
|
|
layer_ligne = self.iface.addVectorLayer(uri.uri(), table_name + '_ligne', "postgres")
|
|
legende.moveLayer(layer_ligne,index)
|
|
|
|
# ajout des couches dans le groupe Observations
|
|
legende.setGroupExpanded (index, True)
|
|
|
|
self.iface.messageBar().pushMessage(u"Extraction réussie : ", u" Pensez à renommer vos couches pour plus de lisibilité.", level=QgsMessageBar.INFO, duration=10)
|
|
|
|
|
|
elif layer.selectedFeatureCount() == 0 :
|
|
self.iface.messageBar().pushMessage(u"Vous devez sélectionner au moins un polygone !", level=QgsMessageBar.WARNING, duration=5)
|
|
reqwhere_geom = ''
|
|
|
|
else :
|
|
self.iface.messageBar().pushMessage(u"Vous devez sélectionner qu'un seul polygone !", level=QgsMessageBar.WARNING, duration=5)
|
|
reqwhere_geom = ''
|
|
else :
|
|
# Requete finale
|
|
reqwhere_final = '(' + reqwhere_obs + ')' + 'AND' + reqwhere_insee + 'AND' + reqwhere_ordre + 'AND' + reqwhere_sp + 'AND' + '(' + reqwhere_pat + ')' + 'AND' + reqwhere_regne + 'AND' + reqwhere_date
|
|
|
|
# Nettoyage de la requete finale
|
|
while reqwhere_final.find('ANDAND') != -1 :
|
|
reqwhere_final = reqwhere_final.replace('ANDAND','AND')
|
|
while reqwhere_final.find('AND()AND') != -1 :
|
|
reqwhere_final = reqwhere_final.replace('AND()AND','AND')
|
|
while reqwhere_final[-3:] == 'AND' :
|
|
reqwhere_final = reqwhere_final[:-3]
|
|
while reqwhere_final[:5] == '()AND' :
|
|
reqwhere_final = reqwhere_final[5:]
|
|
while reqwhere_final[-5:] == 'AND()' :
|
|
reqwhere_final = reqwhere_final[:-5]
|
|
while reqwhere_final[:3] == 'AND' :
|
|
reqwhere_final = reqwhere_final[3:]
|
|
|
|
## Affichage des tables
|
|
table_name = 'observations'
|
|
|
|
uri = QgsDataSourceURI()
|
|
uri.setConnection(host ,port ,dbname ,user ,password)
|
|
|
|
## Centroide
|
|
if self.dlg.centroide.isChecked() == 1 :
|
|
if reqwhere_final.find('()') != -1 :
|
|
reqwhere_final = ''
|
|
uri.setDataSource("_agregation_ra", "observations_centroide", "geometrie", reqwhere_final, "gid")
|
|
layer = self.iface.addVectorLayer(uri.uri(), table_name + '_centroides', "postgres")
|
|
|
|
else :
|
|
con = psycopg2.connect("dbname="+ dbname + " user=" + user + " host=" + host + " password=" + password + " port=" + port)
|
|
cur = con.cursor()
|
|
|
|
# Creation group de couches
|
|
i = qgis.utils.iface
|
|
legende = i.legendInterface()
|
|
nomgroupe = 'Observations'
|
|
groupe = legende.addGroup(nomgroupe,False)
|
|
|
|
# Récupération de la liste de groupes
|
|
toc = qgis.utils.iface.legendInterface()
|
|
groups = toc.groups()
|
|
index = groups.index(nomgroupe)
|
|
|
|
### Polygone
|
|
reqwhere_final_poly = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%POLYGON%'" + ')'
|
|
if reqwhere_final_poly.find('()') != -1 :
|
|
reqwhere_final_poly = "(GeometryType(geometrie) LIKE '%POLYGON%')"
|
|
|
|
SQL_count_poly = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_poly
|
|
cur.execute(SQL_count_poly)
|
|
list_count_poly = cur.fetchall()
|
|
count_poly = list_count_poly[0][0]
|
|
|
|
if count_poly != 0:
|
|
uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_poly, "gid")
|
|
layer_poly = self.iface.addVectorLayer(uri.uri(), table_name + '_poly', "postgres")
|
|
legende.moveLayer(layer_poly,index)
|
|
|
|
### Point
|
|
reqwhere_final_point = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%POINT%'" + ')'
|
|
if reqwhere_final_point.find('()') != -1 :
|
|
reqwhere_final_point = "(GeometryType(geometrie) LIKE '%POINT%')"
|
|
|
|
SQL_count_point = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_point
|
|
cur.execute(SQL_count_point)
|
|
list_count_point = cur.fetchall()
|
|
count_point = list_count_point[0][0]
|
|
|
|
if count_point != 0:
|
|
uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_point, "gid")
|
|
layer_point = self.iface.addVectorLayer(uri.uri(), table_name + '_point', "postgres")
|
|
legende.moveLayer(layer_point,index)
|
|
|
|
### Ligne
|
|
reqwhere_final_ligne = '(' + reqwhere_final + " AND GeometryType(geometrie) LIKE '%LINE%'" + ')'
|
|
if reqwhere_final_ligne.find('()') != -1 :
|
|
reqwhere_final_ligne = "(GeometryType(geometrie) LIKE '%LINE%')"
|
|
|
|
SQL_count_ligne = u"""SELECT DISTINCT count(gid) AS count FROM _agregation_ra.observations_table WHERE """ + reqwhere_final_ligne
|
|
cur.execute(SQL_count_ligne)
|
|
list_count_ligne = cur.fetchall()
|
|
count_ligne = list_count_ligne[0][0]
|
|
|
|
if count_ligne != 0:
|
|
uri.setDataSource("_agregation_ra", "observations_table", "geometrie", reqwhere_final_ligne, "gid")
|
|
layer_ligne = self.iface.addVectorLayer(uri.uri(), table_name + '_ligne', "postgres")
|
|
legende.moveLayer(layer_ligne,index)
|
|
|
|
# ajout des couches dans le groupe Observations
|
|
legende.setGroupExpanded (index, True)
|
|
|
|
self.iface.messageBar().pushMessage(u"Extraction réussie : ", u" Pensez à renommer vos couches pour plus de lisibilité.", level=QgsMessageBar.INFO, duration=10)
|
|
|
|
pass
|
|
|
|
def export(self):
|
|
|
|
### config.txt
|
|
config = "//100.100.100.100/bd_sig/z_QGIS/config.txt" # Chemin du fichier config
|
|
|
|
# Fonction de lecture des lignes du fichier config
|
|
def readline(n):
|
|
with open(config, "r") as f:
|
|
for lineno, line in enumerate(f):
|
|
if lineno == n:
|
|
return line.strip() # Permet d'enlever les retours chariots
|
|
|
|
# Recuperation des donnees
|
|
host = readline(10)
|
|
port = readline(12)
|
|
dbname = readline(14)
|
|
user = readline(16)
|
|
password = readline(18)
|
|
|
|
layer = self.iface.activeLayer()
|
|
|
|
if layer == None :
|
|
self.iface.messageBar().pushMessage(u"Vous devez sélectionner une table !", level=QgsMessageBar.WARNING, duration=5)
|
|
|
|
else :
|
|
selection = layer.selectedFeatures()
|
|
if (layer.selectedFeatureCount() == 1) :
|
|
for feature in selection:
|
|
geom = feature.geometry()
|
|
poly = geom.exportToWkt()
|
|
|
|
buffer = '100'
|
|
|
|
con = psycopg2.connect("dbname="+ dbname + " user=" + user + " host=" + host + " password=" + password + " port=" + port)
|
|
cur = con.cursor()
|
|
|
|
SQL_list_sp = u"""SELECT DISTINCT classe::text, ordre::text, nom_complet::text, nom_vern::text, max(date_obs)::text AS derniere_obs FROM _agregation_ra.observations_table WHERE ST_intersects(geometrie,ST_Buffer(ST_geomFromText('""" + poly + "',2154), " + buffer + " )) GROUP BY classe, ordre, nom_complet, nom_vern ORDER BY nom_vern"""
|
|
cur.execute(SQL_list_sp)
|
|
data_sp = cur.fetchall()
|
|
|
|
chemin_fichier = QFileDialog.getSaveFileName(None, 'Enregistrer sous...', "C:\Users\\" + str(os.environ.get("USERNAME")) + '\Desktop\liste_sp.csv', "Fichiers CSV (*.csv)")
|
|
file = open(chemin_fichier, 'wb')
|
|
file.write(u'\ufeff'.encode('utf8')) # BOM (optionel...Permet a Excel d'ouvrir proprement le fichier en UTF-8)
|
|
writer = csv.writer(file, delimiter = ';') # délimiteur ';' pour faciliter l'ouverture avec Excel
|
|
writer.writerow(['Classe', 'Ordre', 'Nom Complet', 'Nom Vernaculaire', u'Date Derniere Observation']) # Création des entêtes
|
|
|
|
for row in data_sp : # Boucle d'écriture ligne par ligne dans le csv
|
|
list_sp = []
|
|
if row[0] == None : # Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement
|
|
row0 = str(row[0])
|
|
row0 = ''
|
|
else :
|
|
row0 = row[0]
|
|
list_sp.append(row0.encode('utf-8'))
|
|
if row[1] == None : # Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement
|
|
row1 = str(row[1])
|
|
row1 = ''
|
|
else :
|
|
row1 = row[1]
|
|
list_sp.append(row1.encode('utf-8'))
|
|
if row[2] == None : # Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement
|
|
row2 = str(row[2])
|
|
row2 = ''
|
|
else :
|
|
row2 = row[2]
|
|
list_sp.append(row2.encode('utf-8'))
|
|
if row[3] == None : # Si la valeur est 'None' l'encodage ne peut se faire donc boucle d'évitement
|
|
row3 = str(row[3])
|
|
row3 = ''
|
|
else :
|
|
row3 = row[3]
|
|
list_sp.append(row3.encode('utf-8'))
|
|
if row[4] == None :
|
|
row4 = str(row[4])
|
|
row4 = ''
|
|
else :
|
|
row4 = row[4]
|
|
list_sp.append(row4.encode('utf-8'))
|
|
writer.writerow(list_sp)
|
|
|
|
file.close()
|
|
self.iface.messageBar().pushMessage(u"Export réussi dans " + chemin_fichier , level=QgsMessageBar.INFO, duration=5)
|
|
|
|
elif (layer.selectedFeatureCount() == 0) :
|
|
self.iface.messageBar().pushMessage(u"Vous devez sélectionner au moins un polygone !", level=QgsMessageBar.WARNING, duration=5)
|
|
|
|
else :
|
|
self.iface.messageBar().pushMessage(u"Vous devez sélectionner qu'un seul polygone !", level=QgsMessageBar.WARNING, duration=5) |