基于SpringBoot和PostGIS的城市道路里程信息转换-以湖南省OSM数据为例

目录

前言

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

1、需求描述

2、PostGIS的实现方式

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

1、物理表的设计

2、JavaBean的设计

3、MybatisPlus实现数据写入

4、数据库缓存表查询

三、总结

前言

        随着城市化进程的加速，城市交通基础设施的管理和优化成为城市可持续发展的关键环节。城市道路作为交通网络的核心组成部分，其里程信息的准确获取和高效处理对于交通规划、智能交通系统建设以及城市资源分配等具有极其重要的意义。在众多地理空间数据源中，开放街道地图（OpenStreetMap，OSM）因其数据的开放性、高精度以及广泛的社区支持，成为城市道路数据获取的重要来源之一。近年来，随着地理信息系统（GIS）技术的不断发展，PostGIS作为一种强大的空间数据库扩展，为地理空间数据的存储、查询和分析提供了强大的支持。PostGIS能够处理复杂的地理空间数据，包括道路网络的拓扑结构和几何信息，这使得它成为处理OSM数据的理想选择。然而，PostGIS在处理大规模数据时，尤其是在实时求解城市道路里程信息时，面临着性能瓶颈。实时计算道路里程信息需要对大量的地理空间数据进行复杂的几何运算和网络分析，这不仅消耗大量的计算资源，还可能导致响应时间过长，难以满足实时应用的需求。

        为了克服这一挑战，我们提出了基于Spring Boot与PostGIS的城市道路里程信息转换研究方案。Spring Boot作为一种流行的Java基础框架，以其简洁的配置、高效的开发效率和强大的生态系统，为构建高性能的后端服务提供了坚实的基础。通过将Spring Boot与PostGIS相结合，我们能够构建一个高效、可扩展的城市道路里程信息处理系统。然而，仅依靠Spring Boot和PostGIS的直接结合仍然无法完全解决实时性能问题。因此，我们引入了数据缓存机制，通过预先对OSM数据进行处理和运算，并将结果缓存起来，从而在实际查询时能够快速返回结果，极大地提高了系统的响应速度。

        在之前的研究中，我们已经对OSM数据进行详细的分析和预处理，提取出城市道路的几何信息和属性信息，并将其导入到PostGIS数据库中。然后，利用PostGIS提供的空间分析功能，对道路数据进行拓扑构建和里程计算。为了提高查询效率，我们将计算得到的里程信息存储到数据库中，当用户发起查询请求时，系统首先从数据库中查找结果，如果数据库中不存在，则调用PostGIS进行实时计算，并将结果更新到数据库表中。

        通过本研究，我们期望能够为城市交通管理部门、智能交通系统开发者以及相关领域的研究人员提供一种高效、可靠的城市道路里程信息处理方法。这不仅有助于提升城市交通管理的效率和精度，还为基于地理空间数据的智能应用开发提供了新的思路和技术支持。在接下来的章节中，我们将详细介绍研究的背景、技术方法、实验结果以及未来的研究方向，以期为相关领域的研究和实践提供有价值的参考。

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

        本节将将以PostGIS为例，首先对整体的需求进行简单介绍，然后重点介绍如何在PostGIS中进行数据查询实现，在实现过程中又可能会遇到什么性能瓶颈。

1、需求描述

        在之前的内容我们我们以湖南省的路网为例，将OSM的数据已经成功导入了PostGIS中，存储在biz_road_***work表中。

        表中大约存储了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) 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_***work r 
JOIN biz_city t1 ON ST_Contains(t1.geom, r.geom)
and t1.province_code = '430000'
GROUP BY t1.city_code
ORDER BY total_length DESC;

        在Navicat客户端中执行以上sql之后，可以看到以下结果：

        可以看到本次查询耗时大约15秒，虽然这里设计比较大范围的空间计算，我们在相关表中也增加了空间索引，但是15秒的时间还是太长了，对性能有要求，还是需要进行优化。

        通过查询结果可以明显的看到，直接利用PostGIS进行空间查询的方式不可行，因为查询效率太低了。那么我们怎么来进行优化呢？下节将进行重点讲解。

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

        本节将基于上面的空间检索实现，重点介绍如何将临时计算结果缓存到数据库表中。正常情况下，道路的基本信息不发生改变的话，我们就不用每次都进行求解，需要查询数据时，只需要将数据从缓存中查询出来即可，这样性能就可以提高几个数量级。

1、物理表的设计

        为了将查询结果都能存储到缓存表中，这里我们根据查询情况设计一张数据缓存表。表物理结构如下：

为了方便大家参考，下面将直接提供物理表的SQL脚本：

