数据库集成

第十六章 数据库集成

16.1 数据库概述

16.1.1 QGIS支持的数据库

数据库	空间扩展	特点
PostgreSQL	PostGIS	功能最强大
SQLite	SpatiaLite	轻量级文件数据库
Oracle	Oracle Spatial	企业级
MS SQL Server	空间类型	Windows环境
GeoPackage	内置	现代文件格式
MySQL	空间扩展	Web应用常用

16.1.2 空间数据库优势

• 多用户并发访问
• 事务支持
• 空间索引
• SQL空间查询
• 数据完整性
• 版本管理

16.2 PostgreSQL/PostGIS

16.2.1 创建连接

GUI方式：

图层 > 数据源管理器 > PostgreSQL
点击"新建"

连接参数：

名称: my_postgis
主机: localhost
端口: 5432
数据库: gisdb
SSL模式: 禁用
认证: 用户名/密码

PyQGIS连接：

from qgis.core import QgsDataSourceUri, QgsVectorLayer

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "user", "password")
uri.setDataSource("public", "table_name", "geom", "", "gid")

layer = QgsVectorLayer(uri.uri(), "PostGIS Layer", "postgres")

16.2.2 加载数据

# 加载整个表
uri.setDataSource("schema", "table", "geometry_column", "", "primary_key")

# 加载带过滤条件
uri.setDataSource("schema", "table", "geom", "status='active'", "id")

# 使用SQL查询
uri.setDataSource("", "(SELECT * FROM table WHERE value > 100)", "geom", "", "id")

16.2.3 PostGIS空间函数

在QGIS中可以使用PostGIS函数：

-- 在虚拟图层或DB管理器中执行
SELECT 
    id,
    name,
    ST_Area(geom) as area,
    ST_Buffer(geom, 100) as buffered_geom
FROM my_table
WHERE ST_Intersects(geom, ST_MakeEnvelope(100, 30, 120, 45, 4326))

16.2.4 使用DB管理器

数据库 > DB管理器

功能：

• 浏览数据库结构
• 执行SQL查询
• 导入导出数据
• 管理表和索引

-- 在DB管理器中创建空间表
CREATE TABLE new_points (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326)
);

-- 创建空间索引
CREATE INDEX idx_new_points_geom ON new_points USING GIST(geom);

16.2.5 导入数据到PostGIS

使用DB管理器：

DB管理器 > 选择数据库 > 导入图层/文件

使用Processing：

result = processing.run("qgis:importintopostgis", {
    'INPUT': layer,
    'DATABASE': 'my_postgis',
    'SCHEMA': 'public',
    'TABLENAME': 'new_table',
    'PRIMARY_KEY': 'id',
    'GEOMETRY_COLUMN': 'geom',
    'ENCODING': 'UTF-8',
    'OVERWRITE': True,
    'CREATEINDEX': True
})

16.3 SpatiaLite

16.3.1 创建连接

from qgis.core import QgsDataSourceUri, QgsVectorLayer

# 加载SpatiaLite图层
uri = QgsDataSourceUri()
uri.setDatabase('/path/to/database.sqlite')
uri.setDataSource('', 'table_name', 'geometry')

layer = QgsVectorLayer(uri.uri(), 'SpatiaLite Layer', 'spatialite')

16.3.2 创建SpatiaLite数据库

from qgis.core import QgsVectorFileWriter

# 将图层保存为SpatiaLite
options = QgsVectorFileWriter.SaveVectorOptions()
options.driverName = 'SpatiaLite'
options.fileEncoding = 'UTF-8'

error = QgsVectorFileWriter.writeAsVectorFormatV2(
    layer,
    '/path/to/output.sqlite',
    QgsCoordinateTransformContext(),
    options
)

16.3.3 SpatiaLite空间查询

-- 空间查询示例
SELECT * FROM points
WHERE ST_Within(geometry, 
    (SELECT geometry FROM regions WHERE name = '北京'))

16.4 GeoPackage

16.4.1 创建GeoPackage

# 创建新的GeoPackage
options = QgsVectorFileWriter.SaveVectorOptions()
options.driverName = 'GPKG'
options.fileEncoding = 'UTF-8'

error = QgsVectorFileWriter.writeAsVectorFormatV2(
    layer,
    '/path/to/output.gpkg',
    QgsCoordinateTransformContext(),
    options
)

16.4.2 向GeoPackage添加图层

# 追加到现有GeoPackage
options = QgsVectorFileWriter.SaveVectorOptions()
options.driverName = 'GPKG'
options.actionOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
options.layerName = 'new_layer_name'

error = QgsVectorFileWriter.writeAsVectorFormatV2(
    layer,
    '/path/to/existing.gpkg',
    QgsCoordinateTransformContext(),
    options
)

16.4.3 GeoPackage管理

from osgeo import ogr

# 列出GeoPackage中的图层
ds = ogr.Open('/path/to/data.gpkg')
for i in range(ds.GetLayerCount()):
    layer = ds.GetLayerByIndex(i)
    print(layer.GetName())

