基于 SpringBoot 和 PostGIS 的城市道路里程信息转换

文章介绍了基于 SpringBoot 和 PostGIS 处理城市道路里程信息的方案。针对直接使用 PostGIS 进行大规模空间查询导致的性能瓶颈（如耗时约 15 秒），提出了一种使用物理表进行结果缓存的优化策略。通过设计缓存表结构、JavaBean 映射以及 MyBatis Plus 实现数据写入，将复杂的实时计算结果预存至数据库。测试表明，该方案将查询响应时间从 15 秒降低至 0.059 秒，显著提升了系统性能。同时指出若数据频繁变动，需配合消息中间件或定时任务维护数据一致性。

月亮邮递员发布于 2026/3/28更新于 2026/4/163 浏览

一、PostGIS 的查询方案实现与分析

本节将首先对整体需求进行介绍，然后重点说明如何在 PostGIS 中进行数据查询实现，以及在实现过程中可能遇到的性能瓶颈。

1、需求描述

以湖南省的路网为例，OSM 数据已成功导入 PostGIS 中，存储在 biz_road_network 表中。

文章配图

表中大约存储了 17 万条道路信息。这里表示采集时点的道路信息，但我们需要更多维度的信息提取，例如查找湖南省各地市的高速道路里程长度、城市主干道的道路里程长度，甚至需要掌握步行路线的长度，这些应该如何去实现呢？

2、PostGIS 的实现方式

首先来看一下在 PostGIS 完全使用空间检索的方式如何实现。为了实现城市行政区划范围和道路查询，需要使用城市信息表和道路信息表，通过空间检索查询城市行政区划范围内的道路信息。查询 SQL 如下：

SELECT t1.city_code, MAX(t1.city_name) AS city_name, SUM(CASE WHEN r.fclass IN ('motorway', 'motorway_link') THEN ST_Length(r.geom::geography) ELSE 0 END) AS highway_length, SUM(CASE WHEN r.fclass IN ('trunk', 'trunk_link') THEN ST_Length(r.geom::geography) ELSE 0 END) AS trunk_length, SUM(CASE WHEN r.fclass IN ('primary', 'primary_link') THEN ST_Length(r.geom::geography) ELSE 0 END)  primary_length, (  r.fclass  (, )  ST_Length(r.geom::geography)   )  secondary_length, (  r.fclass  (, )  ST_Length(r.geom::geography)   )  tertiary_length, (  r.fclass  (, )  ST_Length(r.geom::geography)   )  residential_length, (  r.fclass  (, )  ST_Length(r.geom::geography)   )  service_length, (  r.fclass  (, , )  ST_Length(r.geom::geography)   )  pedestrian_length, (  r.fclass    ST_Length(r.geom::geography)   )  cycleway_length, (  r.fclass    ST_Length(r.geom::geography)   )  track_length, (  r.fclass  (, )  ST_Length(r.geom::geography)   )  steps_length, (ST_Length(r.geom::geography))  total_length  biz_road_network r  biz_city t1  ST_Contains(t1.geom, r.geom)  t1.province_code     t1.city_code   total_length ;

相关免费在线工具

Keycode 信息

查找任何按下的键的javascript键代码、代码、位置和修饰符。在线工具，Keycode 信息在线工具，online

Escape 与 Native 编解码

JavaScript 字符串转义/反转义；Java 风格 \uXXXX（Native2Ascii）编码与解码。在线工具，Escape 与 Native 编解码在线工具，online

JavaScript / HTML 格式化

使用 Prettier 在浏览器内格式化 JavaScript 或 HTML 片段。在线工具，JavaScript / HTML 格式化在线工具，online

JavaScript 压缩与混淆

Terser 压缩、变量名混淆，或 javascript-obfuscator 高强度混淆（体积会增大）。在线工具，JavaScript 压缩与混淆在线工具，online

加密/解密文本

使用加密算法（如AES、TripleDES、Rabbit或RC4）加密和解密文本明文。在线工具，加密/解密文本在线工具，online

Base64 字符串编码/解码

将字符串编码和解码为其 Base64 格式表示形式即可。在线工具，Base64 字符串编码/解码在线工具，online

