# -*- coding: utf-8 -*- """ /*************************************************************************** Cadastre - import main methods A QGIS plugin This plugins helps users to import the french land registry ('cadastre') into a database. It is meant to ease the use of the data in QGIs by providing search tools and appropriate layer symbology. ------------------- begin : 2013-06-11 copyright : (C) 2013 by 3liz email : info@3liz.com ***************************************************************************/ /*************************************************************************** * * * 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 os, glob import StringIO import string, sys import re import time import tempfile import shutil from distutils import dir_util from PyQt4.QtCore import * from PyQt4.QtGui import * from qgis.core import * from datetime import datetime # db_manager scripts from db_manager.db_plugins.plugin import DBPlugin, Schema, Table, BaseError from db_manager.db_plugins import createDbPlugin from db_manager.dlg_db_error import DlgDbError from pyspatialite import dbapi2 as sqlite # Import ogr2ogr.py from processing plugin try: from processing.algs.gdal.pyogr.ogr2ogr import main as ogr2ogr except ImportError: pass try: from processing.gdal.pyogr.ogr2ogr import main as ogr2ogr except ImportError: pass try: from scripts.pyogr.ogr2ogr import main as ogr2ogr except ImportError: pass from scripts.pyogr.ogr2ogr import main as ogr2ogr from cadastre_dialogs import cadastre_common class cadastreImport(QObject): def __init__(self, dialog): self.dialog = dialog # common cadastre methods self.qc = self.dialog.qc self.db = self.dialog.db self.connector = self.db.connector self.pScriptSourceDir = os.path.join(self.qc.plugin_dir, 'scripts/plugin') # projections if self.dialog.doEdigeoImport: self.sourceSridFull = self.dialog.edigeoSourceProj self.targetSridFull = self.dialog.edigeoTargetProj self.sourceSrid = self.sourceSridFull.split(":")[1] self.targetSrid = self.targetSridFull.split(":")[1] else: self.targetSrid = '2154' # create temporary directories s = QSettings() tempDir = s.value("cadastre/tempDir", '%s' % tempfile.gettempdir(), type=str) self.pScriptDir = tempfile.mkdtemp('', 'cad_p_script_', tempDir) self.edigeoPlainDir = tempfile.mkdtemp('', 'cad_edigeo_plain_', tempDir) self.replaceDict = { '[VERSION]' : self.dialog.dataVersion, '[ANNEE]' : self.dialog.dataYear, '[LOT]' : self.dialog.edigeoLot } self.maxInsertRows = s.value("cadastre/maxInsertRows", 50000, type=int) self.spatialiteTempStore = s.value("cadastre/spatialiteTempStore", 'MEMORY', type=str) self.geoTableList = ['geo_zoncommuni', 'geo_ptcanv', 'geo_commune', 'geo_parcelle', 'geo_symblim', 'geo_tronfluv', 'geo_tronroute', 'geo_label', 'geo_subdsect', 'geo_batiment', 'geo_borne', 'geo_croix', 'geo_tpoint', 'geo_lieudit', 'geo_section', 'geo_subdfisc', 'geo_tsurf', 'geo_tline', 'geo_unite_fonciere'] s = QSettings() self.majicSourceFileNames = [ {'key': '[FICHIER_BATI]', 'value': s.value("cadastre/batiFileName", 'REVBATI.800', type=str), 'table': 'bati', 'required': True }, {'key': '[FICHIER_FANTOIR]', 'value': s.value("cadastre/fantoirFileName", 'TOPFANR.800', type=str), 'table': 'fanr', 'required': True }, {'key': '[FICHIER_LOTLOCAL]', 'value': s.value("cadastre/lotlocalFileName", 'REVD166.800', type=str), 'table': 'lloc', 'required': False }, {'key': '[FICHIER_NBATI]', 'value': s.value("cadastre/nbatiFileName", 'REVNBAT.800', type=str), 'table': 'nbat', 'required': True }, {'key': '[FICHIER_PDL]', 'value': s.value("cadastre/pdlFileName", 'REVFPDL.800', type=str), 'table': 'pdll', 'required': False }, {'key': '[FICHIER_PROP]', 'value': s.value("cadastre/propFileName", 'REVPROP.800', type=str), 'table': 'prop', 'required': True } ] if self.dialog.dbType == 'postgis': self.replaceDict['[PREFIXE]'] = '"%s".' % self.dialog.schema else: self.replaceDict['[PREFIXE]'] = '' self.go = True self.startTime = datetime.now() self.step = 0 self.totalSteps = 0 self.multiPolygonUpdated = 0 self.qc.checkDatabaseForExistingStructure() self.hasConstraints = False if self.dialog.hasStructure: self.hasConstraints = True # Remove MAJIC from tables bati|fanr|lloc|nbat|pdll|prop self.removeMajicRawData = True self.beginImport() def beginJobLog(self, stepNumber, title): ''' reinit progress bar ''' self.totalSteps = stepNumber self.step = 0 self.dialog.stepLabel.setText('%s' % title) self.qc.updateLog('

%s

