基于PostGIS的GeoTools执行原生SQL查询制图实践-以贵州省行政区划及地级市驻地为例

目录

前言

一、空间相关表简介

1、地市行政区划表

2、地市驻地信息表

3、空间查询检索

二、GeoTools制图实现

1、数据类型绑定

2、WKT转Geometry

3、原生SQL转SimpleFeatureCollection

4、集成调用

5、成果预览

三、总结

---

前言

        在当今这个信息爆炸的时代，地理空间数据的处理与可视化分析对于众多领域都具有至关重要的意义。无论是城市规划、资源管理、环境保护，还是交通运输、灾害预警等方面，能够高效、准确地对地理空间数据进行操作并以直观的制图形式呈现，都为决策制定和相关研究提供了有力支持。贵州省作为一个地理环境独特、行政区划丰富的地区，其行政区划信息以及地级市驻地的空间分布等数据蕴含着大量的重要信息。传统上，对于此类地理空间数据的查询和制图往往依赖于一些专业的地理信息系统（GIS）软件，但这些软件通常存在操作复杂、成本较高、与其他系统集成困难等问题。随着开源 GIS 技术的不断发展，PostGIS 和 GeoTools 作为其中的佼佼者，为地理空间数据的处理提供了一种高效、灵活且成本较低的解决方案。

        本研究旨在探索基于 PostGIS 的 GeoTools 执行原生 SQL 查询制图的方法和技术流程。首先，对贵州省行政区划及地级市驻地的空间数据进行整理和导入到 PostGIS 中，确保数据的准确性和完整性。然后，深入研究 GeoTools 中如何构建原生 SQL 查询语句，以满足对特定地理空间数据的查询需求，例如查询特定地级市的行政区划范围、地级市驻地的位置及其周边地理要素等。在此基础上，利用 GeoTools 的渲染引擎和制图工具，将查询结果转化为直观的地理地图，通过调整符号化、图层叠加、地图布局等元素，使地图能够清晰地展示贵州省行政区划及地级市驻地的空间分布特征和相关地理信息。

        通过对这一实践过程的研究，期望能够为地理空间数据的处理和制图提供一种可借鉴的开源解决方案，降低对商业 GIS 软件的依赖，提高地理空间数据利用效率，同时也为相关领域的研究人员和从业者提供一种新的技术思路和方法，助力他们更高效地开展地理空间分析和决策支持工作。在接下来的章节中，将详细阐述整个实践过程中的技术细节、遇到的问题及解决方案，以及最终的制图成果和相应的分析。

一、空间相关表简介

        PostGIS 是 PostgreSQL 数据库的扩展，它为数据库提供了对地理空间数据的支持，使得空间数据能够在关系型数据库中得以高效存储、查询和管理。而 GeoTools 作为一个开源的 Java GIS 工具库，能够与 PostGIS 紧密集成，提供丰富的地理空间数据处理和制图功能。通过利用 GeoTools 执行原生 SQL 查询，我们可以充分发挥数据库的强大查询能力，同时结合 GeoTools 的可视化组件，实现对贵州省行政区划及地级市驻地的精准查询和高质量制图。在之前的博客中，曾经简单介绍了在Geotools中执行CQL和EQL的查询的实例，但是没有讲过如何在Geotools中直接执行原生SQL语句的例子。本节将具体介绍涉及的两张空间表，地市行政区划表和地市驻地信息表以及如何进行空间查询检索实践。

1、地市行政区划表

        地市行政区划的物理表结构如下图所示：

        物理结构如下，定义了相关的信息字段信息：

