コード例 #1
0
    def processAlgorithm(self, parameters, context, feedback):
        connection = self.parameterAsString(parameters, self.DATABASE, context)
        id_field = self.parameterAsString(parameters, self.ID_FIELD, context)
        geom_field = self.parameterAsString(
            parameters, self.GEOMETRY_FIELD, context)
        uri = postgis.uri_from_name(connection)
        sql = self.parameterAsString(parameters, self.SQL, context)
        sql = sql.replace('\n', ' ')
        uri.setDataSource("", "(" + sql + ")", geom_field, "", id_field)

        vlayer = QgsVectorLayer(uri.uri(), "layername", "postgres")

        if not vlayer.isValid():
            raise QgsProcessingException(self.tr("""This layer is invalid!
                Please check the PostGIS log for error messages."""))

        context.temporaryLayerStore().addMapLayer(vlayer)
        context.addLayerToLoadOnCompletion(
            vlayer.id(),
            QgsProcessingContext.LayerDetails('SQL layer',
                                              context.project(),
                                              self.OUTPUT))

        return {self.OUTPUT: vlayer.id()}
コード例 #2
0
    def getConsoleCommands(self):
        connection = self.DB_CONNECTIONS[self.getParameterValue(self.DATABASE)]
        uri = uri_from_name(connection)
        if self.processing:
            # to get credentials input when needed
            uri = GeoDB(uri=uri).uri

        inLayer = self.getParameterValue(self.INPUT_LAYER)
        ogrLayer = ogrConnectionString(inLayer)[1:-1]
        shapeEncoding = self.getParameterValue(self.SHAPE_ENCODING)
        ssrs = str(self.getParameterValue(self.S_SRS))
        tsrs = str(self.getParameterValue(self.T_SRS))
        asrs = str(self.getParameterValue(self.A_SRS))
        schema = str(self.getParameterValue(self.SCHEMA))
        table = str(self.getParameterValue(self.TABLE))
        pk = str(self.getParameterValue(self.PK))
        pkstring = "-lco FID=" + pk
        primary_key = self.getParameterValue(self.PRIMARY_KEY)
        geocolumn = str(self.getParameterValue(self.GEOCOLUMN))
        geocolumnstring = "-lco GEOMETRY_NAME=" + geocolumn
        dim = self.DIMLIST[self.getParameterValue(self.DIM)]
        dimstring = "-lco DIM=" + dim
        simplify = str(self.getParameterValue(self.SIMPLIFY))
        segmentize = str(self.getParameterValue(self.SEGMENTIZE))
        spat = self.getParameterValue(self.SPAT)
        clip = self.getParameterValue(self.CLIP)
        where = str(self.getParameterValue(self.WHERE))
        wherestring = '-where "' + where + '"'
        gt = str(self.getParameterValue(self.GT))
        overwrite = self.getParameterValue(self.OVERWRITE)
        append = self.getParameterValue(self.APPEND)
        addfields = self.getParameterValue(self.ADDFIELDS)
        launder = self.getParameterValue(self.LAUNDER)
        launderstring = "-lco LAUNDER=NO"
        index = self.getParameterValue(self.INDEX)
        indexstring = "-lco SPATIAL_INDEX=OFF"
        skipfailures = self.getParameterValue(self.SKIPFAILURES)
        promotetomulti = self.getParameterValue(self.PROMOTETOMULTI)
        precision = self.getParameterValue(self.PRECISION)
        options = str(self.getParameterValue(self.OPTIONS))

        arguments = []
        arguments.append('-progress')
        arguments.append('--config PG_USE_COPY YES')
        if shapeEncoding:
            arguments.append('--config')
            arguments.append('SHAPE_ENCODING')
            arguments.append('"' + shapeEncoding + '"')
        arguments.append('-f')
        arguments.append('PostgreSQL')
        arguments.append('PG:"')
        for token in uri.connectionInfo(self.processing).split(' '):
            arguments.append(token)
        arguments.append('active_schema={}'.format(schema or 'public'))
        arguments.append('"')
        arguments.append(dimstring)
        arguments.append(ogrLayer)
        arguments.append(ogrLayerName(inLayer))
        if index:
            arguments.append(indexstring)
        if launder:
            arguments.append(launderstring)
        if append:
            arguments.append('-append')
        if addfields:
            arguments.append('-addfields')
        if overwrite:
            arguments.append('-overwrite')
        if len(self.GEOMTYPE[self.getParameterValue(self.GTYPE)]) > 0:
            arguments.append('-nlt')
            arguments.append(self.GEOMTYPE[self.getParameterValue(self.GTYPE)])
        if len(geocolumn) > 0:
            arguments.append(geocolumnstring)
        if len(pk) > 0:
            arguments.append(pkstring)
        elif primary_key is not None:
            arguments.append("-lco FID=" + primary_key)
        if len(table) == 0:
            table = ogrLayerName(inLayer).lower()
        if schema:
            table = '{}.{}'.format(schema, table)
        arguments.append('-nln')
        arguments.append(table)
        if len(ssrs) > 0:
            arguments.append('-s_srs')
            arguments.append(ssrs)
        if len(tsrs) > 0:
            arguments.append('-t_srs')
            arguments.append(tsrs)
        if len(asrs) > 0:
            arguments.append('-a_srs')
            arguments.append(asrs)
        if len(spat) > 0:
            regionCoords = spat.split(',')
            arguments.append('-spat')
            arguments.append(regionCoords[0])
            arguments.append(regionCoords[2])
            arguments.append(regionCoords[1])
            arguments.append(regionCoords[3])
            if clip:
                arguments.append('-clipsrc spat_extent')
        if skipfailures:
            arguments.append('-skipfailures')
        if where:
            arguments.append(wherestring)
        if len(simplify) > 0:
            arguments.append('-simplify')
            arguments.append(simplify)
        if len(segmentize) > 0:
            arguments.append('-segmentize')
            arguments.append(segmentize)
        if len(gt) > 0:
            arguments.append('-gt')
            arguments.append(gt)
        if promotetomulti:
            arguments.append('-nlt PROMOTE_TO_MULTI')
        if precision is False:
            arguments.append('-lco PRECISION=NO')
        if len(options) > 0:
            arguments.append(options)

        commands = []
        if isWindows():
            commands = ['cmd.exe', '/C ', 'ogr2ogr.exe',
                        GdalUtils.escapeAndJoin(arguments)]
        else:
            commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]

        return commands
コード例 #3
0
    def getConsoleCommands(self, parameters):
        connection = self.DB_CONNECTIONS[self.getParameterValue(self.DATABASE)]
        uri = uri_from_name(connection)
        if self.processing:
            # to get credentials input when needed
            uri = GeoDB(uri=uri).uri

        inLayer = self.getParameterValue(self.INPUT_LAYER)
        ogrLayer = ogrConnectionString(inLayer)[1:-1]
        shapeEncoding = self.getParameterValue(self.SHAPE_ENCODING)
        schema = str(self.getParameterValue(self.SCHEMA))
        table = str(self.getParameterValue(self.TABLE))
        pk = str(self.getParameterValue(self.PK))
        pkstring = "-lco FID=" + pk
        primary_key = self.getParameterValue(self.PRIMARY_KEY)
        where = str(self.getParameterValue(self.WHERE))
        wherestring = '-where "' + where + '"'
        gt = str(self.getParameterValue(self.GT))
        overwrite = self.getParameterValue(self.OVERWRITE)
        append = self.getParameterValue(self.APPEND)
        addfields = self.getParameterValue(self.ADDFIELDS)
        launder = self.getParameterValue(self.LAUNDER)
        launderstring = "-lco LAUNDER=NO"
        skipfailures = self.getParameterValue(self.SKIPFAILURES)
        precision = self.getParameterValue(self.PRECISION)
        options = str(self.getParameterValue(self.OPTIONS))

        arguments = []
        arguments.append('-progress')
        arguments.append('--config PG_USE_COPY YES')
        if len(shapeEncoding) > 0:
            arguments.append('--config')
            arguments.append('SHAPE_ENCODING')
            arguments.append('"' + shapeEncoding + '"')
        arguments.append('-f')
        arguments.append('PostgreSQL')
        arguments.append('PG:"')
        for token in uri.connectionInfo(self.processing).split(' '):
            arguments.append(token)
        arguments.append('active_schema={}'.format(schema or 'public'))
        arguments.append('"')
        arguments.append(ogrLayer)
        arguments.append('-nlt NONE')
        arguments.append(ogrLayerName(inLayer))
        if launder:
            arguments.append(launderstring)
        if append:
            arguments.append('-append')
        if addfields:
            arguments.append('-addfields')
        if overwrite:
            arguments.append('-overwrite')
        if len(pk) > 0:
            arguments.append(pkstring)
        elif primary_key is not None:
            arguments.append("-lco FID=" + primary_key)
        if len(table) == 0:
            table = ogrLayerName(inLayer).lower()
        if schema:
            table = '{}.{}'.format(schema, table)
        arguments.append('-nln')
        arguments.append(table)
        if skipfailures:
            arguments.append('-skipfailures')
        if where:
            arguments.append(wherestring)
        if len(gt) > 0:
            arguments.append('-gt')
            arguments.append(gt)
        if not precision:
            arguments.append('-lco PRECISION=NO')
        if len(options) > 0:
            arguments.append(options)

        commands = []
        if isWindows():
            commands = ['cmd.exe', '/C ', 'ogr2ogr.exe',
                        GdalUtils.escapeAndJoin(arguments)]
        else:
            commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]

        return commands