/*==============================================================*/ /* Table: biz_urban_road_mileage_info */ /*==============================================================*/ create table biz_urban_road_mileage_info ( pk_id INT8 not null, parent_code VARCHAR(6) not null, city_code VARCHAR(6) not null, city_name VARCHAR(30) not null, highway_length NUMERIC not null default 0, trunk_length NUMERIC not null default 0, primary_length NUMERIC not null default 0, secondary_length NUMERIC not null default 0, tertiary_length NUMERIC not null default 0, residential_length NUMERIC not null default 0, service_length NUMERIC not null default 0, pedestrian_length NUMERIC not null default 0, cycleway_length NUMERIC not null default 0, track_length NUMERIC not null default 0, steps_length NUMERIC not null default 0, create_by VARCHAR(64) not null default '', create_time TIMESTAMP null, update_by VARCHAR(64) null default '', update_time TIMESTAMP null, constraint PK_BIZ_URBAN_ROAD_MILEAGE_INFO primary key (pk_id) ); comment on table biz_urban_road_mileage_info is '城市道路里程信息'; comment on column biz_urban_road_mileage_info.pk_id is '主键'; comment on column biz_urban_road_mileage_info.parent_code is '上级城市 code'; comment on column biz_urban_road_mileage_info.city_code is '城市 code'; comment on column biz_urban_road_mileage_info.city_name is '城市名称'; comment on column biz_urban_road_mileage_info.highway_length is '高速路里程'; comment on column biz_urban_road_mileage_info.trunk_length is '快速路里程'; comment on column biz_urban_road_mileage_info.primary_length is '主干道里程'; comment on column biz_urban_road_mileage_info.secondary_length is '次干道里程'; comment on column biz_urban_road_mileage_info.tertiary_length is '三级道路里程'; comment on column biz_urban_road_mileage_info.residential_length is '居住区道路里程'; comment on column biz_urban_road_mileage_info.service_length is '服务道路里程'; comment on column biz_urban_road_mileage_info.pedestrian_length is '步行街/步行区里程'; comment on column biz_urban_road_mileage_info.cycleway_length is '自行车道里程'; comment on column biz_urban_road_mileage_info.track_length is '轨道里程'; comment on column biz_urban_road_mileage_info.steps_length is '人行道里程'; comment on column biz_urban_road_mileage_info.create_by is '创建人'; comment on column biz_urban_road_mileage_info.create_time is '创建时间'; comment on column biz_urban_road_mileage_info.update_by is '修改人'; comment on column biz_urban_road_mileage_info.update_time is '修改时间';