# 删除图层
ds.DeleteLayer(layer_index)

16.5 Oracle Spatial

16.5.1 连接Oracle

uri = QgsDataSourceUri()
uri.setConnection("hostname", "1521", "database", "user", "password")
uri.setDataSource("SCHEMA", "TABLE", "GEOMETRY", "")

layer = QgsVectorLayer(uri.uri(), "Oracle Layer", "oracle")

16.5.2 Oracle特定功能

-- Oracle空间查询
SELECT * FROM my_table t
WHERE SDO_WITHIN_DISTANCE(t.geometry, 
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(116.4, 39.9, NULL), NULL, NULL),
    'distance=1000 unit=m') = 'TRUE'

16.6 MS SQL Server

16.6.1 连接SQL Server

# 连接字符串
uri = "MSSQL:server=hostname;database=dbname;tables=schema.tablename;UID=user;PWD=password"
layer = QgsVectorLayer(uri, "MSSQL Layer", "mssql")

16.6.2 SQL Server空间类型

SQL Server支持geometry和geography类型：

• geometry：平面坐标系
• geography：球面坐标系（经纬度）

16.7 虚拟图层

16.7.1 创建虚拟图层

图层 > 数据源管理器 > 虚拟图层

16.7.2 SQL查询

-- 虚拟图层SQL示例
SELECT 
    a.id,
    a.name,
    b.population,
    a.geometry
FROM cities a
LEFT JOIN statistics b ON a.city_id = b.city_id
WHERE b.year = 2023

16.7.3 PyQGIS创建虚拟图层

from qgis.core import QgsVectorLayer

# 定义查询
query = """
SELECT id, name, geometry
FROM (
    SELECT * FROM layer1
    UNION ALL
    SELECT * FROM layer2
)
"""

uri = f"?query={query}"
virtual_layer = QgsVectorLayer(uri, "Virtual", "virtual")

16.7.4 空间连接

-- 空间连接示例
SELECT 
    p.id,
    p.name,
    r.region_name,
    p.geometry
FROM points p
JOIN regions r ON ST_Within(p.geometry, r.geometry)

16.8 数据库事务

16.8.1 事务管理

# 开始事务
layer.startEditing()

# 添加要素
layer.addFeature(feature)

# 提交事务
if layer.commitChanges():
    print("提交成功")
else:
    print(f"提交失败: {layer.commitErrors()}")

# 或回滚
# layer.rollBack()

16.8.2 批量操作

# 使用编辑缓冲
layer.startEditing()

# 批量添加
features = []
for i in range(1000):
    f = QgsFeature()
    f.setGeometry(QgsGeometry.fromPointXY(QgsPointXY(i, i)))
    f.setAttributes([i, f"Point {i}"])
    features.append(f)

layer.addFeatures(features)
layer.commitChanges()

16.9 性能优化

16.9.1 空间索引

-- PostGIS
CREATE INDEX idx_geom ON table_name USING GIST(geom);

-- SpatiaLite
SELECT CreateSpatialIndex('table_name', 'geometry');

16.9.2 查询优化

# 使用空间过滤
request = QgsFeatureRequest()
request.setFilterRect(QgsRectangle(100, 30, 120, 45))

# 只获取需要的字段
request.setSubsetOfAttributes(['id', 'name'], layer.fields())

# 只获取几何
request.setFlags(QgsFeatureRequest.NoAttributes)

for feature in layer.getFeatures(request):
    # 处理
    pass

16.9.3 连接池

对于高并发应用，考虑使用连接池：

# 在应用级别管理连接
# QGIS会自动管理数据库连接

16.10 数据同步

16.10.1 离线编辑

from qgis.core import QgsOfflineEditing

offline = QgsOfflineEditing()

# 转换为离线
offline.convertToOfflineProject(
    '/path/to/offline.sqlite',
    [layer1, layer2],
    False,  # 仅选中要素
    QgsOfflineEditing.SpatiaLite
)

# 同步更改
offline.synchronize()

16.10.2 数据复制

# 从数据库复制到本地
result = processing.run("native:savefeatures", {
    'INPUT': db_layer,
    'OUTPUT': '/path/to/local.gpkg'
})

# 从本地导入到数据库
result = processing.run("qgis:importintopostgis", {
    'INPUT': local_layer,
    'DATABASE': 'connection_name',
    'SCHEMA': 'public',
    'TABLENAME': 'imported_table'
})

16.11 小结

本章详细介绍了QGIS的数据库集成功能：

关键要点：

1. 了解各种空间数据库的特点
2. 掌握PostGIS连接和查询
3. 熟练使用SpatiaLite和GeoPackage
4. 了解虚拟图层的SQL能力
5. 掌握数据库事务和性能优化

数据库集成是企业级GIS应用的基础。

---

上一章：第15章 插件开发指南

下一章：第17章 Web服务与OGC标准