コード例 #4
0
    def getConsoleCommands(self,
                           parameters,
                           context,
                           feedback,
                           executing=True):
        connection = self.DB_CONNECTIONS[self.getParameterValue(self.DATABASE)]
        uri = uri_from_name(connection)
        if executing:
            # to get credentials input when needed
            uri = GeoDB(uri=uri).uri

        inLayer = self.getParameterValue(self.INPUT_LAYER)
        ogrLayer = GdalUtils.ogrConnectionString(inLayer, context)
        shapeEncoding = self.getParameterValue(self.SHAPE_ENCODING)
        schema = str(self.getParameterValue(self.SCHEMA))
        table = str(self.getParameterValue(self.TABLE))
        pk = str(self.getParameterValue(self.PK))
        pkstring = "-lco FID=" + pk
        primary_key = self.getParameterValue(self.PRIMARY_KEY)
        where = str(self.getParameterValue(self.WHERE))
        wherestring = '-where "' + where + '"'
        gt = str(self.getParameterValue(self.GT))
        overwrite = self.getParameterValue(self.OVERWRITE)
        append = self.getParameterValue(self.APPEND)
        addfields = self.getParameterValue(self.ADDFIELDS)
        launder = self.getParameterValue(self.LAUNDER)
        launderstring = "-lco LAUNDER=NO"
        skipfailures = self.getParameterValue(self.SKIPFAILURES)
        precision = self.getParameterValue(self.PRECISION)
        options = str(self.getParameterValue(self.OPTIONS))

        arguments = []
        arguments.append('-progress')
        arguments.append('--config PG_USE_COPY YES')
        if len(shapeEncoding) > 0:
            arguments.append('--config')
            arguments.append('SHAPE_ENCODING')
            arguments.append('"' + shapeEncoding + '"')
        arguments.append('-f')
        arguments.append('PostgreSQL')
        arguments.append('PG:"')
        for token in uri.connectionInfo(executing).split(' '):
            arguments.append(token)
        arguments.append('active_schema={}'.format(schema or 'public'))
        arguments.append('"')
        arguments.append(ogrLayer)
        arguments.append('-nlt NONE')
        arguments.append(GdalUtils.ogrLayerName(inLayer))
        if launder:
            arguments.append(launderstring)
        if append:
            arguments.append('-append')
        if addfields:
            arguments.append('-addfields')
        if overwrite:
            arguments.append('-overwrite')
        if len(pk) > 0:
            arguments.append(pkstring)
        elif primary_key is not None:
            arguments.append("-lco FID=" + primary_key)
        if len(table) == 0:
            table = GdalUtils.ogrLayerName(inLayer).lower()
        if schema:
            table = '{}.{}'.format(schema, table)
        arguments.append('-nln')
        arguments.append(table)
        if skipfailures:
            arguments.append('-skipfailures')
        if where:
            arguments.append(wherestring)
        if len(gt) > 0:
            arguments.append('-gt')
            arguments.append(gt)
        if not precision:
            arguments.append('-lco PRECISION=NO')
        if len(options) > 0:
            arguments.append(options)

        commands = []
        if isWindows():
            commands = [
                'cmd.exe', '/C ', 'ogr2ogr.exe',
                GdalUtils.escapeAndJoin(arguments)
            ]
        else:
            commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]

        return commands
コード例 #5
0
    def getConsoleCommands(self, parameters, context, feedback, executing=True):
        connection = self.parameterAsString(parameters, self.DATABASE, context)
        uri = uri_from_name(connection)
        if executing:
            # to get credentials input when needed
            uri = GeoDB(uri=uri).uri

        ogrLayer, layername = self.getOgrCompatibleSource(self.INPUT, parameters, context, feedback, executing)
        shapeEncoding = self.parameterAsString(parameters, self.SHAPE_ENCODING, context)
        ssrs = self.parameterAsCrs(parameters, self.S_SRS, context)
        tsrs = self.parameterAsCrs(parameters, self.T_SRS, context)
        asrs = self.parameterAsCrs(parameters, self.A_SRS, context)
        table = self.parameterAsString(parameters, self.TABLE, context)
        schema = self.parameterAsString(parameters, self.SCHEMA, context)
        pk = self.parameterAsString(parameters, self.PK, context)
        pkstring = "-lco FID=" + pk
        primary_key = self.parameterAsString(parameters, self.PRIMARY_KEY, context)
        geocolumn = self.parameterAsString(parameters, self.GEOCOLUMN, context)
        geocolumnstring = "-lco GEOMETRY_NAME=" + geocolumn
        dim = self.DIMLIST[self.parameterAsEnum(parameters, self.DIM, context)]
        dimstring = "-lco DIM=" + dim
        simplify = self.parameterAsString(parameters, self.SIMPLIFY, context)
        segmentize = self.parameterAsString(parameters, self.SEGMENTIZE, context)
        spat = self.parameterAsExtent(parameters, self.SPAT, context)
        clip = self.parameterAsBool(parameters, self.CLIP, context)
        where = self.parameterAsString(parameters, self.WHERE, context)
        wherestring = '-where "' + where + '"'
        gt = self.parameterAsString(parameters, self.GT, context)
        overwrite = self.parameterAsBool(parameters, self.OVERWRITE, context)
        append = self.parameterAsBool(parameters, self.APPEND, context)
        addfields = self.parameterAsBool(parameters, self.ADDFIELDS, context)
        launder = self.parameterAsBool(parameters, self.LAUNDER, context)
        launderstring = "-lco LAUNDER=NO"
        index = self.parameterAsBool(parameters, self.INDEX, context)
        indexstring = "-lco SPATIAL_INDEX=OFF"
        skipfailures = self.parameterAsBool(parameters, self.SKIPFAILURES, context)
        promotetomulti = self.parameterAsBool(parameters, self.PROMOTETOMULTI, context)
        precision = self.parameterAsBool(parameters, self.PRECISION, context)
        options = self.parameterAsString(parameters, self.OPTIONS, context)

        arguments = []
        arguments.append('-progress')
        arguments.append('--config PG_USE_COPY YES')
        if shapeEncoding:
            arguments.append('--config')
            arguments.append('SHAPE_ENCODING')
            arguments.append('"' + shapeEncoding + '"')
        arguments.append('-f')
        arguments.append('PostgreSQL')
        arguments.append('PG:"')
        for token in uri.connectionInfo(executing).split(' '):
            arguments.append(token)
        arguments.append('active_schema={}'.format(schema or 'public'))
        arguments.append('"')
        arguments.append(dimstring)
        arguments.append(ogrLayer)
        arguments.append(layername)
        if index:
            arguments.append(indexstring)
        if launder:
            arguments.append(launderstring)
        if append:
            arguments.append('-append')
        if addfields:
            arguments.append('-addfields')
        if overwrite:
            arguments.append('-overwrite')
        if len(self.GEOMTYPE[self.parameterAsEnum(parameters, self.GTYPE, context)]) > 0:
            arguments.append('-nlt')
            arguments.append(self.GEOMTYPE[self.parameterAsEnum(parameters, self.GTYPE, context)])
        if len(geocolumn) > 0:
            arguments.append(geocolumnstring)
        if len(pk) > 0:
            arguments.append(pkstring)
        elif primary_key is not None:
            arguments.append("-lco FID=" + primary_key)
        if len(table) == 0:
            table = layername.lower()
        if schema:
            table = '{}.{}'.format(schema, table)
        arguments.append('-nln')
        arguments.append(table)
        if ssrs.isValid():
            arguments.append('-s_srs')
            arguments.append(GdalUtils.gdal_crs_string(ssrs))
        if tsrs.isValid():
            arguments.append('-t_srs')
            arguments.append(GdalUtils.gdal_crs_string(tsrs))
        if asrs.isValid():
            arguments.append('-a_srs')
            arguments.append(GdalUtils.gdal_crs_string(asrs))
        if not spat.isNull():
            arguments.append('-spat')
            arguments.append(spat.xMinimum())
            arguments.append(spat.yMinimum())
            arguments.append(spat.xMaximum())
            arguments.append(spat.yMaximum())
            if clip:
                arguments.append('-clipsrc spat_extent')
        if skipfailures:
            arguments.append('-skipfailures')
        if where:
            arguments.append(wherestring)
        if len(simplify) > 0:
            arguments.append('-simplify')
            arguments.append(simplify)
        if len(segmentize) > 0:
            arguments.append('-segmentize')
            arguments.append(segmentize)
        if len(gt) > 0:
            arguments.append('-gt')
            arguments.append(gt)
        if promotetomulti:
            arguments.append('-nlt PROMOTE_TO_MULTI')
        if precision is False:
            arguments.append('-lco PRECISION=NO')
        if len(options) > 0:
            arguments.append(options)

        commands = []
        if isWindows():
            commands = ['cmd.exe', '/C ', 'ogr2ogr.exe',
                        GdalUtils.escapeAndJoin(arguments)]
        else:
            commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]

        return commands
