第09章 - 数据库空间数据访问
第09章 - 数据库空间数据访问
9.1 空间数据库概述
9.1.1 常用空间数据库
| 数据库 | 模块 | 特点 |
|---|---|---|
| PostGIS | gt-jdbc-postgis | 最流行的开源空间数据库 |
| Oracle Spatial | gt-jdbc-oracle | 企业级空间数据库 |
| SQL Server | gt-jdbc-sqlserver | 微软空间扩展 |
| H2GIS | gt-jdbc-h2gis | 嵌入式空间数据库 |
| SpatiaLite | gt-jdbc-spatialite | SQLite 空间扩展 |
| MySQL | gt-jdbc-mysql | MySQL 空间扩展 |
9.1.2 JDBCDataStore 体系
JDBCDataStore
│
├── PostGISDataStore
├── OracleDataStore
├── SQLServerDataStore
├── H2GISDataStore
└── MySQLDataStore
9.2 PostGIS 连接
9.2.1 基本连接
import org.geotools.api.data.DataStore;
import org.geotools.api.data.DataStoreFinder;
import org.geotools.data.postgis.PostgisNGDataStoreFactory;
public class PostGISConnection {
public static DataStore connect() throws Exception {
Map<String, Object> params = new HashMap<>();
// 必需参数
params.put("dbtype", "postgis");
params.put("host", "localhost");
params.put("port", 5432);
params.put("database", "gisdb");
params.put("schema", "public");
params.put("user", "postgres");
params.put("passwd", "password");
// 可选参数
params.put("Expose primary keys", true); // 暴露主键
params.put("encode functions", true); // 编码函数
params.put("preparedStatements", true); // 预编译语句
params.put("Loose bbox", true); // 宽松边界框
params.put("Estimated extends", false); // 估算范围
// 连接池参数
params.put("max connections", 20);
params.put("min connections", 5);
params.put("connection timeout", 20);
params.put("validate connections", true);
params.put("fetch size", 1000);
DataStore dataStore = DataStoreFinder.getDataStore(params);
if (dataStore == null) {
throw new RuntimeException("无法连接到 PostGIS 数据库");
}
return dataStore;
}
public static void main(String[] args) throws Exception {
DataStore store = connect();
try {
// 列出所有图层
String[] typeNames = store.getTypeNames();
System.out.println("数据库中的图层:");
for (String name : typeNames) {
System.out.println(" - " + name);
}
} finally {
store.dispose();
}
}
}
9.2.2 使用 JNDI 数据源
public class PostGISJNDI {
public static DataStore connectWithJNDI() throws Exception {
Map<String, Object> params = new HashMap<>();
params.put("dbtype", "postgis");
params.put("jndiReferenceName", "java:comp/env/jdbc/gisdb");
params.put("schema", "public");
return DataStoreFinder.getDataStore(params);
}
}
9.3 数据读取
9.3.1 读取表数据
public class PostGISReader {
public static void readTable(DataStore store, String tableName) throws Exception {
SimpleFeatureSource source = store.getFeatureSource(tableName);
// 获取 Schema
SimpleFeatureType schema = source.getSchema();
System.out.println("表名: " + schema.getTypeName());
System.out.println("几何字段: " + schema.getGeometryDescriptor().getLocalName());
System.out.println("CRS: " + schema.getCoordinateReferenceSystem());
// 属性列表
System.out.println("属性:");
for (AttributeDescriptor attr : schema.getAttributeDescriptors()) {
System.out.printf(" %s: %s%n",
attr.getLocalName(),
attr.getType().getBinding().getSimpleName());
}
// 统计信息
System.out.println("要素数量: " + source.getCount(Query.ALL));
System.out.println("边界: " + source.getBounds());
// 读取数据
SimpleFeatureCollection fc = source.getFeatures();
try (SimpleFeatureIterator iter = fc.features()) {
int count = 0;
while (iter.hasNext() && count < 5) {
SimpleFeature f = iter.next();
System.out.println(f.getID() + ": " + f.getDefaultGeometry());
count++;
}
}
}
}
9.3.2 空间查询
public class PostGISSpatialQuery {
public static void spatialQuery(DataStore store, String tableName) throws Exception {
SimpleFeatureSource source = store.getFeatureSource(tableName);
FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();
// 1. BBOX 查询
Filter bboxFilter = ff.bbox(
ff.property("geom"),
116.0, 39.0, 117.0, 40.0,
"EPSG:4326"
);
SimpleFeatureCollection bboxResult = source.getFeatures(bboxFilter);
System.out.println("BBOX 查询结果: " + bboxResult.size());
// 2. 相交查询
GeometryFactory gf = JTSFactoryFinder.getGeometryFactory();
Polygon queryPolygon = gf.createPolygon(new Coordinate[] {
new Coordinate(116.0, 39.0),
new Coordinate(117.0, 39.0),
new Coordinate(117.0, 40.0),
new Coordinate(116.0, 40.0),
new Coordinate(116.0, 39.0)
});
Filter intersectsFilter = ff.intersects(
ff.property("geom"),
ff.literal(queryPolygon)
);
SimpleFeatureCollection intersectResult = source.getFeatures(intersectsFilter);
System.out.println("相交查询结果: " + intersectResult.size());
// 3. 距离查询
Point center = gf.createPoint(new Coordinate(116.4, 39.9));
Filter dwithinFilter = ff.dwithin(
ff.property("geom"),
ff.literal(center),
0.1, // 距离
"degree" // 单位
);
SimpleFeatureCollection dwithinResult = source.getFeatures(dwithinFilter);
System.out.println("距离查询结果: " + dwithinResult.size());
// 4. 包含查询
Filter containsFilter = ff.contains(
ff.property("geom"),
ff.literal(center)
);
// 5. 组合空间和属性查询
Filter combinedFilter = ff.and(
bboxFilter,
ff.greater(ff.property("population"), ff.literal(1000000))
);
Query query = new Query(tableName, combinedFilter);
query.setPropertyNames("name", "population", "geom");
query.setMaxFeatures(100);
SimpleFeatureCollection combinedResult = source.getFeatures(query);
System.out.println("组合查询结果: " + combinedResult.size());
}
}
9.4 数据写入
9.4.1 创建表
public class PostGISWriter {
public static void createTable(DataStore store) throws Exception {
// 定义表结构
SimpleFeatureType schema = DataUtilities.createType(
"cities",
"geom:Point:srid=4326," +
"name:String," +
"population:Long," +
"area:Double," +
"country:String"
);
// 创建表
store.createSchema(schema);
System.out.println("表创建成功: " + schema.getTypeName());
}
public static void insertData(DataStore store, String tableName) throws Exception {
SimpleFeatureSource source = store.getFeatureSource(tableName);
if (!(source instanceof SimpleFeatureStore)) {
throw new Exception("不支持写入");
}
SimpleFeatureStore featureStore = (SimpleFeatureStore) source;
SimpleFeatureType schema = featureStore.getSchema();
// 创建要素
GeometryFactory gf = JTSFactoryFinder.getGeometryFactory();
SimpleFeatureBuilder builder = new SimpleFeatureBuilder(schema);
DefaultFeatureCollection collection = new DefaultFeatureCollection();
// 添加数据
Object[][] data = {
{gf.createPoint(new Coordinate(116.4074, 39.9042)), "北京", 21540000L, 16410.54, "中国"},
{gf.createPoint(new Coordinate(121.4737, 31.2304)), "上海", 24280000L, 6340.5, "中国"},
{gf.createPoint(new Coordinate(113.2644, 23.1291)), "广州", 15300000L, 7434.4, "中国"}
};
for (int i = 0; i < data.length; i++) {
builder.addAll(data[i]);
collection.add(builder.buildFeature("city." + (i + 1)));
}
// 使用事务
Transaction transaction = new DefaultTransaction("insert");
featureStore.setTransaction(transaction);
try {
List<FeatureId> ids = featureStore.addFeatures(collection);
transaction.commit();
System.out.println("插入了 " + ids.size() + " 条记录");
} catch (Exception e) {
transaction.rollback();
throw e;
} finally {
transaction.close();
}
}
}
9.4.2 更新和删除
public class PostGISUpdate {
public static void updateData(DataStore store, String tableName) throws Exception {
SimpleFeatureStore featureStore =
(SimpleFeatureStore) store.getFeatureSource(tableName);
FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();
Transaction transaction = new DefaultTransaction("update");
featureStore.setTransaction(transaction);
try {
// 更新单个属性
Filter filter = ff.equals(ff.property("name"), ff.literal("北京"));
featureStore.modifyFeatures("population", 22000000L, filter);
// 更新多个属性
featureStore.modifyFeatures(
new String[] {"population", "area"},
new Object[] {22000000L, 16500.0},
filter
);
// 更新几何
GeometryFactory gf = JTSFactoryFinder.getGeometryFactory();
Point newLocation = gf.createPoint(new Coordinate(116.41, 39.91));
featureStore.modifyFeatures("geom", newLocation, filter);
transaction.commit();
System.out.println("更新成功");
} catch (Exception e) {
transaction.rollback();
throw e;
} finally {
transaction.close();
}
}
public static void deleteData(DataStore store, String tableName) throws Exception {
SimpleFeatureStore featureStore =
(SimpleFeatureStore) store.getFeatureSource(tableName);
FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();
Transaction transaction = new DefaultTransaction("delete");
featureStore.setTransaction(transaction);
try {
// 按条件删除
Filter filter = ff.less(ff.property("population"), ff.literal(1000000));
featureStore.removeFeatures(filter);
transaction.commit();
System.out.println("删除成功");
} catch (Exception e) {
transaction.rollback();
throw e;
} finally {
transaction.close();
}
}
}
9.5 SQL 视图
9.5.1 创建 SQL 视图
public class PostGISSQLView {
public static void createSQLView(DataStore store) throws Exception {
if (store instanceof JDBCDataStore) {
JDBCDataStore jdbcStore = (JDBCDataStore) store;
// 定义 SQL 视图
String sql = "SELECT id, name, population, geom " +
"FROM cities " +
"WHERE population > 1000000";
// 创建虚拟表
VirtualTable vt = new VirtualTable("large_cities", sql);
// 设置主键
vt.setPrimaryKeyColumns(Arrays.asList("id"));
// 设置几何列
vt.addGeometryMetadatata("geom", Point.class, 4326);
// 注册视图
jdbcStore.createVirtualTable(vt);
// 使用视图
SimpleFeatureSource source = jdbcStore.getFeatureSource("large_cities");
System.out.println("视图要素数: " + source.getCount(Query.ALL));
}
}
}
9.5.2 参数化视图
public class ParameterizedView {
public static void createParameterizedView(JDBCDataStore store) throws Exception {
// 带参数的 SQL
String sql = "SELECT * FROM cities WHERE country = %country%";
VirtualTable vt = new VirtualTable("cities_by_country", sql);
// 定义参数
VirtualTableParameter param = new VirtualTableParameter(
"country", // 参数名
"中国", // 默认值
new RegexpValidator("^[\\w\\s]+$") // 验证器
);
vt.addParameter(param);
// 设置几何
vt.addGeometryMetadatata("geom", Point.class, 4326);
store.createVirtualTable(vt);
// 带参数查询
Query query = new Query("cities_by_country");
query.setHints(new Hints(Hints.VIRTUAL_TABLE_PARAMETERS,
Collections.singletonMap("country", "日本")));
SimpleFeatureSource source = store.getFeatureSource("cities_by_country");
SimpleFeatureCollection fc = source.getFeatures(query);
System.out.println("结果数: " + fc.size());
}
}
9.6 H2GIS 嵌入式数据库
9.6.1 H2GIS 连接
import org.geotools.data.h2.H2GISDataStoreFactory;
public class H2GISExample {
public static DataStore connectH2GIS(String dbPath) throws Exception {
Map<String, Object> params = new HashMap<>();
params.put("dbtype", "h2gis");
params.put("database", dbPath);
params.put("MVCC", true); // 多版本并发控制
return DataStoreFinder.getDataStore(params);
}
// 内存数据库
public static DataStore createInMemoryH2() throws Exception {
Map<String, Object> params = new HashMap<>();
params.put("dbtype", "h2gis");
params.put("database", "mem:testdb");
return DataStoreFinder.getDataStore(params);
}
public static void main(String[] args) throws Exception {
DataStore store = createInMemoryH2();
try {
// 创建表
SimpleFeatureType schema = DataUtilities.createType(
"points",
"geom:Point:srid=4326,name:String,value:Double"
);
store.createSchema(schema);
// 插入数据
SimpleFeatureStore featureStore =
(SimpleFeatureStore) store.getFeatureSource("points");
GeometryFactory gf = JTSFactoryFinder.getGeometryFactory();
SimpleFeatureBuilder builder = new SimpleFeatureBuilder(schema);
DefaultFeatureCollection fc = new DefaultFeatureCollection();
for (int i = 0; i < 100; i++) {
Point p = gf.createPoint(new Coordinate(
Math.random() * 360 - 180,
Math.random() * 180 - 90
));
builder.add(p);
builder.add("Point " + i);
builder.add(Math.random() * 100);
fc.add(builder.buildFeature(null));
}
featureStore.addFeatures(fc);
System.out.println("H2GIS 要素数: " + featureStore.getCount(Query.ALL));
} finally {
store.dispose();
}
}
}
9.7 连接池管理
9.7.1 配置连接池
public class ConnectionPoolConfig {
public static DataStore createWithPool() throws Exception {
Map<String, Object> params = new HashMap<>();
// 基本连接参数
params.put("dbtype", "postgis");
params.put("host", "localhost");
params.put("port", 5432);
params.put("database", "gisdb");
params.put("user", "postgres");
params.put("passwd", "password");
// 连接池配置
params.put("max connections", 50); // 最大连接数
params.put("min connections", 10); // 最小连接数
params.put("connection timeout", 30); // 连接超时（秒）
params.put("validate connections", true); // 验证连接
params.put("Max open prepared statements", 50); // 最大预编译语句数
// 测试查询（用于验证连接）
params.put("Test while idle", true);
params.put("Evictor run periodicity", 300); // 清理周期（秒）
return DataStoreFinder.getDataStore(params);
}
}
9.7.2 监控连接
public class ConnectionMonitor {
public static void monitorConnections(JDBCDataStore store) {
// 获取数据源
DataSource dataSource = store.getDataSource();
if (dataSource instanceof BasicDataSource) {
BasicDataSource bds = (BasicDataSource) dataSource;
System.out.println("连接池状态:");
System.out.println(" 活跃连接: " + bds.getNumActive());
System.out.println(" 空闲连接: " + bds.getNumIdle());
System.out.println(" 最大连接: " + bds.getMaxTotal());
System.out.println(" 最小空闲: " + bds.getMinIdle());
}
}
}
9.8 性能优化
9.8.1 批量操作
public class BatchOperations {
public static void batchInsert(DataStore store, String tableName,
List<SimpleFeature> features) throws Exception {
SimpleFeatureStore featureStore =
(SimpleFeatureStore) store.getFeatureSource(tableName);
int batchSize = 1000;
Transaction transaction = new DefaultTransaction("batch");
featureStore.setTransaction(transaction);
try {
for (int i = 0; i < features.size(); i += batchSize) {
int end = Math.min(i + batchSize, features.size());
List<SimpleFeature> batch = features.subList(i, end);
DefaultFeatureCollection fc = new DefaultFeatureCollection();
fc.addAll(batch);
featureStore.addFeatures(fc);
transaction.commit();
System.out.printf("已插入 %d/%d%n", end, features.size());
}
} catch (Exception e) {
transaction.rollback();
throw e;
} finally {
transaction.close();
}
}
}
9.8.2 使用空间索引
public class SpatialIndexUsage {
// 确保使用空间索引
public static void queryWithIndex(DataStore store, String tableName,
Envelope bbox) throws Exception {
SimpleFeatureSource source = store.getFeatureSource(tableName);
FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();
// BBOX 过滤器会使用空间索引
Filter bboxFilter = ff.bbox(
ff.property("geom"),
bbox.getMinX(), bbox.getMinY(),
bbox.getMaxX(), bbox.getMaxY(),
"EPSG:4326"
);
// 使用 Loose bbox 加速（允许稍大的边界框）
Query query = new Query(tableName, bboxFilter);
Hints hints = new Hints();
hints.put(Hints.LOOSEBBOX, true);
query.setHints(hints);
SimpleFeatureCollection fc = source.getFeatures(query);
System.out.println("查询结果: " + fc.size());
}
}
9.9 本章小结
本章详细介绍了数据库空间数据访问：
-
数据库连接
- PostGIS 连接配置
- JNDI 数据源
- H2GIS 嵌入式数据库
-
数据操作
- 读取和查询
- 空间查询
- 写入和更新
-
高级特性
- SQL 视图
- 参数化视图
- 连接池管理
-
性能优化
- 批量操作
- 空间索引
关键要点
- 正确配置连接池
- 使用事务保证数据一致性
- 利用空间索引提高查询性能
- 批量操作提高写入效率
浙公网安备 33010602011771号