ImportOSGBToPostGIS.py 8.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237
  1. __author__ = 'liying'
  2. __date__ = 'May 2025'
  3. __copyright__ = '(C) 2025, liying'
  4. import uuid
  5. import siwei_config
  6. from qgis.core import (
  7. QgsProcessingAlgorithm,
  8. QgsProcessingParameterFile,
  9. QgsProcessingParameterProviderConnection,
  10. QgsProcessingParameterDatabaseSchema,
  11. QgsProcessingParameterString,
  12. QgsProcessingParameterEnum,
  13. QgsProcessingParameterDateTime,
  14. QgsProcessingParameterBoolean,
  15. QgsProcessingParameterCrs,
  16. )
  17. from qgis.PyQt.QtCore import QCoreApplication
  18. import os
  19. import psycopg2
  20. from psycopg2 import sql
  21. from datetime import datetime
  22. from processing.tools.PostgreSQL.PostgreSQL import PostgreSQL
  23. class ImportOSGBToPostGIS(QgsProcessingAlgorithm):
  24. DATABASE = 'DATABASE'
  25. SCHEMA = 'SCHEMA'
  26. TABLE = 'TABLE'
  27. INPUT_DIR = 'INPUT_DIR'
  28. VECTOR_SJLY = 'VECTOR_SJLY'
  29. VECTOR_YEAR = 'VECTOR_YEAR'
  30. VECTOR_YWLX = 'VECTOR_YWLX'
  31. VECTOR_GLBM = 'VECTOR_GLBM'
  32. VECTOR_ZYML = 'VECTOR_ZYML'
  33. T_SRS = 'T_SRS'
  34. RASTER_T = 'RASTER_T'
  35. SOURCE_TYPE = 'SOURCE_TYPE'
  36. Metadata_storage = 'Metadata_storage'
  37. INDEX = 'INDEX'
  38. RASTER_T_LIST = ['不分块', '128', '256', '512']
  39. @staticmethod
  40. def tr(string):
  41. return QCoreApplication.translate('ImportOSGBToPostGIS', string)
  42. def initAlgorithm(self, config=None):
  43. self.addParameter(QgsProcessingParameterProviderConnection(
  44. self.DATABASE,
  45. self.tr('数据库连接'),
  46. 'postgres',
  47. defaultValue=siwei_config.CONFIG['db']['host']
  48. ))
  49. self.addParameter(QgsProcessingParameterDatabaseSchema(
  50. self.SCHEMA,
  51. self.tr('模式'),
  52. connectionParameterName=self.DATABASE,
  53. defaultValue='base'
  54. ))
  55. self.addParameter(QgsProcessingParameterString(
  56. self.TABLE,
  57. self.tr('表名'),
  58. defaultValue='t_vector_storage'
  59. ))
  60. self.addParameter(QgsProcessingParameterFile(
  61. self.INPUT_DIR,
  62. self.tr('OSGB 数据目录'),
  63. behavior=QgsProcessingParameterFile.Folder,
  64. optional=False
  65. ))
  66. self.addParameter(QgsProcessingParameterString(
  67. self.VECTOR_SJLY,
  68. self.tr('数据来源'),
  69. optional=False
  70. ))
  71. self.addParameter(QgsProcessingParameterDateTime(
  72. self.VECTOR_YEAR,
  73. self.tr('数据时效'),
  74. type=QgsProcessingParameterDateTime.Type.Date,
  75. optional=False
  76. ))
  77. pgconn = PostgreSQL(schema='base')
  78. rows = pgconn.getVectorYwlx()
  79. self.ywlxs = [row[0] for row in rows]
  80. self.addParameter(QgsProcessingParameterEnum(self.VECTOR_YWLX,
  81. self.tr('业务类型'),
  82. options=self.ywlxs,
  83. optional=False))
  84. rows = pgconn.getDeptList()
  85. self.depts = [row[0] for row in rows]
  86. self.addParameter(QgsProcessingParameterEnum(self.VECTOR_GLBM,
  87. self.tr('管理部门'),
  88. options=self.depts))
  89. rows = pgconn.getVectorZyml()
  90. self.zymls = [row[1] for row in rows]
  91. self.addParameter(QgsProcessingParameterEnum(self.VECTOR_ZYML,
  92. self.tr('资源目录'),
  93. options=self.zymls))
  94. self.addParameter(QgsProcessingParameterCrs(
  95. self.T_SRS,
  96. self.tr('指定入库坐标系'),
  97. defaultValue='EPSG:4326',
  98. optional=False
  99. ))
  100. self.addParameter(QgsProcessingParameterEnum(
  101. self.RASTER_T,
  102. self.tr('分块存储大小'),
  103. options=self.RASTER_T_LIST,
  104. defaultValue=0,
  105. optional=False
  106. ))
  107. self.addParameter(QgsProcessingParameterString(
  108. self.SOURCE_TYPE,
  109. self.tr('数据源类型'),
  110. defaultValue='osgb',
  111. optional=False
  112. ))
  113. self.addParameter(QgsProcessingParameterBoolean(
  114. self.Metadata_storage,
  115. self.tr('元数据入库'),
  116. defaultValue=True
  117. ))
  118. self.addParameter(QgsProcessingParameterBoolean(
  119. self.INDEX,
  120. self.tr('创建空间索引'),
  121. defaultValue=True
  122. ))
  123. def processAlgorithm(self, parameters, context, feedback):
  124. connection_name = self.parameterAsString(parameters, self.DATABASE, context)
  125. schema = self.parameterAsString(parameters, self.SCHEMA, context)
  126. table = self.parameterAsString(parameters, self.TABLE, context)
  127. input_dir = self.parameterAsString(parameters, self.INPUT_DIR, context)
  128. data_source = self.parameterAsString(parameters, self.VECTOR_SJLY, context)
  129. # 转换 QDateTime 为 Python date
  130. qdatetime = self.parameterAsDateTime(parameters, self.VECTOR_YEAR, context)
  131. data_year = qdatetime.date().toPyDate().year # 只保留年份(整数)
  132. business_type_index = self.parameterAsInt(parameters, self.VECTOR_YWLX, context)
  133. management_dept_index = self.parameterAsInt(parameters, self.VECTOR_GLBM, context)
  134. resource_catalog_index = self.parameterAsInt(parameters, self.VECTOR_ZYML, context)
  135. t_srs = self.parameterAsCrs(parameters, self.T_SRS, context)
  136. raster_t_index = self.parameterAsInt(parameters, self.RASTER_T, context)
  137. source_type = self.parameterAsString(parameters, self.SOURCE_TYPE, context)
  138. metadata_storage = self.parameterAsBoolean(parameters, self.Metadata_storage, context)
  139. create_index = self.parameterAsBoolean(parameters, self.INDEX, context)
  140. pgconn = PostgreSQL(schema='base')
  141. business_type = pgconn.getVectorYwlx()[business_type_index][0]
  142. management_dept = pgconn.getDeptList()[management_dept_index][0]
  143. resource_catalog_name = pgconn.getVectorZyml()[resource_catalog_index][1]
  144. # 查资源目录 bsm
  145. query = f"SELECT bsm FROM t_vector_zyml WHERE name = '{resource_catalog_name}'"
  146. result = pgconn.execute(query)
  147. if result and len(result) > 0:
  148. resource_catalog = result[0][0]
  149. else:
  150. feedback.reportError(f"未找到名称为 {resource_catalog_name} 的资源目录 BSM 值。")
  151. raise Exception(f"资源目录未找到:{resource_catalog_name}")
  152. # 连接数据库
  153. conn_params = {
  154. 'host': siwei_config.CONFIG['db']['host'],
  155. 'port': siwei_config.CONFIG['db']['port'],
  156. 'dbname': siwei_config.CONFIG['db']['name'],
  157. 'user': siwei_config.CONFIG['db']['user'],
  158. 'password': siwei_config.CONFIG['db']['password'],
  159. "connect_timeout": 10,
  160. }
  161. feedback.pushInfo("连接数据库...")
  162. conn = psycopg2.connect(**conn_params)
  163. cursor = conn.cursor()
  164. feedback.pushInfo("插入数据目录元数据...")
  165. id = str(uuid.uuid4())
  166. nm = os.path.basename(input_dir)
  167. insert_sql = sql.SQL("""
  168. INSERT INTO {schema}.{table}
  169. (sjywz, rksj, year, ywlx, glbm, id, sjlx, xmlx, name, sjly)
  170. VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
  171. """).format(
  172. schema=sql.Identifier(schema),
  173. table=sql.Identifier(table)
  174. )
  175. cursor.execute(insert_sql, (
  176. input_dir,
  177. datetime.now().date(),
  178. data_year,
  179. business_type,
  180. management_dept,
  181. id,
  182. source_type,
  183. resource_catalog,
  184. nm,
  185. data_source
  186. ))
  187. conn.commit()
  188. cursor.close()
  189. conn.close()
  190. feedback.pushInfo("数据目录元数据已成功插入。")
  191. return {}
  192. def name(self):
  193. return "importosgbtopostgis"
  194. def displayName(self):
  195. return "三维数据入库"
  196. def group(self):
  197. return "三维数据工具"
  198. def groupId(self):
  199. return "osgbtools"
  200. def shortHelpString(self):
  201. return "扫描 OSGB 文件目录,将路径、包围盒等元数据信息写入 PostGIS 数据库。"
  202. def createInstance(self):
  203. return ImportOSGBToPostGIS()