コード例 #6
0
    def processAlgorithm(self, parameters, context, feedback):
        """
        Here is where the processing itself takes place.
        """

        ### RETRIEVE PARAMETERS ###
        # Retrieve the input vector layer = study area
        study_area = self.parameterAsSource(parameters, self.STUDY_AREA,
                                            context)
        # Retrieve the output PostGIS layer name and format it
        layer_name = self.parameterAsString(parameters, self.OUTPUT_NAME,
                                            context)
        ts = datetime.now()
        format_name = "{} {}".format(layer_name,
                                     str(ts.strftime('%Y%m%d_%H%M%S')))
        # Retrieve the taxons filters
        groupe_taxo = [
            self.db_variables.value('groupe_taxo')[i] for i in (
                self.parameterAsEnums(parameters, self.GROUPE_TAXO, context))
        ]
        regne = [
            self.db_variables.value('regne')[i]
            for i in (self.parameterAsEnums(parameters, self.REGNE, context))
        ]
        phylum = [
            self.db_variables.value('phylum')[i]
            for i in (self.parameterAsEnums(parameters, self.PHYLUM, context))
        ]
        classe = [
            self.db_variables.value('classe')[i]
            for i in (self.parameterAsEnums(parameters, self.CLASSE, context))
        ]
        ordre = [
            self.db_variables.value('ordre')[i]
            for i in (self.parameterAsEnums(parameters, self.ORDRE, context))
        ]
        famille = [
            self.db_variables.value('famille')[i]
            for i in (self.parameterAsEnums(parameters, self.FAMILLE, context))
        ]
        group1_inpn = [
            self.db_variables.value('group1_inpn')[i] for i in (
                self.parameterAsEnums(parameters, self.GROUP1_INPN, context))
        ]
        group2_inpn = [
            self.db_variables.value('group2_inpn')[i] for i in (
                self.parameterAsEnums(parameters, self.GROUP2_INPN, context))
        ]
        # Retrieve the datetime filter
        period_type = self.period_variables[self.parameterAsEnum(
            parameters, self.PERIOD, context)]
        # Retrieve the extra "where" conditions
        extra_where = self.parameterAsString(parameters, self.EXTRA_WHERE,
                                             context)

        ### CONSTRUCT "WHERE" CLAUSE (SQL) ###
        # Construct the sql array containing the study area's features geometry
        array_polygons = construct_sql_array_polygons(study_area)
        # Define the "where" clause of the SQL query, aiming to retrieve the output PostGIS layer = biodiversity data
        where = "is_valid and ST_within(geom, ST_union({}))".format(
            array_polygons)
        # Define a dictionnary with the aggregated taxons filters and complete the "where" clause thanks to it
        taxons_filters = {
            "groupe_taxo": groupe_taxo,
            "regne": regne,
            "phylum": phylum,
            "classe": classe,
            "ordre": ordre,
            "famille": famille,
            "obs.group1_inpn": group1_inpn,
            "obs.group2_inpn": group2_inpn
        }
        taxons_where = construct_sql_taxons_filter(taxons_filters)
        where += taxons_where
        # Complete the "where" clause with the datetime filter
        datetime_where = construct_sql_datetime_filter(self, period_type, ts,
                                                       parameters, context)
        where += datetime_where
        # Complete the "where" clause with the extra conditions
        where += " " + extra_where

        ### EXECUTE THE SQL QUERY ###
        # Retrieve the data base connection name
        connection = self.parameterAsString(parameters, self.DATABASE, context)
        # URI --> Configures connection to database and the SQL query
        uri = postgis.uri_from_name(connection)
        # Define the SQL query
        query = """SELECT obs.*
        FROM src_lpodatas.v_c_observations obs
        LEFT JOIN taxonomie.taxref t ON obs.taxref_cdnom=t.cd_nom
        WHERE {}""".format(where)
        #feedback.pushInfo(query)
        # Format the URI with the query
        uri.setDataSource("", "(" + query + ")", "geom", "", "id_synthese")

        ### GET THE OUTPUT LAYER ###
        # Retrieve the output PostGIS layer = biodiversity data
        layer_obs = QgsVectorLayer(uri.uri(), format_name, "postgres")
        # Check if the PostGIS layer is valid
        check_layer_is_valid(feedback, layer_obs)
        # Load the PostGIS layer
        load_layer(context, layer_obs)

        ### MANAGE EXPORT ###
        # Create new valid fields for the sink
        new_fields = format_layer_export(layer_obs)
        # Retrieve the sink for the export
        (sink, dest_id) = self.parameterAsSink(parameters, self.OUTPUT,
                                               context, new_fields,
                                               layer_obs.wkbType(),
                                               layer_obs.sourceCrs())
        if sink is None:
            # Return the PostGIS layer
            return {self.OUTPUT: layer_obs.id()}
        else:
            # Fill the sink and return it
            for feature in layer_obs.getFeatures():
                sink.addFeature(feature)
            return {self.OUTPUT: dest_id}
