sanya-dbms/processing/algs/gdal/rastertopostgislist.py

"""
***************************************************************************
    ogr2ogrtopostgislist.py
    ---------------------
    Date                 : November 2012
    Copyright            : (C) 2012 by Victor Olaya
    Email                : volayaf at gmail dot 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.                                   *
*                                                                         *
***************************************************************************
"""

__author__ = 'wanger'
__date__ = 'November 2024'
__copyright__ = '(C) 2024, wanger'

import os
import re
from PyQt5.QtGui import QIcon

from qgis._core import QgsProcessingParameterFile
from qgis.core import (QgsProcessing,
                       QgsProcessingParameterFeatureSource,
                       QgsProcessingParameterRasterLayer,
                       QgsProcessingParameterString,
                       QgsProcessingParameterDateTime,
                       QgsProcessingParameterEnum,
                       QgsProcessingParameterCrs,
                       QgsProcessingParameterField,
                       QgsProcessingParameterExtent,
                       QgsProcessingParameterBoolean,
                       QgsProcessingParameterProviderConnection,
                       QgsProcessingParameterDatabaseSchema,
                       QgsProcessingParameterDatabaseTable,
                       QgsProviderRegistry,
                       QgsProcessingException,
                       QgsCoordinateReferenceSystem,
                       QgsProviderConnectionException,
                       QgsDataSourceUri)

from processing.algs.gdal.GdalAlgorithm import GdalAlgorithm
from processing.algs.gdal.GdalUtils import GdalUtils
from processing.tools.PrintUtils import printStr
from processing.tools.PostgreSQL.PostgreSQL import PostgreSQL
from processing.tools.system import isWindows
from processing.tools.FileUtils import getInputFileName
from processing.tools.StringUtils import getConnectionStr

pluginPath = os.path.normpath(os.path.join(
    os.path.split(os.path.dirname(__file__))[0], os.pardir))