/*==============================================================*/
/* 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)
);
***ment on table biz_urban_road_mileage_info is
'城市道路里程信息';
***ment on column biz_urban_road_mileage_info.pk_id is
'主键';
***ment on column biz_urban_road_mileage_info.parent_code is
'上级城市code';
***ment on column biz_urban_road_mileage_info.city_code is
'城市code';
***ment on column biz_urban_road_mileage_info.city_name is
'城市名称';
***ment on column biz_urban_road_mileage_info.highway_length is
'高速路里程';
***ment on column biz_urban_road_mileage_info.trunk_length is
'快速路里程';
***ment on column biz_urban_road_mileage_info.primary_length is
'主干道里程';
***ment on column biz_urban_road_mileage_info.secondary_length is
'次干道里程';
***ment on column biz_urban_road_mileage_info.tertiary_length is
'三级道路里程';
***ment on column biz_urban_road_mileage_info.residential_length is
'居住区道路里程';
***ment on column biz_urban_road_mileage_info.service_length is
'服务道路里程';
***ment on column biz_urban_road_mileage_info.pedestrian_length is
'步行街/步行区里程';
***ment on column biz_urban_road_mileage_info.cycleway_length is
'自行车道里程';
***ment on column biz_urban_road_mileage_info.track_length is
'轨道里程';
***ment on column biz_urban_road_mileage_info.steps_length is
'人行道里程';
***ment on column biz_urban_road_mileage_info.create_by is
'创建人';
***ment on column biz_urban_road_mileage_info.create_time is
'创建时间';
***ment on column biz_urban_road_mileage_info.update_by is
'修改人';
***ment on column biz_urban_road_mileage_info.update_time is
'修改时间';

2、JavaBean的设计

        JavaBean的设计比较简单，之前我们设计了创建人、创建时间、修改人、修改时间等四个字段，因此这里我们封装了基础父类，JavaBean类图设计如下：

        基础实体类的代码比较简单，如果需要分享可以在评论区留言或者发私信都可以。这里分享子类的代码实现：

package ***.yelang.project.extend.earthquake.domain;
import java.math.BigDecimal;
import ***.baomidou.mybatisplus.annotation.TableField;
import ***.baomidou.mybatisplus.annotation.TableId;
import ***.baomidou.mybatisplus.annotation.TableName;
import ***.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;// 上级城市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;
	}
}

        为了方便使用MybatisPlus来快速的将查询结果写入到数据库中，我们需要设计一个数据查询类，把上一节中涉及的SQL语句包装成查询方法，实现结果如下：

package ***.yelang.project.extend.earthquake.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import ***.baomidou.mybatisplus.core.mapper.BaseMapper;
import ***.yelang.project.extend.earthquake.domain.UrbanRoadMileageInfo;
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_***work 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);
}

3、MybatisPlus实现数据写入

        最后来编写数据导入实现代码，通过Mapper类将数据查询出来之后，再使用对应的Servcie将数据写入到数据库中。数据查询及写入方法如下：

package ***.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 ***.yelang.***mon.utils.DateUtils;
import ***.yelang.***mon.utils.StringUtils;
import ***.yelang.project.extend.earthquake.domain.UrbanRoadMileageInfo;
import ***.yelang.project.extend.earthquake.service.IUrbanRoadMileageInfoService;
/**
 * - 城市道路里程信息测试实例
 * @author 夜郎king
 *
 */
@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、数据库缓存表查询

        最后在数据库中查询验证一下，看是否成功的将数据写入到数据库中。在Navicat中执行以下查询语句即可：

select * from biz_urban_road_mileage_info;

        执行完成后，可以在数据库中看到以下查询结果：

        通过这种方式，我们的查询速度就提高了很多倍，大家可以根据自己的机器配置来计算具体的倍数。以我本机的机器性能为例，从15秒提升到了0.059秒，这个查询性能提升非常明显。

三、总结

        以上就是文本的主要内容，本文以SpringBoot和PostGIS空间数据管理和查询为例，引入了数据缓存机制，通过预先对OSM数据进行处理和运算，并将结果缓存起来，从而在实际查询时能够快速返回结果，极大地提高了系统的响应速度。通过具体的策略说明和技术代码实现，给大家提供一种提升性能解决思路。当然这种思路不是说就是十全十美的，因为我们假设OSM的数据不怎么改变，因此才有这种折中的解决方案，但是如果道路信息经常变化，我们可能就需要使用消息中间件或者定时任务来进行定时重建，以维持数据的一致性和准确性，具体实现大家可以先构想一下。行文仓促，定有不足之处，欢迎各位朋友在评论区批评指正，不胜感激。