コード例 #7
0
ファイル: ogr2ogrtopostgislist.py プロジェクト: dimespi/QGIS
    def getConsoleCommands(self, parameters, context, feedback, executing=True):
        connection = self.parameterAsString(parameters, self.DATABASE, context)
        uri = uri_from_name(connection)
        if executing:
            # to get credentials input when needed
            uri = GeoDB(uri=uri).uri

        ogrLayer, layername = self.getOgrCompatibleSource(self.INPUT, parameters, context, feedback, executing)
        shapeEncoding = self.parameterAsString(parameters, self.SHAPE_ENCODING, context)
        ssrs = self.parameterAsCrs(parameters, self.S_SRS, context)
        tsrs = self.parameterAsCrs(parameters, self.T_SRS, context)
        asrs = self.parameterAsCrs(parameters, self.A_SRS, context)
        table = self.parameterAsString(parameters, self.TABLE, context)
        schema = self.parameterAsString(parameters, self.SCHEMA, context)
        pk = self.parameterAsString(parameters, self.PK, context)
        pkstring = "-lco FID=" + pk
        primary_key = self.parameterAsString(parameters, self.PRIMARY_KEY, context)
        geocolumn = self.parameterAsString(parameters, self.GEOCOLUMN, context)
        geocolumnstring = "-lco GEOMETRY_NAME=" + geocolumn
        dim = self.DIMLIST[self.parameterAsEnum(parameters, self.DIM, context)]
        dimstring = "-lco DIM=" + dim
        simplify = self.parameterAsString(parameters, self.SIMPLIFY, context)
        segmentize = self.parameterAsString(parameters, self.SEGMENTIZE, context)
        spat = self.parameterAsExtent(parameters, self.SPAT, context)
        clip = self.parameterAsBool(parameters, self.CLIP, context)
        where = self.parameterAsString(parameters, self.WHERE, context)
        wherestring = '-where "' + where + '"'
        gt = self.parameterAsString(parameters, self.GT, context)
        overwrite = self.parameterAsBool(parameters, self.OVERWRITE, context)
        append = self.parameterAsBool(parameters, self.APPEND, context)
        addfields = self.parameterAsBool(parameters, self.ADDFIELDS, context)
        launder = self.parameterAsBool(parameters, self.LAUNDER, context)
        launderstring = "-lco LAUNDER=NO"
        index = self.parameterAsBool(parameters, self.INDEX, context)
        indexstring = "-lco SPATIAL_INDEX=OFF"
        skipfailures = self.parameterAsBool(parameters, self.SKIPFAILURES, context)
        promotetomulti = self.parameterAsBool(parameters, self.PROMOTETOMULTI, context)
        precision = self.parameterAsBool(parameters, self.PRECISION, context)
        options = self.parameterAsString(parameters, self.OPTIONS, context)

        arguments = []
        arguments.append('-progress')
        arguments.append('--config PG_USE_COPY YES')
        if shapeEncoding:
            arguments.append('--config')
            arguments.append('SHAPE_ENCODING')
            arguments.append('"' + shapeEncoding + '"')
        arguments.append('-f')
        arguments.append('PostgreSQL')
        arguments.append('PG:"')
        for token in uri.connectionInfo(executing).split(' '):
            arguments.append(token)
        arguments.append('active_schema={}'.format(schema or 'public'))
        arguments.append('"')
        arguments.append(dimstring)
        arguments.append(ogrLayer)
        arguments.append(layername)
        if index:
            arguments.append(indexstring)
        if launder:
            arguments.append(launderstring)
        if append:
            arguments.append('-append')
        if addfields:
            arguments.append('-addfields')
        if overwrite:
            arguments.append('-overwrite')
        if len(self.GEOMTYPE[self.parameterAsEnum(parameters, self.GTYPE, context)]) > 0:
            arguments.append('-nlt')
            arguments.append(self.GEOMTYPE[self.parameterAsEnum(parameters, self.GTYPE, context)])
        if len(geocolumn) > 0:
            arguments.append(geocolumnstring)
        if pk:
            arguments.append(pkstring)
        elif primary_key:
            arguments.append("-lco FID=" + primary_key)
        if len(table) == 0:
            table = layername.lower()
        if schema:
            table = '{}.{}'.format(schema, table)
        arguments.append('-nln')
        arguments.append(table)
        if ssrs.isValid():
            arguments.append('-s_srs')
            arguments.append(GdalUtils.gdal_crs_string(ssrs))
        if tsrs.isValid():
            arguments.append('-t_srs')
            arguments.append(GdalUtils.gdal_crs_string(tsrs))
        if asrs.isValid():
            arguments.append('-a_srs')
            arguments.append(GdalUtils.gdal_crs_string(asrs))
        if not spat.isNull():
            arguments.append('-spat')
            arguments.append(spat.xMinimum())
            arguments.append(spat.yMinimum())
            arguments.append(spat.xMaximum())
            arguments.append(spat.yMaximum())
            if clip:
                arguments.append('-clipsrc spat_extent')
        if skipfailures:
            arguments.append('-skipfailures')
        if where:
            arguments.append(wherestring)
        if len(simplify) > 0:
            arguments.append('-simplify')
            arguments.append(simplify)
        if len(segmentize) > 0:
            arguments.append('-segmentize')
            arguments.append(segmentize)
        if len(gt) > 0:
            arguments.append('-gt')
            arguments.append(gt)
        if promotetomulti:
            arguments.append('-nlt PROMOTE_TO_MULTI')
        if precision is False:
            arguments.append('-lco PRECISION=NO')
        if len(options) > 0:
            arguments.append(options)

        commands = []
        if isWindows():
            commands = ['cmd.exe', '/C ', 'ogr2ogr.exe',
                        GdalUtils.escapeAndJoin(arguments)]
        else:
            commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]

        return commands
コード例 #8
0
    def getConsoleCommands(self):
        connection = self.getParameterValue(self.DATABASE)
        uri = uri_from_name(connection)
        if self.processing:
            # to get credentials input when needed
            uri = GeoDB(uri=uri).uri

        inLayer = self.getParameterValue(self.INPUT_LAYER)
        ogrLayer = ogrConnectionString(inLayer)[1:-1]
        shapeEncoding = self.getParameterValue(self.SHAPE_ENCODING)
        ssrs = self.getParameterValue(self.S_SRS)
        tsrs = self.getParameterValue(self.T_SRS)
        asrs = self.getParameterValue(self.A_SRS)
        schema = self.getParameterValue(self.SCHEMA)
        table = self.getParameterValue(self.TABLE)
        pk = self.getParameterValue(self.PK)
        primary_key = self.getParameterValue(self.PRIMARY_KEY)
        geocolumn = self.getParameterValue(self.GEOCOLUMN)
        dim = self.DIMLIST[self.getParameterValue(self.DIM)]
        simplify = self.getParameterValue(self.SIMPLIFY)
        segmentize = self.getParameterValue(self.SEGMENTIZE)
        spat = self.getParameterValue(self.SPAT)
        clip = self.getParameterValue(self.CLIP)
        where = self.getParameterValue(self.WHERE)
        gt = self.getParameterValue(self.GT)
        overwrite = self.getParameterValue(self.OVERWRITE)
        append = self.getParameterValue(self.APPEND)
        addfields = self.getParameterValue(self.ADDFIELDS)
        launder = self.getParameterValue(self.LAUNDER)
        index = self.getParameterValue(self.INDEX)
        skipfailures = self.getParameterValue(self.SKIPFAILURES)
        promotetomulti = self.getParameterValue(self.PROMOTETOMULTI)
        precision = self.getParameterValue(self.PRECISION)
        options = self.getParameterValue(self.OPTIONS)

        arguments = []
        arguments.append('-progress')
        arguments.append('--config PG_USE_COPY YES')
        if shapeEncoding:
            arguments.append('--config')
            arguments.append('SHAPE_ENCODING')
            arguments.append('"' + shapeEncoding + '"')
        arguments.append('-f')
        arguments.append('PostgreSQL')
        arguments.append('PG:"')
        for token in uri.connectionInfo(self.processing).split(' '):
            arguments.append(token)
        arguments.append('active_schema={}'.format(schema or 'public'))
        arguments.append('"')
        arguments.append("-lco DIM=" + dim)
        arguments.append(ogrLayer)
        arguments.append(ogrLayerName(inLayer))
        if index:
            arguments.append("-lco SPATIAL_INDEX=OFF")
        if launder:
            arguments.append("-lco LAUNDER=NO")
        if append:
            arguments.append('-append')
        if addfields:
            arguments.append('-addfields')
        if overwrite:
            arguments.append('-overwrite')
        if len(self.GEOMTYPE[self.getParameterValue(self.GTYPE)]) > 0:
            arguments.append('-nlt')
            arguments.append(self.GEOMTYPE[self.getParameterValue(self.GTYPE)])
        if geocolumn:
            arguments.append("-lco GEOMETRY_NAME=" + geocolumn)
        if pk:
            arguments.append("-lco FID=" + pk)
        elif primary_key is not None:
            arguments.append("-lco FID=" + primary_key)
        if not table:
            table = ogrLayerName(inLayer).lower()
        if schema:
            table = '{}.{}'.format(schema, table)
        arguments.append('-nln')
        arguments.append(table)
        if ssrs:
            arguments.append('-s_srs')
            arguments.append(ssrs)
        if tsrs:
            arguments.append('-t_srs')
            arguments.append(tsrs)
        if asrs:
            arguments.append('-a_srs')
            arguments.append(asrs)
        if spat:
            regionCoords = spat.split(',')
            arguments.append('-spat')
            arguments.append(regionCoords[0])
            arguments.append(regionCoords[2])
            arguments.append(regionCoords[1])
            arguments.append(regionCoords[3])
            if clip:
                arguments.append('-clipsrc spat_extent')
        if skipfailures:
            arguments.append('-skipfailures')
        if where:
            arguments.append('-where "' + where + '"')
        if simplify:
            arguments.append('-simplify')
            arguments.append(simplify)
        if segmentize:
            arguments.append('-segmentize')
            arguments.append(segmentize)
        if gt:
            arguments.append('-gt')
            arguments.append(gt)
        if promotetomulti:
            arguments.append('-nlt PROMOTE_TO_MULTI')
        if precision is False:
            arguments.append('-lco PRECISION=NO')
        if options:
            arguments.append(options)

        commands = []
        if isWindows():
            commands = [
                'cmd.exe', '/C ', 'ogr2ogr.exe',
                GdalUtils.escapeAndJoin(arguments)
            ]
        else:
            commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]

        return commands