' % title) def updateProgressBar(self): ''' Update the progress bar ''' if self.go: self.step+=1 self.dialog.pbProcess.setValue(int(self.step * 100/self.totalSteps)) def updateTimer(self): ''' Update the timer for each process ''' if self.go: b = datetime.now() diff = b - self.startTime self.qc.updateLog(u'%s s' % diff.seconds) def beginImport(self): ''' Process to run before importing data ''' # Log jobTitle = u'INITIALISATION' self.beginJobLog(2, jobTitle) # Set postgresql synchronous_commit to off # to speed up bulk inserts if self.dialog.dbType == 'postgis': sql = "SET LOCAL synchronous_commit TO off;" if self.dialog.dbType == 'spatialite': sql = 'PRAGMA synchronous = OFF;PRAGMA journal_mode = OFF;PRAGMA temp_store = %s;PRAGMA cache_size = 500000' % self.spatialiteTempStore self.executeSqlQuery(sql) # copy SQL script files to temporary dir self.updateProgressBar() self.copyFilesToTemp(self.pScriptSourceDir, self.pScriptDir) self.updateTimer() self.updateProgressBar() def installCadastreStructure(self): ''' Create the empty db structure ''' if not self.go: return False # Log jobTitle = u'STRUCTURATION BDD' self.beginJobLog(6, jobTitle) # Replace dictionnary replaceDict = self.replaceDict.copy() replaceDict['2154'] = self.targetSrid # Suppression des éventuelles tables edigeo import # laissées suite à bug par exemple self.dropEdigeoRawData() # install cadastre structure scriptList = [ { 'title' : u'Création des tables', 'script': '%s' % os.path.join(self.pScriptDir, 'commun_create_metier.sql') }, { 'title': u'Création des tables edigeo', 'script': '%s' % os.path.join(self.pScriptDir, 'edigeo_create_import_tables.sql') }, { 'title' : u'Ajout de la nomenclature', 'script': '%s' % os.path.join(self.pScriptDir, 'commun_insert_nomenclatures.sql') } ] for item in scriptList: if self.go: s = item['script'] self.dialog.subStepLabel.setText(item['title']) self.qc.updateLog('%s' % item['title']) self.updateProgressBar() self.replaceParametersInScript(s, replaceDict) self.executeSqlScript(s, item.has_key('constraints')) if item.has_key('constraints'): self.hasConstraints = item['constraints'] self.updateProgressBar() self.updateTimer() def updateCadastreStructure(self): ''' Add some tables if they do not exists This method is run only if structure already exists and if each table is not already present ''' # List all the tables which have been created between plugin versions newTables = [ 'geo_tronroute', 'commune_majic' ] # Replace dictionnary replaceDict = self.replaceDict.copy() replaceDict['2154'] = self.targetSrid # Run the table creation scripts for table in newTables: # Check if table already exists if self.qc.checkDatabaseForExistingTable(table, self.dialog.schema): continue # Build path the the SQL creation file and continue if it does not exists s = '%s' % os.path.join(self.pScriptDir, 'update/create_%s.sql' % table) if not os.path.exists(s): continue self.replaceParametersInScript(s, replaceDict) self.executeSqlScript(s, False) def importMajic(self): # Log jobTitle = u'MAJIC' self.beginJobLog(13, jobTitle) # dict for parameters replacement replaceDict = self.replaceDict.copy() mandatoryFilesKeys = ['[FICHIER_BATI]', '[FICHIER_FANTOIR]', '[FICHIER_NBATI]', '[FICHIER_PROP]'] missingMajicFiles = False scriptList = [] scriptList.append( { 'title' : u'Suppression des contraintes', 'script' : os.path.join(self.pScriptDir, 'commun_suppression_contraintes.sql'), 'constraints': False, 'divide': True } ) # Remove previous data if self.dialog.hasMajicData: scriptList.append( { 'title' : u'Purge des données MAJIC', 'script' : os.path.join(self.pScriptDir, 'majic3_purge_donnees.sql') } ) scriptList.append( { 'title' : u'Purge des données brutes', 'script' : os.path.join(self.pScriptDir, 'majic3_purge_donnees_brutes.sql') } ) # Remove indexes scriptList.append( { 'title' : u'Suppression des indexes', 'script' : os.path.join(self.pScriptDir, 'majic3_drop_indexes.sql') } ) # Import MAJIC files into database # No use of COPY FROM to allow import into distant databases importScript = { 'title' : u'Import des fichiers majic', 'method' : self.importMajicIntoDatabase } scriptList.append(importScript) # Format data scriptList.append( { 'title' : u'Mise en forme des données', 'script' : os.path.join(self.pScriptDir, '%s/majic3_formatage_donnees.sql' % self.dialog.dataVersion), 'divide': True } ) # Remove MAJIC raw data if self.removeMajicRawData: scriptList.append( { 'title' : u'Purge des données brutes', 'script' : os.path.join(self.pScriptDir, 'majic3_purge_donnees_brutes.sql') } ) # If MAJIC but no EDIGEO afterward # run SQL script to update link between EDI/MAJ if not self.dialog.doEdigeoImport: replaceDict['[DEPDIR]'] = '%s%s' % (self.dialog.edigeoDepartement, self.dialog.edigeoDirection) scriptList.append( { 'title' : u'Suppression des indexes', 'script' : os.path.join(self.pScriptDir, 'edigeo_drop_indexes.sql') } ) scriptList.append( { 'title' : u'Mise à jour des liens EDIGEO', 'script' : os.path.join(self.pScriptDir, 'edigeo_update_majic_link.sql'), 'divide': True } ) scriptList.append( { 'title' : u'Création des indexes spatiaux', 'script' : os.path.join(self.pScriptDir, 'edigeo_create_indexes.sql'), 'divide': True } ) # Ajout de la table parcelle_info replaceDict['2154'] = self.targetSrid scriptList.append( { 'title' : u'Ajout de la table parcelle_info', 'script' : '%s' % os.path.join(self.pScriptDir, 'edigeo_create_table_parcelle_info_majic.sql'), 'divide': False } ) # Add constraints scriptList.append( { 'title' : u'Ajout des contraintes', 'script' : os.path.join(self.pScriptDir, 'commun_creation_contraintes.sql'), 'constraints': True, 'divide': True } ) # Run previously defined SQL queries for item in scriptList: if self.go: self.dialog.subStepLabel.setText(item['title']) self.qc.updateLog('%s' % item['title']) if item.has_key('script'): s = item['script'] self.replaceParametersInScript(s, replaceDict) self.updateProgressBar() if item.has_key('divide'): self.executeSqlScript(s, True, item.has_key('constraints')) else: self.executeSqlScript(s, False, item.has_key('constraints')) else: self.updateProgressBar() item['method']() if item.has_key('constraints') \ and not self.dialog.dbType == 'spatialite': self.hasConstraints = item['constraints'] self.updateTimer() self.updateProgressBar() return None def chunk(self, iterable, n=100000, padvalue=None): ''' Chunks an iterable (file, etc.) into pieces ''' from itertools import izip_longest return izip_longest(*[iter(iterable)]*n, fillvalue=padvalue) def importMajicIntoDatabase(self): ''' Method wich read each majic file and bulk import data intp temp tables Returns False if no file processed ''' processedFilesCount = 0 majicFilesKey = [] majicFilesFound = {} # Regex to remove all chars not in the range in ASCII table from space to ~ # http://www.catonmat.net/blog/my-favorite-regex/ r = re.compile(r"[^ -~]") # Loop through all majic files # 1st path to build the complet liste for each majic source type (nbat, bati, lloc, etc.) # and read 1st line to get departement and direction to compare to inputs depdirs = {} for item in self.majicSourceFileNames: table = item['table'] value = item['value'] # Get majic files for item majList = [] for root, dirs, files in os.walk(self.dialog.majicSourceDir): for i in files: if os.path.split(i)[1] == value: fpath = os.path.join(root, i) # Add file path to the list majList.append(fpath) # Store depdir for this file # avoid fantoir, as now it is given for the whole country if table == 'fanr': continue # Get depdir : first line with content with open(fpath) as fin: for a in fin: if len( a ) < 4 : continue depdir = a[0:3] break depdirs[depdir] = True majicFilesFound[table] = majList # Check if some important majic files are missing fKeys = [ a for a in majicFilesFound if majicFilesFound[a] ] rKeys = [ a['table'] for a in self.majicSourceFileNames if a['required'] ] mKeys = [ a for a in rKeys if a not in fKeys ] if mKeys: msg = u"Des fichiers MAJIC importants sont manquants: %s
Vérifier le chemin des fichiers MAJIC:
%s
ainsi que les noms des fichiers configurés dans les options du plugin Cadastre:
%s

Vous pouvez télécharger les fichiers fantoirs à cette adresse :
http://www.collectivites-locales.gouv.fr/mise-a-disposition-fichier-fantoir-des-voies-et-lieux-dits
" % ( ', '.join(mKeys), self.dialog.majicSourceDir, ', '.join([a['value'].upper() for a in self.majicSourceFileNames]) ) missingMajicIgnore = QMessageBox.question( self.dialog, u'Cadastre', msg + '\n\n' + u"Voulez-vous néanmoins continuer l'import ?", QMessageBox.Yes | QMessageBox.No, QMessageBox.No ) if missingMajicIgnore != QMessageBox.Yes: self.go = False self.qc.updateLog(msg) return False # Check if departement and direction are the same for every file if len(depdirs.keys()) > 1: self.go = False lst = ",
".join( u"département : %s et direction : %s" % (a[0:2], a[2:3]) for a in depdirs) self.qc.updateLog( u"ERREUR : MAJIC - Les données concernent des départements et codes direction différents :\n
%s" % lst ) self.qc.updateLog(u"Veuillez réaliser l'import en %s fois." % len( depdirs.keys() ) ) return False # Check if departement and direction are different from those given by the user in dialog fDep = depdirs.keys()[0][0:2] fDir = depdirs.keys()[0][2:3] if self.dialog.edigeoDepartement != fDep or self.dialog.edigeoDirection != fDir: msg = u"ERREUR : MAJIC - Les numéros de département et de direction trouvés dans les fichiers ne correspondent pas à ceux renseignés dans les options du dialogue d'import:\n
* fichiers : %s et %s
* options : %s et %s" % ( fDep, fDir, self.dialog.edigeoDepartement, self.dialog.edigeoDirection ) useFileDepDir = QMessageBox.question( self.dialog, u'Cadastre', msg + '\n\n' + u"

Voulez-vous continuer l'import avec les numéros trouvés dans les fichiers ?", QMessageBox.Yes | QMessageBox.No, QMessageBox.No ) if useFileDepDir == QMessageBox.Yes: self.dialog.edigeoDepartement = fDep self.dialog.inEdigeoDepartement.setText(fDep) self.dialog.edigeoDirection = fDir self.dialog.inEdigeoDirection.setValue(int(fDir)) else: self.go = False self.qc.updateLog(msg) return False # 2nd path to insert data depdir = '%s%s' % (self.dialog.edigeoDepartement, self.dialog.edigeoDirection) for item in self.majicSourceFileNames: table = item['table'] self.totalSteps+= len(majicFilesFound[table]) processedFilesCount+=len(majicFilesFound[table]) for fpath in majicFilesFound[table]: self.qc.updateLog(fpath) # read file content with open(fpath) as fin: # Divide file into chuncks for a in self.chunk(fin, self.maxInsertRows): # Build sql INSERT query depending on database if self.dialog.dbType == 'postgis': sql = "BEGIN;" sql = cadastre_common.setSearchPath(sql, self.dialog.schema) # Build INSERT list sql+= '\n'.join( [ "INSERT INTO \"%s\" VALUES (%s);" % ( table, self.connector.quoteString( r.sub(' ', x.strip('\r\n')) ) ) for x in a if x and x[0:3] == depdir ] ) sql+= "COMMIT;" self.executeSqlQuery(sql) else: c = self.connector._get_cursor() c.executemany('INSERT INTO %s VALUES (?)' % table, [( r.sub(' ', x.strip('\r\n')) ,) for x in a if x and x[0:3] == depdir] ) self.connector._commit() c.close() del c if not processedFilesCount: self.qc.updateLog( u"ERREUR : MAJIC - aucun fichier trouvé. Vérifier les noms de fichiers dans les paramètres du plugin et que le répertoire '%s' contient bien des fichiers qui correspondent\n : %s" % ( self.dialog.majicSourceDir, ', '.join( majicFilesKey ) ) ) self.go = False def importEdigeo(self): ''' Import EDIGEO data into database ''' if not self.go: return False # Log : Print connection parameters to database jobTitle = u'EDIGEO' self.beginJobLog(21, jobTitle) self.qc.updateLog(u'Type de base : %s, Connexion: %s, Schéma: %s' % ( self.dialog.dbType, self.dialog.connectionName, self.dialog.schema ) ) self.updateProgressBar() if self.go: # unzip edigeo files in temp dir self.dialog.subStepLabel.setText('Extraction des fichiers') self.updateProgressBar() self.unzipFolderContent(self.dialog.edigeoSourceDir) self.updateTimer() self.updateProgressBar() scriptList = [] replaceDict = self.replaceDict.copy() # Add geo_unite_foncieres if needed if not self.qc.checkDatabaseForExistingTable('geo_unite_fonciere', self.dialog.schema) \ and self.dialog.dbType == 'postgis': scriptList.append( { 'title' : u'Ajout de la table geo_unite_foncieres', 'script' : '%s' % os.path.join(self.pScriptDir, 'edigeo_create_table_unite_fonciere.sql'), 'constraints': False } ) # Drop constraints scriptList.append( { 'title' : u'Suppression des contraintes', 'script' : '%s' % os.path.join(self.pScriptDir, 'commun_suppression_contraintes.sql'), 'constraints': False, 'divide' : True } ) # Suppression et recréation des tables edigeo pour import if self.dialog.hasData: replaceDict['2154'] = self.targetSrid # Drop edigeo data self.dropEdigeoRawData() scriptList.append( { 'title': u'Création des tables edigeo', 'script': '%s' % os.path.join(self.pScriptDir, 'edigeo_create_import_tables.sql') } ) # Suppression des indexes if self.dialog.hasData: scriptList.append( { 'title' : u'Suppression des indexes', 'script' : '%s' % os.path.join(self.pScriptDir, 'edigeo_drop_indexes.sql') } ) for item in scriptList: if self.go: self.dialog.subStepLabel.setText(item['title']) self.qc.updateLog('%s' % item['title']) s = item['script'] self.replaceParametersInScript(s, replaceDict) self.updateProgressBar() self.executeSqlScript(s, item.has_key('divide'), item.has_key('constraints')) if item.has_key('constraints'): self.hasConstraints = item['constraints'] self.updateTimer() self.updateProgressBar() # import edigeo *.thf and *.vec files into database if self.go: self.dialog.subStepLabel.setText('Import des fichiers') self.updateProgressBar() self.importAllEdigeoToDatabase() self.updateTimer() self.updateProgressBar() # Format edigeo data replaceDict = self.replaceDict.copy() replaceDict['[DEPDIR]'] = '%s%s' % (self.dialog.edigeoDepartement, self.dialog.edigeoDirection) scriptList = [] scriptList.append( { 'title' : u'Mise en forme des données', 'script' : os.path.join(self.pScriptDir, 'edigeo_formatage_donnees.sql'), 'divide': True } ) scriptList.append( { 'title' : u'Placement des étiquettes', 'script' : os.path.join(self.pScriptDir, 'edigeo_add_labels_xy.sql') } ) scriptList.append( { 'title' : u'Création des indexes spatiaux', 'script' : os.path.join(self.pScriptDir, 'edigeo_create_indexes.sql' ), 'divide': True } ) scriptList.append( { 'title' : u'Ajout des contraintes', 'script' : os.path.join(self.pScriptDir, 'commun_creation_contraintes.sql' ), 'constraints': True, 'divide': True } ) # ajout des unités foncières # seulement si on a des données MAJIC de propriétaire self.qc.checkDatabaseForExistingStructure() if ( self.dialog.doMajicImport or self.dialog.hasMajicDataProp ) \ and self.dialog.dbType == 'postgis': scriptList.append( { 'title' : u'Création Unités foncières', 'script' : os.path.join( self.pScriptDir, 'edigeo_unites_foncieres_%s.sql' % self.dialog.dbType) } ) # Ajout de la table parcelle_info if ( self.dialog.doMajicImport or self.dialog.hasMajicDataProp ): replaceDict['2154'] = self.targetSrid scriptList.append( { 'title' : u'Ajout de la table parcelle_info', 'script' : '%s' % os.path.join(self.pScriptDir, 'edigeo_create_table_parcelle_info_majic.sql') } ) else: replaceDict['2154'] = self.targetSrid scriptList.append( { 'title' : u'Ajout de la table parcelle_info', 'script' : '%s' % os.path.join(self.pScriptDir, 'edigeo_create_table_parcelle_info_simple.sql') } ) for item in scriptList: if self.go: self.dialog.subStepLabel.setText(item['title']) self.qc.updateLog('%s' % item['title']) s = item['script'] self.replaceParametersInScript(s, replaceDict) self.updateProgressBar() self.executeSqlScript(s, item.has_key('divide'), item.has_key('constraints')) if item.has_key('constraints'): self.hasConstraints = item['constraints'] self.updateTimer() self.updateProgressBar() # drop edigeo raw data self.dialog.subStepLabel.setText('Suppression des fichiers temporaires') self.dropEdigeoRawData() self.updateTimer() self.updateProgressBar() return None def endImport(self): ''' Actions done when import has finished ''' # Log jobTitle = u'FINALISATION' self.beginJobLog(1, jobTitle) # Debug spatialite if self.dialog.dbType == 'spatialite': sql = "SELECT RecoverGeometryColumn( 'parcelle_info', 'geom', %s, 'MULTIPOLYGON', 2 );" % self.targetSrid self.executeSqlQuery(sql) # Re-set SQL optimization parameters to default if self.dialog.dbType == 'postgis': sql = "SET LOCAL synchronous_commit TO on;" self.executeSqlQuery(sql) else: sql = 'PRAGMA journal_mode = MEMORY;' self.executeSqlQuery(sql) # Remove the temp folders self.dialog.subStepLabel.setText(u'Suppression des données temporaires') self.updateProgressBar() tempFolderList = [ self.pScriptDir, self.edigeoPlainDir, ] delmsg = "" try: for rep in tempFolderList: if os.path.exists(rep): shutil.rmtree(rep) rmt = 1 except IOError, e: delmsg = u"Erreur lors de la suppression des répertoires temporaires: %s" % e self.qc.updateLog(delmsg) self.go = False # Delete labels outside commune bbox if self.dialog.dbType == 'spatialite': sql = 'DELETE FROM geo_label WHERE NOT MbrWithin(geom, ( SELECT ST_Buffer(ST_Envelope(Collect(geom)), 100 ) AS geom FROM geo_commune ));' else: sql = 'DELETE FROM geo_label WHERE NOT ST_Within(geom, ( SELECT ST_Buffer(ST_Envelope(ST_Collect(geom)), 100 ) AS geom FROM geo_commune ));' sql = self.qc.setSearchPath(sql, self.dialog.schema) self.executeSqlQuery(sql) # Add parcelle_info index for postgis only (not capability of that type for spatialite) if self.dialog.dbType == 'postgis': sql = 'DROP INDEX IF EXISTS parcelle_info_geo_parcelle_sub;CREATE INDEX parcelle_info_geo_parcelle_sub ON parcelle_info( substr("geo_parcelle", 1, 10));' sql = self.qc.setSearchPath(sql, self.dialog.schema) self.executeSqlQuery(sql) # Refresh spatialite layer statistics if self.dialog.dbType == 'spatialite': sql = '' for layer in self.geoTableList: sql+= "SELECT UpdateLayerStatistics('%s', 'geom');" % layer self.executeSqlQuery(sql) if self.go: msg = u"Import terminé" else: msg = u"Des erreurs ont été rencontrées pendant l'import. Veuillez consulter le log." self.updateProgressBar() self.updateTimer() QMessageBox.information(self.dialog, "Cadastre", msg) return None # # TOOLS # def copyFilesToTemp(self, source, target): ''' Copy cadastre scripts into a temporary folder ''' if self.go: self.qc.updateLog(u'* Copie du répertoire %s' % source) QApplication.setOverrideCursor(Qt.WaitCursor) # copy script directory try: dir_util.copy_tree(source, target) os.chmod(target, 0777) except IOError, e: msg = u"Erreur lors de la copie des scripts d'import: %s" % e QMessageBox.information(self.dialog, "Cadastre", msg) self.go = False return msg finally: QApplication.restoreOverrideCursor() return None def listFilesInDirectory(self, path, extensionList=[], invert=False): ''' List all files from folder and subfolder for a specific extension if given ( via the list extensionList ). If invert is True, then get all files but those corresponding to the given extensions. ''' fileList = [] for root, dirs, files in os.walk(path): for i in files: if not invert: if os.path.splitext(i)[1][1:].lower() in extensionList: fileList.append(os.path.join(root, i)) else: if os.path.splitext(i)[1][1:].lower() not in extensionList: fileList.append(os.path.join(root, i)) return fileList def unzipFolderContent(self, path): ''' Scan content of specified path and unzip all content into a single folder ''' if self.go: QApplication.setOverrideCursor(Qt.WaitCursor) self.qc.updateLog(u'* Décompression des fichiers') # get all the zip files zipFileList = self.listFilesInDirectory(path, ['zip']) # unzip all files import zipfile import tarfile try: # unzip all zip in source folder for z in zipFileList: # Extract file from edigeoDir into edigeoPlainDir with zipfile.ZipFile(z) as azip: azip.extractall(self.edigeoPlainDir) # unzip all new zip in edigeoPlainDir inner_zips_pattern = os.path.join(self.edigeoPlainDir, "*.zip") i=0 for filename in glob.glob(inner_zips_pattern): inner_folder = filename[:-4] + '_%s' % i with zipfile.ZipFile(filename) as myzip: myzip.extractall(inner_folder) try: os.remove(filename) except OSError, e: self.qc.updateLog( "Erreur lors de la suppression de %s" % str(filename)) pass # in Windows, sometime file is not unlocked i+=1 i=0 # untar all tar.bz2 in source folder tarFileListA = self.listFilesInDirectory(path, ['bz2']) for z in tarFileListA: with tarfile.open(z) as t: tar = t.extractall(os.path.join(self.edigeoPlainDir, 'tar_%s' % i)) i+=1 t.close() # untar all new tar.bz2 found in self.edigeoPlainDir tarFileListB = self.listFilesInDirectory(self.edigeoPlainDir, ['bz2']) for z in tarFileListB: with tarfile.open(z) as t: tar = t.extractall(os.path.join(self.edigeoPlainDir, 'tar_%s' % i)) i+=1 t.close() try: os.remove(z) except OSError, e: self.qc.updateLog( "Erreur lors de la suppression de %s" % str(z)) pass # in Windows, sometime file is not unlocked except IOError, e: msg = u"Erreur lors de l'extraction des fichiers EDIGEO" self.go = False self.qc.updateLog(msg) return msg finally: QApplication.restoreOverrideCursor() def replaceParametersInString(self, string, replaceDict): ''' Replace all occurences in string ''' def replfunc(match): if replaceDict.has_key(match.group(0)): return replaceDict[match.group(0)] else: return None regex = re.compile('|'.join(re.escape(x) for x in replaceDict), re.IGNORECASE) string = regex.sub(replfunc, string) return string def replaceParametersInScript(self, scriptPath, replaceDict): ''' Replace all parameters in sql scripts with given values ''' if self.go: QApplication.setOverrideCursor(Qt.WaitCursor) try: fin = open(scriptPath) data = fin.read().decode("utf-8-sig") fin.close() fout = open(scriptPath, 'w') data = self.replaceParametersInString(data, replaceDict) data = data.encode('utf-8') fout.write(data) fout.close() except IOError, e: msg = u"Erreur lors du paramétrage des scripts d'import: %s" % e self.go = False self.qc.updateLog(msg) return msg finally: QApplication.restoreOverrideCursor() return None def executeSqlScript(self, scriptPath, divide=False, ignoreError=False): ''' Execute an SQL script file ''' if self.go: QApplication.setOverrideCursor(Qt.WaitCursor) # Read sql script sql = open(scriptPath).read() sql = sql.decode("utf-8-sig") # Set schema if needed if self.dialog.dbType == 'postgis': sql = self.qc.setSearchPath(sql, self.dialog.schema) # Remove make valid if asked if not self.dialog.edigeoMakeValid: mvReplaceDic = [ {'in': r"ST_CollectionExtract\(ST_MakeValid\(geom\),{2,3}\)", 'out': r"geom"}, {'in': r"ST_CollectionExtract\(ST_MakeValid\(p\.geom\),{2,3}\)", 'out': r"p.geom"} ] for a in mvReplaceDic: r = re.compile(a['in'], re.IGNORECASE|re.MULTILINE) sql = r.sub(a['out'], sql) # Convert SQL into spatialite syntax if self.dialog.dbType == 'spatialite': sql = cadastre_common.postgisToSpatialite(sql, self.targetSrid) sql = cadastre_common.postgisToSpatialiteLocal10(sql, self.dialog.dataYear) #~ self.qc.updateLog('|%s|' % sql) # Execute query if not divide: self.executeSqlQuery(sql, ignoreError) else: statements = sql.split(';') self.totalSteps+= len(statements) self.updateProgressBar() r = re.compile(r'select |insert |update |delete |alter |create |drop |truncate |comment |copy |vacuum |analyze ', re.IGNORECASE|re.MULTILINE) for sqla in statements: if not self.go: break cr = re.compile(r'-- (.+)', re.IGNORECASE|re.MULTILINE) ut = False for comment in cr.findall(sqla): self.qc.updateLog(' - %s' % comment.strip(' \n\r\t')) ut = True # Do nothing if sql is only comment if not r.search(sqla) or not len(sqla.split('~')) == 1: continue sql = '%s' % sqla #~ self.qc.updateLog('@@%s$$' % sql) self.updateProgressBar() # Avoid adding 2 times the same column for spatialite if self.dialog.dbType == 'spatialite' \ and re.search(r'ADD COLUMN tempo_import', sqla, re.IGNORECASE): try: self.executeSqlQuery(sql, ignoreError) except: pass else: self.executeSqlQuery(sql, ignoreError) if ut: self.updateTimer() self.updateProgressBar() QApplication.restoreOverrideCursor() return None def executeSqlQuery(self, sql, ignoreError=False): ''' Execute a SQL string query And commit NB: commit qgis/QGIS@14ab5eb changes QGIS DBmanager behaviour ''' if self.go: QApplication.setOverrideCursor(Qt.WaitCursor) c = None if self.dialog.dbType == 'postgis': try: c = self.connector._execute_and_commit(sql.encode('utf-8')) except BaseError as e: if not ignoreError \ and not re.search(r'ADD COLUMN tempo_import', sql, re.IGNORECASE) \ and not re.search(r'CREATE INDEX ', sql, re.IGNORECASE): DlgDbError.showError(e, self.dialog) self.go = False self.qc.updateLog(e.msg) except UnicodeDecodeError as e: try: c = self.connector._execute_and_commit(sql) except BaseError as e: if not ignoreError \ and not re.search(r'ADD COLUMN tempo_import', sql, re.IGNORECASE) \ and not re.search(r'CREATE INDEX ', sql, re.IGNORECASE): DlgDbError.showError(e, self.dialog) self.go = False self.qc.updateLog(e.msg) finally: QApplication.restoreOverrideCursor() if c: try: c.close() del c except: self.qc.updateLog("issue closing connection") print "issue closing connection" pass if self.dialog.dbType == 'spatialite': #~ self.qc.updateLog(sql) try: c = self.connector._get_cursor() c.executescript(sql) except (BaseError, sqlite.OperationalError) as e: if not re.search(r'ADD COLUMN tempo_import', sql, re.IGNORECASE) \ and not re.search(r'CREATE INDEX ', sql, re.IGNORECASE): self.go = False self.qc.updateLog(u"Erreurs rencontrées pour la requête:

%s

" % sql) finally: QApplication.restoreOverrideCursor() if c: try: c.close() del c except: self.qc.updateLog("issue closing connection") print "issue closing connection" pass def importAllEdigeoToDatabase(self): ''' Loop through all THF files and import each one into database ''' if self.go: self.qc.updateLog(u'* Import des fichiers EDIGEO dans la base') initialStep = self.step initialTotalSteps = self.totalSteps # THF self.dialog.subStepLabel.setText(u'Import des fichiers via ogr2ogr (*.thf)') self.qc.updateLog(u' - Import des fichiers via ogr2ogr') # Get plain files in source directory thfList1 = self.listFilesInDirectory(self.dialog.edigeoSourceDir, ['thf']) # Get files which have been uncompressed by plugin in temp folder thfList2 = self.listFilesInDirectory(self.edigeoPlainDir, ['thf']) thfList = list(set(thfList1) | set(thfList2)) self.step = 0 self.totalSteps = len(thfList) for thf in thfList: self.importEdigeoThfToDatabase(thf) self.updateProgressBar() if not self.go: break if self.go: # VEC - import relations between objects self.dialog.subStepLabel.setText(u'Import des relations (*.vec)') self.qc.updateLog(u' - Import des relations (*.vec)') # Get plain files in source directory vecList1 = self.listFilesInDirectory(self.dialog.edigeoSourceDir, ['vec']) # Get files which have been uncompressed by plugin in temp folder vecList2 = self.listFilesInDirectory(self.edigeoPlainDir, ['vec']) vecList = list(set(vecList1) | set(vecList2)) self.step = 0 self.totalSteps = len(vecList) for vec in vecList: # import via ogr2ogr self.importEdigeoVecToDatabase(vec) # update mission multipolygons (ogr2ogr driver does not handle them yet) self.updateMultipolygonFromVec(vec) self.updateProgressBar() if not self.go: break if self.go: self.qc.updateLog(u' - %s multipolygones mis à jours dans la base de données' % self.multiPolygonUpdated) # Reinit progress var self.step = initialStep self.totalSteps = initialTotalSteps QApplication.restoreOverrideCursor() def importEdigeoThfToDatabase(self, filename): ''' Import one edigeo THF files into database source : db_manager/dlg_import_vector.py ''' if self.go: # Get options targetSridOption = '-t_srs' if self.sourceSridFull == self.targetSridFull: targetSridOption = '-a_srs' # Build ogr2ogr command conn_name = self.dialog.connectionName settings = QSettings() settings.beginGroup( u"/%s/%s" % (self.db.dbplugin().connectionSettingsKey(), conn_name) ) # normalising file path filename = os.path.normpath(filename) if self.dialog.dbType == 'postgis': if not settings.contains( "database" ): # non-existent entry? raise InvalidDataException( self.tr('There is no defined database connection "%s".') % conn_name ) settingsList = ["service", "host", "port", "database", "username", "password"] service, host, port, database, username, password = map(lambda x: settings.value(x), settingsList) if service: pg_access = 'PG:service=%s active_schema=%s' % ( service, self.dialog.schema ) else: pg_access = 'PG:host=%s port=%s dbname=%s active_schema=%s user=%s password=%s' % ( host, port, database, self.dialog.schema, username, password ) cmdArgs = [ '', '-s_srs', self.sourceSridFull, targetSridOption, self.targetSridFull, '-append', '-f', 'PostgreSQL', pg_access, filename, '-lco', 'GEOMETRY_NAME=geom', '-lco', 'PG_USE_COPY=YES', '-nlt', 'GEOMETRY', '-gt', '50000', '--config', 'OGR_EDIGEO_CREATE_LABEL_LAYERS', 'NO' ] #-c client_encoding=latin1 if self.dialog.dbType == 'spatialite': if not settings.contains( "sqlitepath" ): # non-existent entry? self.go = False raise InvalidDataException( u'there is no defined database connection "%s".' % conn_name ) database = settings.value("sqlitepath") cmdArgs = [ '', '-s_srs', self.sourceSridFull, targetSridOption, self.targetSridFull, '-append', '-f', 'SQLite', database, filename, '-lco', 'GEOMETRY_NAME=geom', '-nlt', 'GEOMETRY', '-dsco', 'SPATIALITE=YES', '-gt', '50000', '--config', 'OGR_EDIGEO_CREATE_LABEL_LAYERS', 'NO', '--config', 'OGR_SQLITE_SYNCHRONOUS', 'OFF', '--config', 'OGR_SQLITE_CACHE', '512' ] self.qc.updateLog( ' '.join(cmdArgs)) # Run only if ogr2ogr found if self.go: # Workaround to get ogr2ogr error messages via stdout # as ogr2ogr.py does not return exceptions nor error messages # but only prints the error before returning False stdout = sys.stdout try: sys.stdout = file = StringIO.StringIO() self.go = ogr2ogr(cmdArgs) printedString = file.getvalue() finally: sys.stdout = stdout if not self.go: self.qc.updateLog( u"Erreur - L'import des données via OGR2OGR a échoué:\n\n%s\n\n%s" % ( printedString, cmdArgs ) ) return None def importEdigeoVecToDatabase(self, path): ''' Get edigeo relations between objects from a .VEC file and add them in edigeo_rel table ''' if self.go: reg = '^RID[a-zA-z]{1}[a-zA-z]{1}[0-9]{2}:(Rel_.+)_(Objet_[0-9]+)_(Objet_[0-9]+)' with open(path) as inputFile: # Get a list of RID relations combining a "Rel" and two "_Objet" l = [ a[0] for a in [re.findall(r'%s' % reg, line) for line in inputFile] if a] # Create a sql script to insert all items if self.dialog.dbType == 'postgis': sql="BEGIN;" for item in l: sql+= "INSERT INTO edigeo_rel ( nom, de, vers) VALUES ( '%s', '%s', '%s');" % (item[0], item[1], item[2] ) sql+="COMMIT;" sql = cadastre_common.setSearchPath(sql, self.dialog.schema) self.executeSqlQuery(sql) if self.dialog.dbType == 'spatialite': c = self.connector._get_cursor() query = 'INSERT INTO edigeo_rel (nom, de, vers) VALUES (?, ?, ?)' try: c.executemany(query, [ (item[0], item[1], item[2]) for item in l] ) self.connector._commit() except: self.qc.updateLog('Erreurs pendant la requête : %s' % sql) finally: c.close() del c def updateMultipolygonFromVec(self, path, layerType='edigeo'): ''' Run the update multipolygon query for each VEC files on the given layer type (edigeo = import tables, cadastre = cadastre geo_* tables) ''' # Get SQL update queries sqlList = self.getUpdateMultipolygonFromVecQuery(path, layerType) # Run each query for sql in sqlList: if self.dialog.dbType == 'postgis': sql = self.qc.setSearchPath(sql, self.dialog.schema) self.executeSqlQuery(sql) def getUpdateMultipolygonFromVecQuery(self, path, layerType='edigeo'): ''' EDIGEO ogr driver does not import multipolygon. This method is a patch : it parses the vec file and get WKT. Then the method build an SQL update query adapted on the given layer type (edigeo = import tables, cadastre = cadastre geo_* tables) ''' sqlList = [] # Class wich get multipolygons from getmultipolygonfromvec import GetMultiPolygonFromVec getMultiPolygon = GetMultiPolygonFromVec() # Relations between edigeo import tables and geo_* cadastre table impCadRel = { 'batiment_id' : 'geo_batiment', 'commune_id': 'geo_commune', 'lieudit_id': 'geo_lieudit', 'parcelle_id': 'geo_parcelle', 'section_id': 'geo_section', 'subdfisc_id': 'geo_subdfisc', 'subdsect_id': 'geo_subdsect', 'tronfluv_id': 'geo_tronfluv', 'tronroute_id': 'geo_tronroute', 'tsurf_id': 'geo_tsurf' } # Get dictionnary dic = getMultiPolygon( path ) if dic: # Loop for each layer found in VEC with multi-polygon to update for layer, item in dic.items(): table = layer.lower() # do the changes only for polygon layers if table not in impCadRel: continue # Replace table name if the update is not done on edigeo import table # but on the cadastre geo_* layers instead if layerType == 'cadastre': table = impCadRel[table] # Build SQL sql = '' for obj, wkt in item.items(): self.multiPolygonUpdated+=1 sql+= " UPDATE %s SET geom = ST_Transform(ST_GeomFromText('%s', %s), %s)" % ( table, wkt, self.sourceSrid, self.targetSrid ) # only for given object id sql+= " WHERE object_rid = '%s' " % str(obj) # only if the 2 geometries are indeed different. To be debbuged : geom <> geom : operator is not unique #~ sql+= " AND geom != ST_Transform(ST_GeomFromText('%s', %s), %s) " % (wkt, self.sourceSrid, self.targetSrid) # only if the 2 geometries are related (object_rid is not unique) if self.dialog.dbType == 'postgis': sql+= " AND geom @ ST_Transform(ST_GeomFromText('%s', %s), %s) ; " % (wkt, self.sourceSrid, self.targetSrid) else: sql+= " AND ST_Intersects(geom, ST_Transform(ST_GeomFromText('%s', %s), %s) ); " % (wkt, self.sourceSrid, self.targetSrid) sqlList.append(sql) return sqlList def dropEdigeoRawData(self): ''' Drop Edigeo raw data tables ''' if self.go: # DROP edigeo import tables edigeoImportTables = [ 'batiment_id', 'borne_id', 'boulon_id', 'commune_id', 'croix_id', 'id_s_obj_z_1_2_2', 'lieudit_id', 'numvoie_id', 'parcelle_id', 'ptcanv_id', 'section_id', 'subdfisc_id', 'subdsect_id', 'symblim_id', 'tline_id', 'tpoint_id', 'tronfluv_id', 'tronroute_id', 'tsurf_id', 'voiep_id', 'zoncommuni_id' #~ 'edigeo_rel', ] sql = '' for table in edigeoImportTables: sql+= 'DROP TABLE IF EXISTS "%s";' % table if self.dialog.dbType == 'postgis': sql = cadastre_common.setSearchPath(sql, self.dialog.schema) self.executeSqlQuery(sql)