ImportSingleOSGBToPostGIS.py 8.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249
  1. __author__ = 'liying'
  2. __date__ = 'May 2025'
  3. __copyright__ = '(C) 2025, liying'
  4. import os
  5. import uuid
  6. from datetime import datetime
  7. import psycopg2
  8. import siwei_config
  9. from processing.tools.PostgreSQL.PostgreSQL import PostgreSQL
  10. from psycopg2 import sql
  11. from qgis.PyQt.QtCore import QCoreApplication
  12. from qgis.core import (
  13. QgsProcessingAlgorithm,
  14. QgsProcessingParameterFile,
  15. QgsProcessingParameterProviderConnection,
  16. QgsProcessingParameterDatabaseSchema,
  17. QgsProcessingParameterString,
  18. QgsProcessingParameterEnum,
  19. QgsProcessingParameterDateTime,
  20. QgsProcessingParameterBoolean,
  21. QgsProcessingParameterCrs
  22. )
  23. class ImportSingleOSGBToPostGIS(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('ImportSingleOSGBToPostGIS', 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(name=self.VECTOR_YWLX,
  81. description=self.tr('业务类型'), options=self.ywlxs,
  82. optional=False))
  83. rows = pgconn.getDeptList()
  84. self.depts = [row[0] for row in rows]
  85. self.addParameter(QgsProcessingParameterEnum(name=self.VECTOR_GLBM,
  86. description=self.tr('管理部门'), options=self.depts))
  87. rows = pgconn.getVectorZyml()
  88. self.zymls = [row[1] for row in rows]
  89. self.addParameter(QgsProcessingParameterEnum(name=self.VECTOR_ZYML,
  90. description=self.tr('资源目录'), options=self.zymls))
  91. self.addParameter(QgsProcessingParameterCrs(
  92. self.T_SRS,
  93. self.tr('指定入库坐标系'),
  94. defaultValue='EPSG:4326',
  95. optional=False
  96. ))
  97. self.addParameter(QgsProcessingParameterEnum(
  98. self.RASTER_T,
  99. self.tr('分块存储大小'),
  100. options=self.RASTER_T_LIST,
  101. defaultValue=0,
  102. optional=False
  103. ))
  104. self.addParameter(QgsProcessingParameterString(
  105. self.SOURCE_TYPE,
  106. self.tr('数据源类型'),
  107. defaultValue='osgb',
  108. optional=False
  109. ))
  110. self.addParameter(QgsProcessingParameterBoolean(
  111. self.Metadata_storage,
  112. self.tr('元数据入库'),
  113. defaultValue=True
  114. ))
  115. self.addParameter(QgsProcessingParameterBoolean(
  116. self.INDEX,
  117. self.tr('创建空间索引'),
  118. defaultValue=True
  119. ))
  120. def processAlgorithm(self, parameters, context, feedback):
  121. connection_name = self.parameterAsString(parameters, self.DATABASE, context)
  122. schema = self.parameterAsString(parameters, self.SCHEMA, context)
  123. table = self.parameterAsString(parameters, self.TABLE, context)
  124. input_dir = self.parameterAsString(parameters, self.INPUT_DIR, context)
  125. data_source = self.parameterAsString(parameters, self.VECTOR_SJLY, context)
  126. # 转换 QDateTime 为 Python date
  127. qdatetime = self.parameterAsDateTime(parameters, self.VECTOR_YEAR, context)
  128. data_year = qdatetime.date().toPyDate().year # 只保留年份(整数)
  129. business_type_index = self.parameterAsInt(parameters, self.VECTOR_YWLX, context)
  130. management_dept_index = self.parameterAsInt(parameters, self.VECTOR_GLBM, context)
  131. resource_catalog_index = self.parameterAsInt(parameters, self.VECTOR_ZYML, context)
  132. source_type = self.parameterAsString(parameters, self.SOURCE_TYPE, context)
  133. pgconn = PostgreSQL(schema='base')
  134. business_type = pgconn.getVectorYwlx()[business_type_index][0]
  135. management_dept = pgconn.getDeptList()[management_dept_index][0]
  136. resource_catalog = pgconn.getVectorZyml()[resource_catalog_index][1]
  137. query = f"SELECT bsm FROM t_vector_zyml WHERE name = '{resource_catalog}'"
  138. result = pgconn.execute(query)
  139. if result and len(result) > 0:
  140. resource_catalog = result[0][0]
  141. else:
  142. feedback.pushWarning(f"未找到名称为 {resource_catalog} 的BSM值")
  143. db_config = siwei_config.CONFIG['db']
  144. conn_params = {
  145. 'host': db_config['host'],
  146. 'port': db_config['port'],
  147. 'dbname': db_config['name'],
  148. 'user': db_config['user'],
  149. 'password': db_config['password'],
  150. "connect_timeout": 10,
  151. }
  152. conn = psycopg2.connect(**conn_params)
  153. cursor = conn.cursor()
  154. feedback.pushInfo("插入目录级元数据记录...")
  155. id = str(uuid.uuid4())
  156. nm = os.path.basename(input_dir)
  157. insert_sql = sql.SQL("""
  158. INSERT INTO {schema}.{table}
  159. (sjywz, rksj, year, ywlx, glbm, id, sjlx, xmlx, name, sjly)
  160. VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
  161. """).format(
  162. schema=sql.Identifier(schema),
  163. table=sql.Identifier(table)
  164. )
  165. cursor.execute(insert_sql, (
  166. input_dir,
  167. datetime.now().date(),
  168. data_year,
  169. business_type,
  170. management_dept,
  171. id,
  172. source_type,
  173. resource_catalog,
  174. nm,
  175. data_source
  176. ))
  177. conn.commit()
  178. feedback.pushInfo("扫描目录并插入单体模型记录...")
  179. for root, dirs, files in os.walk(input_dir):
  180. for file in files:
  181. if file.lower().endswith('.osgb'):
  182. full_path = os.path.join(root, file)
  183. model_id = str(uuid.uuid4())
  184. cursor.execute(insert_sql, (
  185. full_path,
  186. datetime.now().date(),
  187. data_year,
  188. business_type,
  189. management_dept,
  190. model_id,
  191. source_type,
  192. resource_catalog,
  193. file,
  194. data_source
  195. ))
  196. conn.commit()
  197. cursor.close()
  198. conn.close()
  199. feedback.pushInfo("所有单体模型路径已成功插入数据库。")
  200. return {}
  201. def name(self):
  202. return "importsingleosgbtopostgis"
  203. def displayName(self):
  204. return "单体三维模型入库"
  205. def group(self):
  206. return "三维数据工具"
  207. def groupId(self):
  208. return "osgbtools"
  209. def shortHelpString(self):
  210. return "扫描 OSGB 文件目录,将单体模型文件路径等元数据信息写入 PostGIS 数据库。"
  211. def createInstance(self):
  212. return ImportSingleOSGBToPostGIS()