コード例 #9
0
    def processAlgorithm(self, parameters, context, feedback):
        msg = ""

        connection = self.parameterAsString(parameters, self.DATABASE, context)
        metadata = QgsProviderRegistry.instance().providerMetadata('postgres')
        conn = metadata.findConnection(connection)
        schema = self.parameterAsString(parameters, self.SCHEMA, context)
        table = self.parameterAsString(parameters, self.TABLE, context)
        input_layer = self.parameterAsVectorLayer(parameters, self.INPUT,
                                                  context)

        geom = None
        geomlayer = [
            "repere", "poi_tourisme", "poi_service", "liaison", "segment"
        ]
        if table in geomlayer:
            geom = "geom"

        uri = uri_from_name(connection)
        uri.setDataSource(schema, table, geom, "")
        layer = QgsVectorLayer(uri.uri(), table, "postgres")
        layer_name = layer.name()

        # Création du dictionnaire de correspondance des champs
        # Format générique d'une correspondance entre champs
        champs = {
            'expression': '',  # champs d'entrée
            'length': 0,  # longueur de destinaion
            'name': '',  # champs de destination
            'precision': 0,  # precision de destinaton
            'type': 10  # type de destination
        }
        matrix = self.parameterAsMatrix(parameters, self.MATRIX, context)
        field_map = []

        # Création du mapping de champs
        for field in layer.fields():
            # Champs fournis par l'utilisateur
            name = field.displayName()
            if name in matrix[1::2]:
                i = len(matrix) - 1 - matrix[::-1].index(name)
                c = champs
                c['expression'] = matrix[i - 1]
                c['name'] = name
                c['precision'] = field.precision()
                c['length'] = field.length()
                ccopy = c.copy()
                field_map.append(ccopy)
            else:
                # Champs éventuellement non fournis par l'utilisateur
                c = champs
                c['expression'] = ""
                c['name'] = name
                c['precision'] = field.precision()
                c['length'] = field.length()
                ccopy = c.copy()
                field_map.append(ccopy)

        if layer_name == 'portion':
            k = matrix.index('lien_itin')
            c_lien_itin = {
                'expression': matrix[k - 1],  # champs d'entrée
                'length': 0,  # longueur de destinaion
                'name': 'lien_itin',  # champs de destination
                'precision': 0,  # precision de destinaton
                'type': 2  # type de destination
            }
            field_map.append(c_lien_itin)
            if 'lien_segm' in matrix:
                m = matrix.index('lien_segm')
                c_lien_segm = {
                    'expression': matrix[m - 1],  # champs d'entrée
                    'length': 0,  # longueur de destinaion
                    'name': 'lien_segm',  # champs de destination
                    'precision': 0,  # precision de destinaton
                    'type': 2  # type de destination
                }
                field_map.append(c_lien_segm)

        if layer_name in ['itineraire', 'portion', 'segment']:
            if 'id_import' in matrix:
                n = matrix.index('id_import')
                c_id_import = {
                    'expression': matrix[n - 1],  # champs d'entrée
                    'length': 0,  # longueur de destinaion
                    'name': 'id_import',  # champs de destination
                    'precision': 0,  # precision de destinaton
                    'type': 2  # type de destination
                }
                field_map.append(c_id_import)

        # Refactorisation des champs
        refact_params = {
            'FIELDS_MAPPING': field_map,
            'INPUT': input_layer,
            'OUTPUT': 'memory:'
        }

        algresult = processing.run('qgis:refactorfields',
                                   refact_params,
                                   context=context,
                                   feedback=feedback,
                                   is_child_algorithm=True)

        feedback.pushInfo(tr("Refactoring des champs fait"))

        # Exporter dans PostgreSQL
        self.to_postgresql(connection, layer_name, algresult['OUTPUT'],
                           context, feedback)

        # Importer la table dans veloroutes
        self.update_to_veloroutes(conn, layer_name, feedback)

        return {self.OUTPUT_MSG: msg}