class Ogr2PostGisList(GdalAlgorithm):
    DATABASE = 'DATABASE'
    INPUT = 'INPUT'
    SHAPE_ENCODING = 'SHAPE_ENCODING'
    GTYPE = 'GTYPE'
    GEOMTYPE = ['', 'NONE', 'GEOMETRY', 'POINT', 'LINESTRING', 'POLYGON', 'GEOMETRYCOLLECTION', 'MULTIPOINT',
                'MULTIPOLYGON', 'MULTILINESTRING', 'CIRCULARSTRING', 'COMPOUNDCURVE', 'CURVEPOLYGON', 'MULTICURVE',
                'MULTISURFACE']
    S_SRS = 'S_SRS'
    T_SRS = 'T_SRS'
    A_SRS = 'A_SRS'
    HOST = 'HOST'
    PORT = 'PORT'
    USER = 'USER'
    DBNAME = 'DBNAME'
    PASSWORD = 'PASSWORD'
    SCHEMA = 'SCHEMA'
    TABLE = 'TABLE'
    PK = 'PK'
    PRIMARY_KEY = 'PRIMARY_KEY'
    GEOCOLUMN = 'GEOCOLUMN'
    DIM = 'DIM'
    DIMLIST = ['2', '3', '4']
    SIMPLIFY = 'SIMPLIFY'
    SEGMENTIZE = 'SEGMENTIZE'
    SPAT = 'SPAT'
    CLIP = 'CLIP'
    WHERE = 'WHERE'
    GT = 'GT'
    OVERWRITE = 'OVERWRITE'
    APPEND = 'APPEND'
    ADDFIELDS = 'ADDFIELDS'
    LAUNDER = 'LAUNDER'
    INDEX = 'INDEX'
    SKIPFAILURES = 'SKIPFAILURES'
    PRECISION = 'PRECISION'
    PROMOTETOMULTI = 'PROMOTETOMULTI'
    OPTIONS = 'OPTIONS'
    # TODO 三亚项目新追加入库属性
    VECTOR_YEAR = 'VECTOR_YEAR'
    VECTOR_XZQH = 'VECTOR_XZQH'
    VECTOR_XMLX = 'VECTOR_XMLX'
    VECTOR_SJLY = 'VECTOR_SJLY'
    VECTOR_YWLX = 'VECTOR_YWLX'
    SOURCE_TYPE = 'SOURCE_TYPE'
    VECTOR_GLBM = 'VECTOR_GLBM'
    VECTOR_ZYML = 'VECTOR_ZYML'
    VECTOR_NODATA = 'VECTOR_NODATA'
    XZQH_FIELD = 'XZQH_FIELD'
    Metadata_storage = 'Metadata_storage'
    selectedValue = "selectedValue"
    Raster_T = "Raster_T"
    Raster_T_List = ['不分块', '128', '256', '512']

    def __init__(self):
        super().__init__()

    def initAlgorithm(self, config=None):

        # self.addParameter(QgsProcessingParameterFile(self.INPUT,
        #                                              self.tr('栅格数据'),
        #                                              optional=False, fileFilter='Tif files (*.tif *.tiff)', ))
        self.addParameter(QgsProcessingParameterRasterLayer(self.INPUT,
                                                            self.tr('栅格数据'),
                                                            [QgsProcessing.TypeRaster]))
        db_param = QgsProcessingParameterProviderConnection(
            self.DATABASE,
            self.tr('数据库'), provider='postgres',
            defaultValue=self.pgcoon["host"])
        self.addParameter(db_param)
        schema_param = QgsProcessingParameterDatabaseSchema(
            self.SCHEMA,
            self.tr('模式'), defaultValue=self.pgcoon["schema"], connectionParameterName=self.DATABASE,
            optional=False)
        self.addParameter(schema_param)
        table_param = QgsProcessingParameterString(
            self.TABLE,
            self.tr('表名'), defaultValue="", optional=True)
        self.addParameter(table_param)
        # 数据来源
        self.addParameter(QgsProcessingParameterString(self.VECTOR_SJLY,
                                                       self.tr('数据来源'), "",
                                                       optional=False))

        self.addParameter(
            QgsProcessingParameterDateTime(self.VECTOR_YEAR, '数据时效', type=QgsProcessingParameterDateTime.Type.Date,
                                           defaultValue=None, optional=False))
        pgconn = PostgreSQL(schema='base')
        rows = pgconn.getVectorYwlx()
        self.ywlxs = []
        for row in rows:
            self.ywlxs.append(row[0])
        self.addParameter(QgsProcessingParameterEnum(name=self.VECTOR_YWLX,
                                                     description=self.tr('业务类型'), options=self.ywlxs,
                                                     optional=False))
        rows = pgconn.getDeptList()
        self.depts = []
        for row in rows:
            self.depts.append(row[0])
        self.addParameter(QgsProcessingParameterEnum(name=self.VECTOR_GLBM,
                                                     description=self.tr('管理部门'), options=self.depts))
        # self.addParameter(QgsProcessingParameterString(self.VECTOR_XMLX,
        #                                                self.tr('项目类型'), "",
        #                                                optional=False))
        rows = pgconn.getVectorZyml()
        self.zymls = []
        for row in rows:
            self.zymls.append(row[1])
        self.addParameter(QgsProcessingParameterEnum(name=self.VECTOR_ZYML,
                                                     description=self.tr('资源目录'), options=self.zymls))
        self.addParameter(QgsProcessingParameterString(self.VECTOR_NODATA,
                                                       self.tr('无效值'), "0,0,0,0,0,0",
                                                       optional=True))

        crs = QgsCoordinateReferenceSystem("EPSG:4525")
        crs_parameter = QgsProcessingParameterCrs(self.T_SRS,
                                                  self.tr('指定入库坐标系'),
                                                  defaultValue=crs,
                                                  optional=False)
        self.addParameter(crs_parameter)
        self.addParameter(QgsProcessingParameterEnum(self.Raster_T,
                                                     self.tr('分块存储大小，如栅格文件大于1G必须分块存储'),
                                                     options=self.Raster_T_List,
                                                     optional=False, defaultValue=3))
        string = QgsProcessingParameterString(self.SOURCE_TYPE,
                                                       self.tr('数据源类型'), "raster",
                                                       optional=False)
        # string.setFlags(QgsProcessingParameterString.FlagReadOnly)
        self.addParameter(string)
        metadata = QgsProcessingParameterBoolean(self.Metadata_storage,
                                                        self.tr('元数据入库'), defaultValue=True)
        # metadata.setEnable(False)
        self.addParameter(metadata)
        self.addParameter(QgsProcessingParameterBoolean(self.INDEX,
                                                        self.tr('不创建数据索引'), defaultValue=False))

        pgconn.close()

    def name(self):
        return 'importrasterintopostgisdatabase'

    def icon(self):
        return QIcon(os.path.join(pluginPath, 'images', 'dbms', 'importimage.png'))

    def displayName(self):
        return self.tr('栅格数据入库')

    def shortDescription(self):
        return self.tr('栅格数据入库')

    def tags(self):
        t = self.tr('import,into,postgis,database,raster').split(',')
        t.extend(super().tags())
        return t

    def group(self):
        return self.tr('数据入库工具')

    def groupId(self):
        return 'vector2postgis'

    def setSelectedValue(self, v):
        printStr(v)
        self.selectedValue = v

    def getSelectedValue(self):
        return self.selectedValue

    def getConsoleCommands(self, parameters, context, feedback, executing=True):
        connection_name = self.parameterAsConnectionName(parameters, self.DATABASE, context)
        if not connection_name:
            raise QgsProcessingException(
                self.tr('No connection specified'))

        try:
            md = QgsProviderRegistry.instance().providerMetadata('postgres')
            conn = md.createConnection(connection_name)
        except QgsProviderConnectionException:
            raise QgsProcessingException(
                self.tr('Could not retrieve connection details for {}').format(connection_name))

        uri = conn.uri()

        # 获取输入栅格图层
        raster_layer = self.parameterAsRasterLayer(parameters, self.INPUT, context)
        file = raster_layer.source()
        print(file)
        # file = self.parameterAsString(parameters, self.INPUT, context)

        tsrs = self.parameterAsCrs(parameters, self.T_SRS, context)
        table = self.parameterAsDatabaseTableName(parameters, self.TABLE, context)
        schema = self.parameterAsSchema(parameters, self.SCHEMA, context)
        nodata = self.parameterAsString(parameters, self.VECTOR_NODATA, context)

        arguments = []
        if tsrs.isValid():
            arguments.append('-s')
            arguments.append(GdalUtils.gdal_crs_string(tsrs).replace("EPSG:", ""))
        # 创建索引
        index = self.parameterAsBoolean(parameters, self.INDEX, context)
        if index == False:
            arguments.append('-I')
        # 分块存储
        t = self.parameterAsInt(parameters, self.Raster_T, context)
        if t > 0:
            arguments.append('-t')
            tv = self.Raster_T_List[t]
            arguments.append(tv + 'x' + tv)
        # raster2pgsql -s 4326 -t 256x256 -I -C -M D:\gisdata\HEBEI\HebeiDEM.tif  gis.tif | psql -h localhost -p 5432 -U postgres -d webgistest -W
        # nodata
        if nodata != "":
            arguments.append('-N {}'.format(nodata))
        arguments.append('-C -M')
        if len(table) == 0:
            table = f'{schema}.{getInputFileName(file)}'
        else:
            table = f'{schema}.{table}'

        arguments.append(f'"{file}"')
        arguments.append(f'"{table}"')

        # postgis相关
        arguments.append('|')
        arguments.append('psql')
        connection_parts = QgsDataSourceUri(uri).connectionInfo(executing).split(' ')
        print(connection_parts)

        arguments.append(f'-d {getConnectionStr(connection_parts, 0)}')
        arguments.append(f'-h {getConnectionStr(connection_parts, 1)}')
        arguments.append(f'-p {getConnectionStr(connection_parts, 2)}')
        arguments.append(f'-U {getConnectionStr(connection_parts, 3)}')
        # TODO wanger 配置postgresql数据库密码
        # TODO wanger -W表示需要再次输入密码
        # TODO wanger 不加则需要将pg密码配置到系统环境变量 PGPASSWORD
        # arguments.append('-W')
        # return ['ogrinfo D:\gisdata\HaiNanXZQ\XZQH3857.shp']
        if isWindows():
            return ['cmd.exe', '/C ', 'raster2pgsql.exe',
                    self.escapeAndJoin(arguments)]
        else:
            return ['raster2pgsql', self.escapeAndJoin(arguments)]
    def escapeAndJoin(self, strList):
        escChars = [' ', '&', '(', ')', '"', ';']
        joined = ''
        for s in strList:
            if not isinstance(s, str):
                s = str(s)
            # don't escape if command starts with - and isn't a negative number, e.g. -9999
            if s and re.match(r'^([^-]|-\d)', s) and any(c in s for c in escChars):
                escaped = s
                # escaped = '"' + s.replace('\\', '\\\\').replace('"', '"""') \
                #           + '"'
            else:
                escaped = s
            if escaped is not None:
                joined += escaped + ' '
        return joined.strip()

    def commandName(self):
        return "raster2pgsql"