SpringBoot + PostGIS 城市道路里程计算与缓存优化实战

在实际测试中，这条 SQL 的执行时间大约需要 15 秒。虽然我们在相关字段上建立了空间索引，但对于高并发的实时应用来说，这个延迟依然过高。因此，必须引入优化策略。

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

既然道路的基础拓扑结构不会频繁变动，我们可以将计算结果持久化到一张专门的缓存表中。这样，后续查询只需从这张小表中检索，性能将得到质的飞跃。

1、物理表的设计

我们需要设计一张表来存储各城市的各类道路里程汇总信息。表结构包含主键、城市编码、名称以及各类道路的数值字段。

/*==============================================================*/
/* 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 '修改时间';

2、JavaBean 的设计

为了配合 MybatisPlus 操作，我们需要定义对应的实体类。这里继承了一个基础实体类以复用创建人、时间等通用字段。

package com.yelang.project.extend.earthquake.domain;
import java.math.BigDecimal;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.yelang.framework.web.domain.BaseEntity;
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;
    @TableField(value = "city_code")
    private String cityCode;
    @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;
    }
}

为了方便调用，我们将上一节的空间查询 SQL 封装到 Mapper 接口中。

package com.yelang.project.extend.earthquake.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 com.yelang.project.extend.earthquake.domain.UrbanRoadMileageInfo;

public interface UrbanRoadMileageInfoMapper extends BaseMapper<UrbanRoadMileageInfo>{
    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)
    List<UrbanRoadMileageInfo> getListByProvinceCode(@Param("province_code")String provinceCode);
}

3、MybatisPlus 实现数据写入

有了 Mapper 之后，只需要编写一个 Service 或测试用例，将查询出的列表批量插入到缓存表中即可。

package com.yelang.project.urbanroadmileageinfo;
import java.util.Date;
import java.util.List;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.yelang.common.utils.DateUtils;
import com.yelang.common.utils.StringUtils;
import com.yelang.project.extend.earthquake.domain.UrbanRoadMileageInfo;
import com.yelang.project.extend.earthquake.service.IUrbanRoadMileageInfoService;

@SpringBootTest
@RunWith(SpringRunner.class)
public class UrbanRoadMileageInfoCase {
    @Autowired
    private IUrbanRoadMileageInfoService urbanRoadMileageInfoService;

    @Test
    public void testInit() {
        String provinceCode = "430000";
        List<UrbanRoadMileageInfo> list = urbanRoadMileageInfoService.getListByProvinceCode(provinceCode);
        System.out.println(list.size());
        Date now = DateUtils.getNowDate();
        for(UrbanRoadMileageInfo info : list) {
            info.setCreateTime(now);
            info.setParentCode(provinceCode);
            info.setUpdateTime(now);
            System.out.println(info);
        }
        if(StringUtils.isNotEmpty(list)) {
            urbanRoadMileageInfoService.saveBatch(list, 300);
        }
    }
}

运行上述测试用例即可完成数据的初始化写入。

4、数据库缓存表查询

最后验证一下数据是否正确入库。执行简单的 SELECT * 即可查看结果。

select * from biz_urban_road_mileage_info;

实测结果显示，数据已成功写入。更重要的是性能的提升：原本需要 15 秒的空间计算，现在仅需 0.059 秒即可返回结果。对于大多数业务场景，这种提升是决定性的。

三、总结

本方案通过 Spring Boot 与 PostGIS 的结合，利用物理表缓存机制有效解决了大规模地理空间数据的实时计算性能问题。核心流程包括 OSM 数据导入、空间 SQL 聚合统计、Java 实体映射及 MybatisPlus 批量写入。

当然，这种预计算方案有一个前提：路网数据相对稳定。如果道路信息频繁变更，就需要考虑引入消息中间件或定时任务来定期重建缓存表，以维持数据的一致性和准确性。希望这套思路能为类似的城市交通数据处理项目提供参考。