コード例 #10
0
    def processAlgorithm(self, parameters, context, feedback):
        msg = ""
        output_layers = []
        layers_name_none = dict()
        layers_name_none["v_certificat"] = "id_view"
        layers_name_none["v_voie"] = "id_view"
        layers_name_none["v_section"] = "id_view"
        layers_name_none["v_parcelle"] = "id_view"

        # override = self.parameterAsBool(parameters, self.OVERRIDE, context)
        connection = self.parameterAsString(parameters, self.DATABASE, context)
        schema = self.parameterAsString(parameters, self.SCHEMA, context)
        data_update = self.parameterAsBool(parameters, self.TRUNCATE_PARCELLE,
                                           context)

        if data_update:
            feedback.pushInfo("## Mise à jour des données parcelles ##")
            feedback.pushInfo(
                "## Rend id_parcelle = null dans adresse.point_adresse ##")
            sql = """
                UPDATE adresse.point_adresse pa
                SET id_parcelle = NULL;
            """
            _, _, _, ok, error_message = fetch_data_from_sql_query(
                connection, sql)
            if not ok:
                return {
                    self.OUTPUT_MSG: error_message,
                    self.OUTPUT: output_layers
                }

            feedback.pushInfo("""
                ## Désactivation de la clé étrangère sur adresse.point_adresse pour
                pouvoir vider la table adresse.parcelle ##
            """)
            sql = """
                ALTER TABLE adresse.point_adresse DROP CONSTRAINT point_adresse_id_parcelle_fkey;
            """
            _, _, _, ok, error_message = fetch_data_from_sql_query(
                connection, sql)
            if not ok:
                return {
                    self.OUTPUT_MSG: error_message,
                    self.OUTPUT: output_layers
                }

            feedback.pushInfo("## Vide la table adresse.parcelle ##")
            sql = """
                TRUNCATE adresse.parcelle RESTART IDENTITY;
            """
            _, _, _, ok, error_message = fetch_data_from_sql_query(
                connection, sql)
            if not ok:
                return {
                    self.OUTPUT_MSG: error_message,
                    self.OUTPUT: output_layers
                }

            feedback.pushInfo(
                "## Réactivation de la clé étrangère sur adresse.point_adresse ##"
            )
            sql = """
                ALTER TABLE adresse.point_adresse
                ADD CONSTRAINT point_adresse_id_parcelle_fkey FOREIGN KEY (id_parcelle)
                REFERENCES adresse.parcelle (fid);
            """
            _, _, _, ok, error_message = fetch_data_from_sql_query(
                connection, sql)
            if not ok:
                return {
                    self.OUTPUT_MSG: error_message,
                    self.OUTPUT: output_layers
                }

            feedback.pushInfo("## Remplissage de la table adresse.parcelle ##")
            sql = """
                INSERT INTO adresse.parcelle(id,commune, prefixe, section, numero,
                    contenance, arpente, geom)
                SELECT p.idu, p.nomcommune, p1.ccopre, p1.ccosec, p1.dnupla, p.contenance,
                CASE
                WHEN p1.ccoarp = 'A' THEN True
                ELSE False
                END as arpente,
                p.geom
                FROM {}.parcelle_info p, {}.parcelle p1
                WHERE p.geo_parcelle = p1.parcelle
                AND p.idu not in(select pa.id from adresse.parcelle pa)
            """.format(schema, schema)
            _, _, _, ok, error_message = fetch_data_from_sql_query(
                connection, sql)
            if not ok:
                return {
                    self.OUTPUT_MSG: error_message,
                    self.OUTPUT: output_layers
                }

        feedback.pushInfo(
            "## Mise à jour de id_parcelle dans adresse.point_adresse ##")
        sql = """
            UPDATE adresse.point_adresse pa
            SET id_parcelle = (SELECT p.fid FROM adresse.parcelle p
            WHERE ST_intersects(pa.geom, p.geom));
        """
        _, _, _, ok, error_message = fetch_data_from_sql_query(connection, sql)
        if not ok:
            return {self.OUTPUT_MSG: error_message, self.OUTPUT: output_layers}

        feedback.pushInfo("## CREATION DES VUES ##")
        feedback.pushInfo("## Vue  adresse.v_certificat ##")
        sql = "DROP VIEW IF EXISTS adresse.v_certificat"
        _, _, _, ok, error_message = fetch_data_from_sql_query(connection, sql)
        sql = """
            CREATE VIEW adresse.v_certificat AS
            SELECT row_number() over (order by c.commune_nom) as id_view,
            pr.proprietaire as id_prop, pa.id_point,
            c.insee_code, c.commune_nom, c.code_postal, c.adresse_mairie, c.maire,
            trim(coalesce(pr.dqualp, '')) || ' ' ||
            CASE WHEN trim(pr.dnomus) != trim(pr.dnomlp) THEN Coalesce( trim(pr.dnomus) ||
            '/' || trim(pr.dprnus) || ', née ', '' ) ELSE '' END ||
            trim(coalesce(pr.ddenom, ''))
            AS p_nom,
            ltrim(trim(coalesce(pr.dlign4, '')), '0') || trim(coalesce(pr.dlign5, '')) AS p_adresse,
            trim(coalesce(pr.dlign6, '')) as p_adresse2,
            pc.ccosec,
            pi.tex,
            pa.adresse_complete
            FROM adresse.commune c
            JOIN adresse.point_adresse pa ON pa.id_commune = c.id_com
            JOIN adresse.parcelle p ON p.fid = pa.id_parcelle
            JOIN {}.parcelle_info pi ON pi.idu = p.id
            JOIN {}.parcelle pc ON pc.parcelle = pi.geo_parcelle
            JOIN {}.proprietaire pr ON pr.dnupro = pc.dnupro;
        """.format(schema, schema, schema)
        _, _, _, ok, error_message = fetch_data_from_sql_query(connection, sql)
        if not ok:
            return {self.OUTPUT_MSG: error_message, self.OUTPUT: output_layers}

        feedback.pushInfo("## Vue  adresse.v_voie ##")
        sql = "DROP VIEW IF EXISTS adresse.v_voie"
        _, _, _, ok, error_message = fetch_data_from_sql_query(connection, sql)
        sql = """
            CREATE VIEW adresse.v_voie
            AS
            SELECT row_number() OVER (ORDER BY v.nom) AS id_view,
            v.id_voie,
            v.nom_complet,
            c.id_com,
            cc.insee_code
            FROM adresse.voie v,
            adresse.appartenir_com c,
            adresse.commune cc
            WHERE c.id_voie = v.id_voie AND c.id_com = cc.id_com;
        """
        _, _, _, ok, error_message = fetch_data_from_sql_query(connection, sql)
        if not ok:
            return {self.OUTPUT_MSG: error_message, self.OUTPUT: output_layers}

        feedback.pushInfo("## Vue  adresse.v_section ##")
        sql = "DROP VIEW IF EXISTS adresse.v_section"
        _, _, _, ok, error_message = fetch_data_from_sql_query(connection, sql)
        sql = """
            CREATE VIEW adresse.v_section
            AS
            SELECT row_number() OVER (ORDER BY s.tex) AS id_view,
            concat(c.ccodep, c.ccocom) AS insee,
            s.tex,
            cc.id_com
            FROM {}.commune c,
            {}.geo_section s,
            adresse.commune cc
            WHERE c.commune = s.geo_commune AND concat(c.ccodep, c.ccocom) = cc.insee_code::text;
        """.format(schema, schema)
        _, _, _, ok, error_message = fetch_data_from_sql_query(connection, sql)
        if not ok:
            return {self.OUTPUT_MSG: error_message, self.OUTPUT: output_layers}

        feedback.pushInfo("## Vue  adresse.v_parcelle ##")
        sql = "DROP VIEW IF EXISTS adresse.v_parcelle"
        _, _, _, ok, error_message = fetch_data_from_sql_query(connection, sql)
        sql = """
            CREATE VIEW adresse.v_parcelle
            as SELECT row_number() OVER (ORDER BY s.tex) AS id_view,
            concat(c.ccodep, c.ccocom) as insee,
            s.tex as section,
            cc.id_com,
            p.tex as parcelle
            FROM {}.commune c,
            {}.geo_section s,
            {}.parcelle_info p,
            adresse.commune cc
            WHERE c.commune = s.geo_commune AND concat(c.ccodep, c.ccocom) = cc.insee_code
            AND p.geo_section= s.geo_section
            ORDER BY p.tex;
        """.format(schema, schema, schema)
        _, _, _, ok, error_message = fetch_data_from_sql_query(connection, sql)
        if not ok:
            return {self.OUTPUT_MSG: error_message, self.OUTPUT: output_layers}

        uri = uri_from_name(connection)
        is_host = uri.host() != ""
        if is_host:
            feedback.pushInfo("Connexion établie via l'hote")
        else:
            feedback.pushInfo("Connexion établie via le service")
        feedback.pushInfo("")
        feedback.pushInfo("## CHARGEMENT DES COUCHES ##")
        for x in layers_name_none:
            if not context.project().mapLayersByName(x):
                result = self.initLayer(context, uri, 'adresse', x, None, "",
                                        layers_name_none[x])
                if not result:
                    feedback.pushInfo("La couche " + x +
                                      " ne peut pas être chargée")
                else:
                    feedback.pushInfo("La couche " + x + " a pu être chargée")
                    output_layers.append(result.id())

        msg = "success"

        return {self.OUTPUT_MSG: msg, self.OUTPUT: output_layers}
