08-PostGIS数据库
PostGIS数据库
概述
PostGIS是PostgreSQL数据库的空间扩展，提供了强大的空间数据存储、查询和分析功能，是企业级GIS应用的首选数据库方案。
数据库连接
连接参数模型
/**
* 数据库连接参数模型
*/
@Data
public class DbConnBaseModel implements Serializable {
private String dbtype = "postgis";
private String host;
private Integer port;
private String schema;
private String database;
private String user;
private String passwd;
}
GeoTools连接方式
/**
* 获取Postgis数据源参数
*/
private static Map<String, Object> getPostgisInfo(DbConnBaseModel dbConnBaseModel) {
Map<String, Object> params = new HashMap<>();
params.put("dbtype", dbConnBaseModel.getDbtype());
params.put("host", dbConnBaseModel.getHost());
params.put("port", dbConnBaseModel.getPort());
params.put("schema", dbConnBaseModel.getSchema());
params.put("database", dbConnBaseModel.getDatabase());
params.put("user", dbConnBaseModel.getUser());
params.put("passwd", dbConnBaseModel.getPasswd());
params.put("preparedStatements", true);
params.put("encode functions", true);
return params;
}
/**
* 获取Postgis数据源
*/
public static JDBCDataStore getPostgisDataStore(DbConnBaseModel dbConnBaseModel) {
Map<String, Object> params = getPostgisInfo(dbConnBaseModel);
return (JDBCDataStore) DataStoreFinder.getDataStore(params);
}
GDAL连接方式
/**
* 获取GDAL Postgis连接字符串
*/
public static String toGdalPostgisConnStr(DbConnBaseModel dbConnBaseModel) {
return "PG: host=" + dbConnBaseModel.getHost() +
" port=" + dbConnBaseModel.getPort() +
" dbname=" + dbConnBaseModel.getDatabase() +
" user=" + dbConnBaseModel.getUser() +
" password=" + dbConnBaseModel.getPasswd() +
" active_schema=" + dbConnBaseModel.getSchema();
}
数据读取
使用GeoTools读取
/**
* Postgis图层转换为WktLayer
*/
public static WktLayer fromPostGIS(DbConnBaseModel dbConnBaseModel,
String layerName, String attributeFilter, String spatialFilterWkt,
GisEngineType gisEngineType) {
gisEngineType = GisEngineType.getGisEngineType(gisEngineType);
if (gisEngineType == GisEngineType.GEOTOOLS) {
DataStore dataStore = PostgisUtil.getPostgisDataStore(dbConnBaseModel);
SimpleFeatureSource source = dataStore.getFeatureSource(layerName);
// 应用过滤条件
SimpleFeatureCollection simpleFeatureCollection =
GeotoolsUtil.filter(source, attributeFilter, spatialFilterWkt);
dataStore.dispose();
return fromSimpleFeatureCollection(simpleFeatureCollection);
}
return null;
}
使用GDAL读取
/**
* 使用GDAL读取PostGIS
*/
public static WktLayer fromPostGISWithGDAL(DbConnBaseModel dbConnBaseModel,
String layerName, String attributeFilter, String spatialFilterWkt) {
String connStr = PostgisUtil.toGdalPostgisConnStr(dbConnBaseModel);
return OgrUtil.layer2WktLayer(DataFormatType.POSTGIS, connStr,
layerName, attributeFilter, spatialFilterWkt);
}
数据写入
使用GeoTools写入
/**
* WktLayer转换为Postgis图层
*/
public static void toPostGIS(WktLayer wktLayer, DbConnBaseModel dbConnBaseModel,
String layerName, GisEngineType gisEngineType) {
wktLayer.check();
EsriUtil.excludeSpecialFields(wktLayer.getFields());
gisEngineType = GisEngineType.getGisEngineType(gisEngineType);
if (gisEngineType == GisEngineType.GEOTOOLS) {
SimpleFeatureCollection featureCollection = toSimpleFeatureCollection(wktLayer);
SimpleFeatureType simpleFeatureType = featureCollection.getSchema();
JDBCDataStore dataStore = PostgisUtil.getPostgisDataStore(dbConnBaseModel);
// 检查表是否存在，不存在则创建
if (!Arrays.asList(dataStore.getTypeNames()).contains(layerName)) {
SimpleFeatureTypeBuilder tb = new SimpleFeatureTypeBuilder();
tb.init(simpleFeatureType);
tb.setName(layerName);
dataStore.createSchema(tb.buildFeatureType());
}
// 构建字段映射
SimpleFeatureType featureType = dataStore.getSchema(layerName);
Map<String, String> fieldMap = buildFieldMapping(featureType, simpleFeatureType);
dataStore.dispose();
// 批量写入
batchWrite(dbConnBaseModel, layerName, featureCollection, fieldMap);
}
}
/**
* 批量写入数据
*/
private static void batchWrite(DbConnBaseModel dbConnBaseModel, String layerName,
SimpleFeatureCollection featureCollection, Map<String, String> fieldMap) {
List<SimpleFeature> features = new ArrayList<>();
try (FeatureIterator<SimpleFeature> iterator = featureCollection.features()) {
while (iterator.hasNext()) {
features.add(iterator.next());
}
}
int batchSize = 1000;
int count = features.size() / batchSize;
ExecutorService executorService = ThreadUtil.newExecutor(count);
for (int i = 0; i <= count; i++) {
List<SimpleFeature> subList;
if (i == count) {
subList = features.subList(i * batchSize, features.size());
} else {
subList = features.subList(i * batchSize, (i + 1) * batchSize);
}
executorService.execute(() -> {
writeBatch(dbConnBaseModel, layerName, subList, fieldMap);
});
}
executorService.shutdown();
executorService.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
}
/**
* 写入单批数据
*/
private static void writeBatch(DbConnBaseModel dbConnBaseModel, String layerName,
List<SimpleFeature> subList, Map<String, String> fieldMap) {
try {
DataStore ds = PostgisUtil.getPostgisDataStore(dbConnBaseModel);
Transaction transaction = new DefaultTransaction("create");
FeatureWriter<SimpleFeatureType, SimpleFeature> writer =
ds.getFeatureWriterAppend(layerName, transaction);
try {
for (SimpleFeature feature : subList) {
writer.hasNext();
SimpleFeature writefeature = writer.next();
writefeature.setDefaultGeometry(feature.getDefaultGeometry());
for (Map.Entry<String, String> kv : fieldMap.entrySet()) {
writefeature.setAttribute(kv.getKey(),
feature.getAttribute(kv.getValue()));
}
writer.write();
}
transaction.commit();
} catch (Exception e) {
transaction.rollback();
throw new RuntimeException(e);
} finally {
writer.close();
transaction.close();
ds.dispose();
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
使用GDAL写入
/**
* 使用GDAL写入PostGIS
*/
public static void toPostGISWithGDAL(WktLayer wktLayer,
DbConnBaseModel dbConnBaseModel, String layerName) {
Vector options = new Vector();
options.add("GEOMETRY_NAME=SHAPE");
options.add("FID=FID");
options.add("FID64=TRUE");
String path = PostgisUtil.toGdalPostgisConnStr(dbConnBaseModel);
OgrUtil.wktLayer2Layer4Postgis(DataFormatType.POSTGIS, dbConnBaseModel,
wktLayer, layerName, options);
}
GDAL批量写入优化
/**
* GDAL批量写入PostGIS（优化版）
*/
private static void wktLayer2Layer4Postgis(DataFormatType driverType, String path,
WktLayer wktLayer, String layerName) {
int batchSize = 1000;
int count = wktLayer.getFeatures().size() / batchSize;
ExecutorService executorService = ThreadUtil.newExecutor(count);
for (int j = 0; j <= count; j++) {
List<WktFeature> subList;
if (j == count) {
subList = wktLayer.getFeatures().subList(j * batchSize,
wktLayer.getFeatures().size());
} else {
subList = wktLayer.getFeatures().subList(j * batchSize,
(j + 1) * batchSize);
}
executorService.execute(() -> {
try {
wktFeatures2Layer(driverType, path, wktLayer.getFields(),
subList, layerName);
} catch (Exception e) {
throw new RuntimeException(e);
}
});
}
executorService.shutdown();
executorService.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
}
数据删除
/**
* 删除PostGIS指定图层的指定要素
*/
public static int deletePostgisFeatures(DbConnBaseModel dbConnBaseModel,
String layerName, String whereClause) {
JDBCDataStore dataStore = PostgisUtil.getPostgisDataStore(dbConnBaseModel);
Statement statement = dataStore.getConnection(Transaction.AUTO_COMMIT)
.createStatement();
String sql = String.format("DELETE FROM %s.%s",
dbConnBaseModel.getSchema(), layerName);
if (CharSequenceUtil.isNotBlank(whereClause)) {
sql += " WHERE " + whereClause;
}
int count = statement.executeUpdate(sql);
statement.close();
dataStore.dispose();
return count;
}
实践案例
案例1：Shapefile导入PostGIS
/**
* 将Shapefile导入PostGIS
*/
public void importShpToPostGIS(String shpPath, DbConnBaseModel dbConn,
String tableName) {
// 读取Shapefile
WktLayer layer = WktLayerConverter.fromShapefile(
shpPath, null, null, GisEngineType.GEOTOOLS);
// 写入PostGIS
WktLayerConverter.toPostGIS(layer, dbConn, tableName, GisEngineType.GEOTOOLS);
System.out.println("导入完成，共 " + layer.getFeatures().size() + " 条记录");
}
案例2：PostGIS导出Shapefile
/**
* 从PostGIS导出Shapefile
*/
public void exportPostGISToShp(DbConnBaseModel dbConn, String tableName,
String outputPath, String whereClause) {
// 读取PostGIS
WktLayer layer = WktLayerConverter.fromPostGIS(
dbConn, tableName, whereClause, null, GisEngineType.GEOTOOLS);
// 写入Shapefile
WktLayerConverter.toShapefile(layer, outputPath, GisEngineType.GEOTOOLS);
System.out.println("导出完成，共 " + layer.getFeatures().size() + " 条记录");
}
案例3：空间查询
/**
* 空间查询：找出指定区域内的要素
*/
public List<WktFeature> spatialQueryFromPostGIS(DbConnBaseModel dbConn,
String tableName, String boundaryWkt) {
// 使用空间过滤读取
WktLayer layer = WktLayerConverter.fromPostGIS(
dbConn, tableName, null, boundaryWkt, GisEngineType.GEOTOOLS);
return layer.getFeatures();
}
案例4：增量更新
/**
* 增量更新PostGIS数据
*/
public void incrementalUpdate(DbConnBaseModel dbConn, String tableName,
WktLayer newData, String keyField) {
// 获取新数据的Key列表
Set<String> newKeys = newData.getFeatures().stream()
.map(f -> {
Optional<WktFieldValue> opt = f.getFieldValues().stream()
.filter(v -> v.getField().getYwName().equals(keyField))
.findFirst();
return opt.map(v -> v.getValue().toString()).orElse("");
})
.collect(Collectors.toSet());
// 删除旧数据
String whereClause = keyField + " IN ('" + String.join("','", newKeys) + "')";
int deleted = PostgisUtil.deletePostgisFeatures(dbConn, tableName, whereClause);
System.out.println("删除旧数据 " + deleted + " 条");
// 写入新数据
WktLayerConverter.toPostGIS(newData, dbConn, tableName, GisEngineType.GEOTOOLS);
System.out.println("写入新数据 " + newData.getFeatures().size() + " 条");
}
案例5：数据同步
/**
* 同步两个PostGIS表的数据
*/
public void syncPostGISTables(DbConnBaseModel sourceDb, String sourceTable,
DbConnBaseModel targetDb, String targetTable, String whereClause) {
// 读取源数据
WktLayer layer = WktLayerConverter.fromPostGIS(
sourceDb, sourceTable, whereClause, null, GisEngineType.GEOTOOLS);
// 清空目标表
PostgisUtil.deletePostgisFeatures(targetDb, targetTable, null);
// 写入目标表
WktLayerConverter.toPostGIS(layer, targetDb, targetTable, GisEngineType.GEOTOOLS);
System.out.println("同步完成，共 " + layer.getFeatures().size() + " 条记录");
}
案例6：坐标转换后入库
/**
* 坐标转换后写入PostGIS
*/
public void transformAndImport(String shpPath, DbConnBaseModel dbConn,
String tableName, int targetWkid) {
// 读取Shapefile
WktLayer layer = WktLayerConverter.fromShapefile(
shpPath, null, null, GisEngineType.GEOTOOLS);
// 坐标转换
if (layer.getWkid() != targetWkid) {
layer = CrsUtil.reproject(layer, targetWkid);
}
// 写入PostGIS
WktLayerConverter.toPostGIS(layer, dbConn, tableName, GisEngineType.GEOTOOLS);
}
PostGIS SQL示例
虽然本教程主要使用Java API，但了解PostGIS的SQL语法也很有帮助：
空间查询
-- 点在面内查询
SELECT * FROM parcels
WHERE ST_Contains(shape, ST_GeomFromText('POINT(116.397 39.908)', 4490));
-- 缓冲区查询
SELECT * FROM points
WHERE ST_DWithin(shape, ST_GeomFromText('POINT(116.397 39.908)', 4490), 0.01);
-- 相交查询
SELECT * FROM buildings
WHERE ST_Intersects(shape, ST_GeomFromText('POLYGON(...)', 4490));
空间计算
-- 计算面积
SELECT id, ST_Area(shape) as area FROM parcels;
-- 计算缓冲区
SELECT id, ST_Buffer(shape, 100) as buffer FROM roads;
-- 计算交集
SELECT a.id, ST_Intersection(a.shape, b.shape) as intersection
FROM layer_a a, layer_b b
WHERE ST_Intersects(a.shape, b.shape);
坐标转换
-- 坐标转换
SELECT id, ST_Transform(shape, 4526) as shape_proj FROM parcels;
性能优化
1. 空间索引
-- 创建空间索引
CREATE INDEX idx_parcels_shape ON parcels USING GIST(shape);
2. 批量写入
使用多线程批量写入，每批1000条左右：
int batchSize = 1000;
ExecutorService executor = ThreadUtil.newExecutor(Runtime.getRuntime().availableProcessors());
3. 使用PreparedStatement
GeoTools连接参数中启用：
params.put("preparedStatements", true);
4. 合理使用过滤
优先使用数据库端过滤，减少数据传输量：
// 推荐：使用属性过滤
WktLayerConverter.fromPostGIS(dbConn, tableName, "TYPE='residential'", null, ...);
// 推荐：使用空间过滤
WktLayerConverter.fromPostGIS(dbConn, tableName, null, boundaryWkt, ...);
小结
本章介绍了PostGIS数据库的核心内容：
- 数据库连接：GeoTools和GDAL两种连接方式
- 数据读取：支持属性过滤和空间过滤
- 数据写入：批量写入提升性能
- 数据删除：按条件删除要素
- 性能优化：空间索引、批量操作、数据库端过滤
下一章将介绍国土TXT格式的处理方法。
浙公网安备 33010602011771号