package com.example.domain; import java.math.BigDecimal; import com.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import lombok.AllArgsConstructor; import lombok.Getter; import lombok.NoArgsConstructor; import lombok.Setter; import lombok.ToString; @TableName(value = "biz_urban_road_mileage_info") @NoArgsConstructor @AllArgsConstructor @Setter @Getter @ToString public class UrbanRoadMileageInfo extends BaseEntity { private static final long serialVersionUID = 294858775475029073L; @TableId(value = "pk_id") private Long pkId; // 主键 @TableField(value = "parent_code") private String parentCode; // 上级城市 code @TableField(value = "city_code") private String cityCode; // 城市 code @TableField(value = "city_name") private String cityName; // 城市名称 @TableField(value = "highway_length") private BigDecimal highwayLength = new BigDecimal(0); // 高速路里程 @TableField(value = "trunk_length") private BigDecimal trunkLength = new BigDecimal(0); // 快速路里程 @TableField(value = "primary_length") private BigDecimal primaryLength = new BigDecimal(0); // 主干道里程 @TableField(value = "secondary_length") private BigDecimal secondaryLength = new BigDecimal(0); // 次干道里程 @TableField(value = "tertiary_length") private BigDecimal tertiaryLength = new BigDecimal(0); // 三级道路里程 @TableField(value = "residential_length") private BigDecimal residentialLength = new BigDecimal(0); // 居住区道路里程 @TableField(value = "service_length") private BigDecimal serviceLength = new BigDecimal(0); // 服务道路里程 @TableField(value = "pedestrian_length") private BigDecimal pedestrianLength = new BigDecimal(0); // 步行街/步行区里程 @TableField(value = "cycleway_length") private BigDecimal cyclewayLength = new BigDecimal(0); // 自行车道里程 @TableField(value = "track_length") private BigDecimal trackLength = new BigDecimal(0); // 轨道里程 @TableField(value = "steps_length") private BigDecimal stepsLength = new BigDecimal(0); // 人行道里程 public UrbanRoadMileageInfo(String parentCode, String cityCode, String cityName, BigDecimal highwayLength, BigDecimal trunkLength, BigDecimal primaryLength, BigDecimal secondaryLength, BigDecimal tertiaryLength, BigDecimal residentialLength, BigDecimal serviceLength, BigDecimal pedestrianLength, BigDecimal cyclewayLength, BigDecimal trackLength, BigDecimal stepsLength) { super(); this.parentCode = parentCode; this.cityCode = cityCode; this.cityName = cityName; this.highwayLength = highwayLength; this.trunkLength = trunkLength; this.primaryLength = primaryLength; this.secondaryLength = secondaryLength; this.tertiaryLength = tertiaryLength; this.residentialLength = residentialLength; this.serviceLength = serviceLength; this.pedestrianLength = pedestrianLength; this.cyclewayLength = cyclewayLength; this.trackLength = trackLength; this.stepsLength = stepsLength; } }

package com.example.mapper; import java.util.List; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import lombok.Data; @Data public interface UrbanRoadMileageInfoMapper extends BaseMapper<UrbanRoadMileageInfo> { /** * - 查询省级以下地市路网长度 SQL */ static final String LIST_BYPROVINCE_SQL = "SELECT t1.city_code,MAX(t1.city_name) AS city_name," + " SUM(CASE WHEN r.fclass IN ('motorway', 'motorway_link') THEN ST_Length(r.geom::geography) ELSE 0 END) AS highway_length, " + "SUM(CASE WHEN r.fclass IN ('trunk', 'trunk_link') THEN ST_Length(r.geom::geography) ELSE 0 END) AS trunk_length, " + "SUM(CASE WHEN r.fclass IN ('primary', 'primary_link') THEN ST_Length(r.geom::geography) ELSE 0 END) AS primary_length, " + "SUM(CASE WHEN r.fclass IN ('secondary', 'secondary_link') THEN ST_Length(r.geom::geography) ELSE 0 END) AS secondary_length," + "SUM(CASE WHEN r.fclass IN ('tertiary', 'tertiary_link') THEN ST_Length(r.geom::geography) ELSE 0 END) AS tertiary_length, " + "SUM(CASE WHEN r.fclass IN ('residential', 'living_street') THEN ST_Length(r.geom::geography) ELSE 0 END) AS residential_length, " + "SUM(CASE WHEN r.fclass IN ('service', 'unclassified') THEN ST_Length(r.geom::geography) ELSE 0 END) AS service_length, " + "SUM(CASE WHEN r.fclass IN ('footway', 'pedestrian', 'path') THEN ST_Length(r.geom::geography) ELSE 0 END) AS pedestrian_length, " + "SUM(CASE WHEN r.fclass = 'cycleway' THEN ST_Length(r.geom::geography) ELSE 0 END) AS cycleway_length, " + "SUM(CASE WHEN r.fclass = 'track' THEN ST_Length(r.geom::geography) ELSE 0 END) AS track_length," + "SUM(CASE WHEN r.fclass in ('steps', 'footway') THEN ST_Length(r.geom::geography) ELSE 0 END) AS steps_length," + "SUM(ST_Length(r.geom::geography)) AS total_length " + "FROM biz_road_network r JOIN biz_city t1 ON ST_Contains(t1.geom, r.geom) WHERE t1.province_code = #{province_code} " + "GROUP BY t1.city_code ORDER BY total_length DESC "; @Select(LIST_BYPROVINCE_SQL) /** * - 根据省份 code 查询对应的地市路网长度 * @param provinceCode 省份 code * @return */ public List<UrbanRoadMileageInfo> getListByProvinceCode(@Param("province_code")String provinceCode); }

基于 SpringBoot 和 PostGIS 的城市道路里程信息转换

1、需求描述

2、PostGIS 的实现方式

更多推荐文章

相关免费在线工具

二、使用物理表进行结果缓存

1、物理表的设计

2、JavaBean 的设计

3、MybatisPlus 实现数据写入

4、数据库缓存表查询

三、总结

基于 SpringBoot 和 PostGIS 的城市道路里程信息转换

1、需求描述

2、PostGIS 的实现方式

微信扫一扫，关注极客日志

更多推荐文章

相关免费在线工具

二、使用物理表进行结果缓存

1、物理表的设计

2、JavaBean 的设计

3、MybatisPlus 实现数据写入

4、数据库缓存表查询

三、总结