コード例 #11
0
    def processAlgorithm(self, parameters, context, feedback):
        """
        Here is where the processing itself takes place.
        """

        ### RETRIEVE PARAMETERS ###
        # Retrieve the input vector layer = study area
        study_area = self.parameterAsSource(parameters, self.STUDY_AREA,
                                            context)
        # Retrieve the output PostGIS layer name and format it
        layer_name = self.parameterAsString(parameters, self.OUTPUT_NAME,
                                            context)
        ts = datetime.now()
        format_name = "{} {}".format(layer_name,
                                     str(ts.strftime('%Y%m%d_%H%M%S')))
        # Retrieve the taxonomic rank
        taxonomic_ranks_labels = [
            "Groupe taxo", "Règne", "Phylum", "Classe", "Ordre", "Famille",
            "Groupe 1 INPN", "Groupe 2 INPN"
        ]
        taxonomic_ranks_db = [
            "groupe_taxo", "regne", "phylum", "classe", "ordre", "famille",
            "obs.group1_inpn", "obs.group2_inpn"
        ]
        taxonomic_rank_label = taxonomic_ranks_labels[self.parameterAsEnum(
            parameters, self.TAXONOMIC_RANK, context)]
        taxonomic_rank_db = taxonomic_ranks_db[self.parameterAsEnum(
            parameters, self.TAXONOMIC_RANK, context)]
        # Retrieve the taxons filters
        groupe_taxo = [
            self.db_variables.value('groupe_taxo')[i] for i in (
                self.parameterAsEnums(parameters, self.GROUPE_TAXO, context))
        ]
        regne = [
            self.db_variables.value('regne')[i]
            for i in (self.parameterAsEnums(parameters, self.REGNE, context))
        ]
        phylum = [
            self.db_variables.value('phylum')[i]
            for i in (self.parameterAsEnums(parameters, self.PHYLUM, context))
        ]
        classe = [
            self.db_variables.value('classe')[i]
            for i in (self.parameterAsEnums(parameters, self.CLASSE, context))
        ]
        ordre = [
            self.db_variables.value('ordre')[i]
            for i in (self.parameterAsEnums(parameters, self.ORDRE, context))
        ]
        famille = [
            self.db_variables.value('famille')[i]
            for i in (self.parameterAsEnums(parameters, self.FAMILLE, context))
        ]
        group1_inpn = [
            self.db_variables.value('group1_inpn')[i] for i in (
                self.parameterAsEnums(parameters, self.GROUP1_INPN, context))
        ]
        group2_inpn = [
            self.db_variables.value('group2_inpn')[i] for i in (
                self.parameterAsEnums(parameters, self.GROUP2_INPN, context))
        ]
        # Retrieve the datetime filter
        period = self.period_variables[self.parameterAsEnum(
            parameters, self.PERIOD, context)]
        # Retrieve the extra "where" conditions
        extra_where = self.parameterAsString(parameters, self.EXTRA_WHERE,
                                             context)
        # Retrieve the histogram parameter
        histogram_variables = [
            "Pas d'histogramme", "Nb de données", "Nb d'espèces",
            "Nb d'observateurs", "Nb de dates", "Nb de données de mortalité"
        ]
        histogram_option = histogram_variables[self.parameterAsEnum(
            parameters, self.HISTOGRAM_OPTIONS, context)]
        if histogram_option != "Pas d'histogramme":
            output_histogram = self.parameterAsFileOutput(
                parameters, self.OUTPUT_HISTOGRAM, context)
            if output_histogram == "":
                raise QgsProcessingException(
                    "Veuillez renseigner un emplacement pour enregistrer votre histogramme !"
                )

        ### CONSTRUCT "WHERE" CLAUSE (SQL) ###
        # Construct the sql array containing the study area's features geometry
        array_polygons = construct_sql_array_polygons(study_area)
        # Define the "where" clause of the SQL query, aiming to retrieve the output PostGIS layer = summary table
        where = "is_valid and is_present and ST_within(obs.geom, ST_union({}))".format(
            array_polygons)
        # Define a dictionnary with the aggregated taxons filters and complete the "where" clause thanks to it
        taxons_filters = {
            "groupe_taxo": groupe_taxo,
            "regne": regne,
            "phylum": phylum,
            "classe": classe,
            "ordre": ordre,
            "famille": famille,
            "obs.group1_inpn": group1_inpn,
            "obs.group2_inpn": group2_inpn
        }
        taxons_where = construct_sql_taxons_filter(taxons_filters)
        where += taxons_where
        # Complete the "where" clause with the datetime filter
        datetime_where = construct_sql_datetime_filter(self, period, ts,
                                                       parameters, context)
        where += datetime_where
        # Complete the "where" clause with the extra conditions
        where += " " + extra_where

        ### EXECUTE THE SQL QUERY ###
        # Retrieve the data base connection name
        connection = self.parameterAsString(parameters, self.DATABASE, context)
        # URI --> Configures connection to database and the SQL query
        uri = postgis.uri_from_name(connection)
        # Define the SQL query
        query = """WITH obs AS (
            SELECT obs.*
            FROM src_lpodatas.v_c_observations obs
            LEFT JOIN taxonomie.taxref t ON obs.taxref_cdnom = t.cd_nom
            WHERE {}),
        communes AS (
            SELECT DISTINCT obs.id_synthese, la.area_name
            FROM obs
            LEFT JOIN gn_synthese.cor_area_synthese cor ON obs.id_synthese = cor.id_synthese
            JOIN ref_geo.l_areas la ON cor.id_area = la.id_area
            WHERE la.id_type = (SELECT id_type FROM ref_geo.bib_areas_types WHERE type_code = 'COM')),
        total_count AS (
            SELECT COUNT(*) AS total_count
            FROM obs)
        SELECT row_number() OVER () AS id, COALESCE({}, 'Pas de correspondance taxref') AS "{}", {}
            COUNT(*) AS "Nb de données",
            ROUND(COUNT(*)::decimal/total_count, 4)*100 AS "Nb données / Nb données TOTAL (%)",
            COUNT(DISTINCT t.cd_ref) FILTER (WHERE t.id_rang='ES') AS "Nb d'espèces",
            COUNT(DISTINCT observateur) AS "Nb d'observateurs", 
            COUNT(DISTINCT date) AS "Nb de dates",
            SUM(CASE WHEN mortalite THEN 1 ELSE 0 END) AS "Nb de données de mortalité",
            max(nombre_total) AS "Nb d'individus max",
            min (date_an) AS "Année première obs", max(date_an) AS "Année dernière obs",
            string_agg(DISTINCT obs.nom_vern,', ') FILTER (WHERE t.id_rang='ES') AS "Liste des espèces",
            string_agg(DISTINCT com.area_name,', ') AS "Communes",
            string_agg(DISTINCT obs.source,', ') AS "Sources"
        FROM total_count, obs
        LEFT JOIN taxonomie.taxref t ON obs.taxref_cdnom=t.cd_nom
        LEFT JOIN communes com ON obs.id_synthese = com.id_synthese
        GROUP BY {}{}, total_count 
        ORDER BY {}{}""".format(
            where, taxonomic_rank_db, taxonomic_rank_label,
            'groupe_taxo AS "Groupe taxo", ' if taxonomic_rank_label
            in ['Ordre', 'Famille'] else "", "groupe_taxo, "
            if taxonomic_rank_label in ['Ordre', 'Famille'] else "",
            taxonomic_rank_db, "groupe_taxo, " if taxonomic_rank_label
            in ['Ordre', 'Famille'] else "", taxonomic_rank_db)
        #feedback.pushInfo(query)
        # Retrieve the boolean add_table
        add_table = self.parameterAsBool(parameters, self.ADD_TABLE, context)
        if add_table:
            # Define the name of the PostGIS summary table which will be created in the DB
            table_name = simplify_name(format_name)
            # Define the SQL queries
            queries = construct_queries_list(table_name, query)
            # Execute the SQL queries
            execute_sql_queries(context, feedback, connection, queries)
            # Format the URI
            uri.setDataSource(None, table_name, None, "", "id")
        else:
            # Format the URI with the query
            uri.setDataSource("", "(" + query + ")", None, "", "id")

        ### GET THE OUTPUT LAYER ###
        # Retrieve the output PostGIS layer = summary table
        layer_summary = QgsVectorLayer(uri.uri(), format_name, "postgres")
        # Check if the PostGIS layer is valid
        check_layer_is_valid(feedback, layer_summary)
        # Load the PostGIS layer
        load_layer(context, layer_summary)
        # Open the attribute table of the PostGIS layer
        iface.showAttributeTable(layer_summary)
        iface.setActiveLayer(layer_summary)

        ### CONSTRUCT THE HISTOGRAM ###
        if histogram_option != "Pas d'histogramme":
            plt.close()
            x_var = [
                (feature[taxonomic_rank_label]
                 if feature[taxonomic_rank_label] !=
                 'Pas de correspondance taxref' else 'Aucune correspondance')
                for feature in layer_summary.getFeatures()
            ]
            y_var = [
                int(feature[histogram_option])
                for feature in layer_summary.getFeatures()
            ]
            if len(x_var) <= 20:
                plt.subplots_adjust(bottom=0.5)
            elif len(x_var) <= 80:
                plt.figure(figsize=(20, 8))
                plt.subplots_adjust(bottom=0.3, left=0.05, right=0.95)
            else:
                plt.figure(figsize=(40, 16))
                plt.subplots_adjust(bottom=0.2, left=0.03, right=0.97)
            plt.bar(range(len(x_var)), y_var, tick_label=x_var)
            plt.xticks(rotation='vertical')
            plt.xlabel(self.taxonomic_ranks_variables[self.parameterAsEnum(
                parameters, self.TAXONOMIC_RANK, context)])
            plt.ylabel(histogram_option.replace("Nb", "Nombre"))
            plt.title('{} par {}'.format(
                histogram_option.replace("Nb", "Nombre"),
                taxonomic_rank_label[0].lower() +
                taxonomic_rank_label[1:].replace("taxo", "taxonomique")))
            if output_histogram[-4:] != ".png":
                output_histogram += ".png"
            plt.savefig(output_histogram)
            #plt.show()

        return {self.OUTPUT: layer_summary.id()}