CREATE TABLE "public"."biz_city" ("id" int8 NOT NULL,"province_code" varchar(16) COLLATE "pg_catalog"."default" NOT NULL,"province_name" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,"city_code" varchar(16) COLLATE "pg_catalog"."default" NOT NULL,"city_name" varchar(512) COLLATE "pg_catalog"."default" NOT NULL,"type" varchar(32) COLLATE "pg_catalog"."default","geom" "public"."geometry",CONSTRAINT "pk_biz_city" PRIMARY KEY ("id")
);
CREATE INDEX "idx_biz_city_citycode" ON "public"."biz_city" USING btree ("city_code" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "idx_biz_city_pcode" ON "public"."biz_city" USING btree ("province_code" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
COMMENT ON COLUMN "public"."biz_city"."id" IS '主键ID';
COMMENT ON COLUMN "public"."biz_city"."province_code" IS '省份编码';
COMMENT ON COLUMN "public"."biz_city"."province_name" IS '省份名称';
COMMENT ON COLUMN "public"."biz_city"."city_code" IS '市级编码';
COMMENT ON COLUMN "public"."biz_city"."city_name" IS '实际名称';
COMMENT ON COLUMN "public"."biz_city"."type" IS '类型';
COMMENT ON COLUMN "public"."biz_city"."geom" IS 'geom';

2、地市驻地信息表

        地市信息表存储的是空间面数据，而地市驻地位置信息则表示为点数据，除了地市的城市驻地，其实还有区县的驻地位置。因此我们有一张完整的空间点位置表来存储以上的信息。表机构如下：

        对应的物理表模型结构如下所示：

CREATE TABLE "public"."biz_geographic_name" ("pk_id" int8 NOT NULL,"name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,"pinyin" varchar(255) COLLATE "pg_catalog"."default","classz" varchar(4) COLLATE "pg_catalog"."default","bz" varchar(100) COLLATE "pg_catalog"."default","slx" varchar(20) COLLATE "pg_catalog"."default","geom" "public"."geometry" NOT NULL,CONSTRAINT "pk_biz_geographic_name" PRIMARY KEY ("pk_id")
);
CREATE INDEX "idex_biz_geographic_name_classz" ON "public"."biz_geographic_name" USING btree ("classz" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "idx_biz_geographic_name_geom" ON "public"."biz_geographic_name" USING gist ("geom" "public"."gist_geometry_ops_2d"
);
COMMENT ON COLUMN "public"."biz_geographic_name"."pk_id" IS '主键id';
COMMENT ON COLUMN "public"."biz_geographic_name"."name" IS '地名';
COMMENT ON COLUMN "public"."biz_geographic_name"."pinyin" IS '汉语拼音';
COMMENT ON COLUMN "public"."biz_geographic_name"."classz" IS 'classz';
COMMENT ON COLUMN "public"."biz_geographic_name"."bz" IS '备注';
COMMENT ON COLUMN "public"."biz_geographic_name"."slx" IS 'slx';
COMMENT ON COLUMN "public"."biz_geographic_name"."geom" IS '空间对象';
COMMENT ON TABLE "public"."biz_geographic_name" IS '地名基础信息表，用于存储中国范围内的地名信息';

3、空间查询检索

        在PostGIS数据库中查询地市空间面信息的SQL查询语句比较简单，这里我们以贵州省为例，则需要查询贵州省对应的下属地级市列表，SQL语句如下：

select * from biz_city where province_code = '520000';

         其中，520000表示贵州省的编号，在省级也是这个编号。在数据库执行终端中执行以上语句后，可以得到以下结果：

        分别得到贵州省对应的9个地市的面数据列表。 有了面数据之后，接下来还要根据面数据查询对应的地市的地名点位置。这里需要使用空间包含函数以及两张表的联合查询，SQL语句如下：

select t.name,tc.city_code,st_asewkt(t.geom) AS geom from biz_geographic_name t,biz_city tc  where tc.province_code = '520000' and t.classz in ('AD','AC') and  st_contains(tc.geom, t.geom)

        执行后可以得到以下的查询结果：

        请注意以上两个查询SQL，尤其是第二个，在后面的生成过程中，主要就是需要在代码层面直接使用JDBC来访问空间数据库，然后获取相关的空间位置信息列表。 

二、GeoTools制图实现

        本节将详细阐述在GeoTools中实现贵州省的地市行政区划以及驻地的位置原生SQL查询。分别从数据类型的绑定、如何在GeoTools中实现WKT转换Geometry，其中可能又会遇到什么问题，接着介绍如何使用SQL来转SimpleFeatureCollection，最后将集成调用的过程以代码的形式展示给大家，最后给大家展示最终的效果。

1、数据类型绑定

        数据类型比较好理解，简单来说以Jdbc为例。我们的数据库表模型和实际的对象模型有一个映射关系，比如我们有一个学生表，有一个varchar(100)  name,int (4) age,varchar(200) adress;这样的一个表结构，与之对应的是有一个Student类，里面有一个String name,int age,String address;类似这样的代码，就是数据类型绑定，需要我们将对象的数据类型和物理表的数据类型进行一一对应。这是我们做数据转换的基础。在应用程序中做数据类型绑定的方法比较简单，如下所示：

private static Class<?> getBinding(String className) throws ClassNotFoundException {// 简化处理：常见类型映射if (className.contains("String")) return String.class;if (className.contains("Integer")) return Integer.class;if (className.contains("Double")) return Double.class;if (className.contains("geometry")) return Geometry.class; // PostGIS几何类型return Class.forName(className);
}

        这里尤其要注意的是Geometry的空间属性的映射关系。 

2、WKT转Geometry

        与其他的属性类型不一样的是，在空间数据库中，空间字段是一个比较麻烦的字段，但是在项目中又没办法不用，因此这里我们需要将执行的查询结果集当中获取到WKT，然后将WKT转换为Geometry的空间字段。

//转换WKT为Geometry字段
public static Geometry convertWKTWithSRID(String wkt) throws ParseException {GeometryFactory factory = new GeometryFactory();WKTReader reader = new WKTReader(factory);if (wkt.toUpperCase().startsWith("SRID=")) {int semicolonIndex = wkt.indexOf(';');if (semicolonIndex == -1) {throw new ParseException("Invalid SRID format");}String sridPart = wkt.substring(5, semicolonIndex);String geometryWKT = wkt.substring(semicolonIndex + 1);Geometry geom = reader.read(geometryWKT);geom.setSRID(Integer.parseInt(sridPart));return geom;}return reader.read(wkt);
}

        这里需要注意的是，在解析WKT文本时，一定需要将解析内容的SRID空间参考先去掉，然后在通过动态读取的方式来设置，否则程序在运行时就会报错。 有所怀疑的大家可以进行亲子验证。

3、原生SQL转SimpleFeatureCollection

        下面给出使用原生SQL作为输入，将结果转换为SimpleFeatureCollection的实例，核心方法如下：

 public static SimpleFeatureCollection executeSQL(String jdbcUrl, String user, String password, String sql) throws Exception {// 1. 建立数据库连接try (Connection conn = DriverManager.getConnection(jdbcUrl, user, password);Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(sql)) {// 2. 根据ResultSet元数据构建SimpleFeatureTypeResultSetMetaData meta = rs.getMetaData();SimpleFeatureTypeBuilder typeBuilder = new SimpleFeatureTypeBuilder();typeBuilder.setName("ResultType");for (int i = 1; i <= meta.getColumnCount(); i++) {String colName = meta.getColumnName(i);Class<?> binding = getBinding(meta.getColumnClassName(i));if(colName.equals("geom")) {//必须要设置空间类型，请注意空间函数的使用，否则样式标绘将不起作用typeBuilder.add("geom",  Geometry.class);//注册空间字段}else {typeBuilder.add(colName, binding);}}SimpleFeatureType featureType = typeBuilder.buildFeatureType();// 3. 转换结果集为FeatureCollectionListFeatureCollection collection = new ListFeatureCollection(featureType);SimpleFeatureBuilder featureBuilder = new SimpleFeatureBuilder(featureType);while (rs.next()) {for (int i = 1; i <= meta.getColumnCount(); i++) {Object value = rs.getObject(i);// 特殊处理几何对象（如WKB格式）if (meta.getColumnName(i).equals("geom")) {String wkt = value.toString(); value = convertWKTWithSRID(wkt);}featureBuilder.set(meta.getColumnName(i), value);}SimpleFeature feature = featureBuilder.buildFeature(null);collection.add(feature);}return collection;}
}

        在这里可以看到，在方法中，我们传入了数据库的连接信息和需要执行的SQL语句。这样做的好处就是可以直接在GeoTools中实现SQL的简单查询操作。 这里需要注意的就是在创建TypeBuilder时设置的字段的属性，一定要包含空间字段，否则在后期的样式绘制时就没有空间信息进行标绘。

4、集成调用

        集成调用的方法比较简单，主要就是传入相应的数据库连接信息，同时传入需要执行的空间表查询SQL，然后即可获取查询结果。

String jdbcUrl = "jdbc:postgresql://"+ PG_HOST +":"+ PG_PORT +"/" + PG_DB;
String execSql = " select t.name,tc.city_code,st_asewkt(t.geom) AS geom from biz_geographic_name t,biz_city tc  ";
execSql += " where tc.province_code = '520000' and t.classz in ('AD','AC') and  st_contains(tc.geom, t.geom) ";
//3、执行原生sql查询并转换为SimpleFeatureCollection
SimpleFeatureCollection features = executeSQL(jdbcUrl, PG_USER, PG_PASS, execSql);

5、成果预览

        关于如何生成预览图，。如何进行生成数据的标绘，这里不再进行详细的介绍。这里贴出一幅图，大家可以大致看到程序的运行调用情况，如下图所示：

        完成后，在预期的磁盘空间中可以看到以下的图片，说明数据加载成功，标绘成功。 

三、总结

        以上就是本文的主要内容，本研究旨在探索基于 PostGIS 的 GeoTools 执行原生 SQL 查询制图的方法和技术流程。首先，对贵州省行政区划及地级市驻地的空间数据进行整理和导入到 PostGIS 中，确保数据的准确性和完整性。然后，深入研究 GeoTools 中如何构建原生 SQL 查询语句，以满足对特定地理空间数据的查询需求，例如查询特定地级市的行政区划范围、地级市驻地的位置及其周边地理要素等。在此基础上，利用 GeoTools 的渲染引擎和制图工具，将查询结果转化为直观的地理地图，通过调整符号化、图层叠加、地图布局等元素，使地图能够清晰地展示贵州省行政区划及地级市驻地的空间分布特征和相关地理信息。

        针对于未来，可以做的工作还很多，需要优化的空间很大，比如在执行时我们需要使用连接池，而不是硬编码的方式进行执行。同时在进行数据映射绑定时，还可以采用其他的绑定框架。那么留一点题目，作为后续的系列内容讲解吧。行文仓促，定有不足之处，欢迎各位朋友在评论区批评指正，不胜感激。