コード例 #12
0
    def processAlgorithm(self, parameters, context, feedback):
        """
        Here is where the processing itself takes place.
        """

        ### RETRIEVE PARAMETERS ###
        # Retrieve the input vector layer = study area
        study_area = self.parameterAsSource(parameters, self.STUDY_AREA,
                                            context)
        # Retrieve the output PostGIS layer name and format it
        layer_name = self.parameterAsString(parameters, self.OUTPUT_NAME,
                                            context)
        format_name = "{} {}".format(layer_name,
                                     str(self.ts.strftime('%Y%m%d_%H%M%S')))
        # Retrieve the time interval
        time_interval = self.interval_variables[self.parameterAsEnum(
            parameters, self.TIME_INTERVAL, context)]
        # Retrieve the period
        start_year = self.parameterAsInt(parameters, self.START_YEAR, context)
        end_year = self.parameterAsInt(parameters, self.END_YEAR, context)
        if end_year < start_year:
            raise QgsProcessingException(
                "Veuillez renseigner une année de fin postérieure à l'année de début !"
            )
        # Retrieve the taxonomic rank
        taxonomic_rank = self.taxonomic_ranks_variables[self.parameterAsEnum(
            parameters, self.TAXONOMIC_RANK, context)]
        # Retrieve the aggregation type
        aggregation_type = 'Nombre de données'
        if taxonomic_rank == 'Groupes taxonomiques':
            aggregation_type = self.agg_variables[self.parameterAsEnum(
                parameters, self.AGG, context)]
        # Retrieve the taxons filters
        groupe_taxo = [
            self.db_variables.value('groupe_taxo')[i] for i in (
                self.parameterAsEnums(parameters, self.GROUPE_TAXO, context))
        ]
        regne = [
            self.db_variables.value('regne')[i]
            for i in (self.parameterAsEnums(parameters, self.REGNE, context))
        ]
        phylum = [
            self.db_variables.value('phylum')[i]
            for i in (self.parameterAsEnums(parameters, self.PHYLUM, context))
        ]
        classe = [
            self.db_variables.value('classe')[i]
            for i in (self.parameterAsEnums(parameters, self.CLASSE, context))
        ]
        ordre = [
            self.db_variables.value('ordre')[i]
            for i in (self.parameterAsEnums(parameters, self.ORDRE, context))
        ]
        famille = [
            self.db_variables.value('famille')[i]
            for i in (self.parameterAsEnums(parameters, self.FAMILLE, context))
        ]
        group1_inpn = [
            self.db_variables.value('group1_inpn')[i] for i in (
                self.parameterAsEnums(parameters, self.GROUP1_INPN, context))
        ]
        group2_inpn = [
            self.db_variables.value('group2_inpn')[i] for i in (
                self.parameterAsEnums(parameters, self.GROUP2_INPN, context))
        ]
        # Retrieve the extra "where" conditions
        extra_where = self.parameterAsString(parameters, self.EXTRA_WHERE,
                                             context)
        # Retrieve the histogram parameter
        add_histogram = self.parameterAsEnums(parameters, self.ADD_HISTOGRAM,
                                              context)
        if len(add_histogram) > 0:
            output_histogram = self.parameterAsFileOutput(
                parameters, self.OUTPUT_HISTOGRAM, context)
            if output_histogram == "":
                raise QgsProcessingException(
                    "Veuillez renseigner un emplacement pour enregistrer votre histogramme !"
                )

        ### CONSTRUCT "SELECT" CLAUSE (SQL) ###
        # Select data according to the time interval and the period
        select_data, x_var = construct_sql_select_data_per_time_interval(
            self, time_interval, start_year, end_year, aggregation_type,
            parameters, context)
        # Select species info (optional)
        select_species_info = """/*source_id_sp, */taxref_cdnom AS cd_nom, cd_ref, nom_rang as "Rang", groupe_taxo AS "Groupe taxo",
            obs.nom_vern AS "Nom vernaculaire", nom_sci AS "Nom scientifique\""""
        # Select taxonomic groups info (optional)
        select_taxo_groups_info = 'groupe_taxo AS "Groupe taxo"'
        ### CONSTRUCT "WHERE" CLAUSE (SQL) ###
        # Construct the sql array containing the study area's features geometry
        array_polygons = construct_sql_array_polygons(study_area)
        # Define the "where" clause of the SQL query, aiming to retrieve the output PostGIS layer = summary table
        where = "is_valid and is_present and ST_within(obs.geom, ST_union({}))".format(
            array_polygons)
        # Define a dictionnary with the aggregated taxons filters and complete the "where" clause thanks to it
        taxons_filters = {
            "groupe_taxo": groupe_taxo,
            "regne": regne,
            "phylum": phylum,
            "classe": classe,
            "ordre": ordre,
            "famille": famille,
            "obs.group1_inpn": group1_inpn,
            "obs.group2_inpn": group2_inpn
        }
        taxons_where = construct_sql_taxons_filter(taxons_filters)
        where += taxons_where
        # Complete the "where" clause with the extra conditions
        where += " " + extra_where
        ### CONSTRUCT "GROUP BY" CLAUSE (SQL) ###
        # Group by species (optional)
        group_by_species = "/*source_id_sp, */taxref_cdnom, cd_ref, nom_rang, nom_sci, obs.nom_vern, " if taxonomic_rank == 'Espèces' else ""

        ### EXECUTE THE SQL QUERY ###
        # Retrieve the data base connection name
        connection = self.parameterAsString(parameters, self.DATABASE, context)
        # URI --> Configures connection to database and the SQL query
        uri = postgis.uri_from_name(connection)
        # Define the SQL query
        query = """SELECT row_number() OVER () AS id, {}{}
            FROM src_lpodatas.v_c_observations obs
            LEFT JOIN taxonomie.taxref t ON obs.taxref_cdnom = t.cd_nom
            LEFT JOIN taxonomie.bib_taxref_rangs r ON t.id_rang = r.id_rang
            WHERE {}
            GROUP BY {}groupe_taxo
            ORDER BY groupe_taxo{}""".format(
            select_species_info if taxonomic_rank == 'Espèces' else
            select_taxo_groups_info, select_data, where, group_by_species,
            ", obs.nom_vern" if taxonomic_rank == 'Espèces' else "")
        #feedback.pushInfo(query)
        # Retrieve the boolean add_table
        add_table = self.parameterAsBool(parameters, self.ADD_TABLE, context)
        if add_table:
            # Define the name of the PostGIS summary table which will be created in the DB
            table_name = simplify_name(format_name)
            # Define the SQL queries
            queries = construct_queries_list(table_name, query)
            # Execute the SQL queries
            execute_sql_queries(context, feedback, connection, queries)
            # Format the URI
            uri.setDataSource(None, table_name, None, "", "id")
        else:
            # Format the URI with the query
            uri.setDataSource("", "(" + query + ")", None, "", "id")

        ### GET THE OUTPUT LAYER ###
        # Retrieve the output PostGIS layer = summary table
        layer_summary = QgsVectorLayer(uri.uri(), format_name, "postgres")
        # Check if the PostGIS layer is valid
        check_layer_is_valid(feedback, layer_summary)
        # Load the PostGIS layer
        load_layer(context, layer_summary)
        # Open the attribute table of the PostGIS layer
        iface.showAttributeTable(layer_summary)
        iface.setActiveLayer(layer_summary)

        ### CONSTRUCT THE HISTOGRAM ###
        if len(add_histogram) > 0:
            plt.close()
            y_var = []
            for x in x_var:
                y = 0
                for feature in layer_summary.getFeatures():
                    y += feature[x]
                y_var.append(y)
            if len(x_var) <= 20:
                plt.subplots_adjust(bottom=0.4)
            elif len(x_var) <= 80:
                plt.figure(figsize=(20, 8))
                plt.subplots_adjust(bottom=0.3, left=0.05, right=0.95)
            else:
                plt.figure(figsize=(40, 16))
                plt.subplots_adjust(bottom=0.2, left=0.03, right=0.97)
            plt.bar(range(len(x_var)), y_var, tick_label=x_var)
            plt.xticks(rotation='vertical')
            x_label = time_interval.split(' ')[1].title()
            if x_label[-1] != 's':
                x_label += 's'
            plt.xlabel(x_label)
            plt.ylabel(aggregation_type)
            plt.title('{} {}'.format(
                aggregation_type,
                time_interval[0].lower() + time_interval[1:]))
            if output_histogram[-4:] != ".png":
                output_histogram += ".png"
            plt.savefig(output_histogram)
            #plt.show()

        return {self.OUTPUT: layer_summary.id()}