最近公司有项目需要实现对PostgreSQL数据库中存储的地理几何元素进行增删查改以及空间的复合查询，前期做了一些调研，实现技术的从0到1，这里把过程与结果记录下来，提供参考，一起进步，一起讨论。

本博客主要讲解的内容是对数据库中相应数据表中的数据的增删改，关于PostgreSQL+PostGis安装，PostgreSQL+PostGis+地理几何(geometry)对象类型介绍，本文章不做过多介绍，这里介绍几篇文章，也是我前期调研的时候参考的文章。

1.W3CSchool关于PostgreSQL的介绍。内容包括PostgreSQL的介绍，PostGis介绍，几何信息介绍，以及如何在PostGreSQL数据库中创建表格，在表格中存储几何信息，以及一些PostGis常用的SQL函数；

2.PostgreSQL+PostGis+PgRouting的安装。关于安装网上的帖子很多且安装比较简单，重点介绍一下PgRouting实现最短路径规划。有两篇文章：

1.https://hogwartsrico.github.io/2016/07/14/Using-PostGIS-to-implement-the-shortestpath/index.html

2. https://blog.csdn.net/weixin_40184249/article/details/82970999

关于实现SpringBoot+MyBatis+PostgreSQL+PostGis实现地理几何元素查询因为涉及多个查询，这里另写了一篇文章，只讲解关于查询的问题。

一.代码的整体结构，pom.xml文件依赖以及application.properties文件的配置

1.代码整体结构

 

其中mapElement实体类定义如下：

package com.honorzhang.postgresql.model;

import com.fasterxml.jackson.databind.util.JSONPObject;
import lombok.Data;

import java.math.BigDecimal;

/**
 * @program: postgresql
 * @author: zgr
 * @create: 2019-07-01 14:35
 **/
@Data
public class MapElement {

    /**
     * 数据库自增主键
     */
    private Long id;

    /**
     * 地图元素的名称（医院，银行，警察局等）
     */
    private String name;


     //地图元素（医院，银行，警察局）以点的形式存储
    /**
     * 地图元素位置点的的纬度
     */
    private Double latitude;

    /**
     * 地图元素位置点的经度
     */
    private Double longitude;

    /**
     * 空间信息转换为字符串
     */
    private String geoStr;
}

2.pom文件对PostgreSQL依赖

<dependency>
      <groupId>postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>9.3-1102-jdbc4</version>
 </dependency>
<dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <scope>runtime</scope>
 </dependency>

3.application.properties配置文件

spring.datasource.url=jdbc:postgresql://10.0.4.121:5432/postgis_test?useSSL=false
spring.datasource.username=postgres
spring.datasource.password=123456
spring.datasource.driver-class-name=org.postgresql.Driver

spring.datasource.url=jdbc:postgresql://数据库地址:数据库服务端口号/数据库名称?参数配置。

2.数据库建表

1.在PostgreSQL的postgis_test数据库中建设数据表map_elements。

1.建表sql语句

create table public.map_elements(
 id serial PRIMARY KEY NOT NULL,
 name varchar(32),
 longitude real,
 latitude real
 );

--表说明
COMMENT ON TABLE public.map_elements IS '地图信息表';
--字段说明
COMMENT ON COLUMN public.map_elements.id IS '主键ID';
COMMENT ON COLUMN public.map_elements.name IS '地图元素名称';
COMMENT ON COLUMN public.map_elements.longitude IS '地图元素位置经度';
COMMENT ON COLUMN public.map_elements.latitude IS '地图元素位置经度';

2.添加地理几何信息列

select AddGeometryColumn('public', 'map_elements', 'element_location', 4326, 'POINT', 2)

添加几何信息列这一步如果报错AddGeometryColumn函数不存在，是因为安装的postgis并没有添加到本数据库，在sql运行工具中执行下面语句即可。

create extension postgis

在建立数据表中，PostgreSQL中主键自增的设置相比mySQL要麻烦一下，除了id的类型要设置为serial外，还需要运行一个sql语句

CREATE SEQUENCE map_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

alter table map_elements alter column id set default nextval('map_id_seq');

关于这个语句的解释这里不做赘述，基本上一目了然，如有不懂的烦请自己google搜索一下。

以上工作完成后数据库会有表的显示

建好的数据库如下图所示

3.实现对数据的增删改

 

只以增加数据数据为例对controller层，mapper层，service层以及mybatis的mapper xml都做介绍，数据的删查改只介绍controller层与mybatis的mapper xml层。

mybatis的mapper.xml配置文件。

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.honorzhang.postgresql.mapper.MapElementMapper">
    <resultMap id="MapElementMap" type="com.honorzhang.postgresql.model.MapElement">
        <id property="id" column="id"/>
        <result property="name" javaType="String" jdbcType="VARCHAR" column="name"/>
        <result property="latitude" javaType="double" jdbcType="REAL" column="latitude"/>
        <result property="longitude" javaType="double" jdbcType="REAL" column="longitude"/>
        <result property="geoStr" javaType="String" jdbcType="VARCHAR" column="element_location"/>
    </resultMap>

resultMap是数据库字段与java pojo字段的一个映射。

1.数据的增加

  controller层

/**
     * 添加地图元素
     * @param mapElement
     * @return 添加的地理元素信息
     */
    @PostMapping
    public MapElement addMapElement(@RequestBody MapElement mapElement){
        mapElement.setGeoStr(geometryToString(mapElement.getLongitude(), mapElement.getLatitude()));
        mapService.addMapElement(mapElement);
        Long id = mapElement.getId();
        return mapService.findById(id);
    }
geometryToString是一个把经纬度转换为字符串的函数，此处我把它作为一个单独的函数放在了controller层，实际项目开发中，可以视情况放在utils数据包中。
private String geometryToString(double longitude, double latitude){
        String geoStr = "POINT" + "(" + longitude + " " + latitude + ")";
        return geoStr;
    }

 mapper层（dao层）

/**
     * @param mapElement
     */
    void addMapElement(MapElement mapElement);

/**
     * @param id id
     * @return 依据id返回数据
     */
    MapElement findById(Long id);

 service层

   service接口

  

/**
     * @param mapElement
     */
    void addMapElement(MapElement mapElement);

/**
     * @param id id
     * @return 依据id返回数据
     */
    MapElement findById(Long id);

  service实现

    

@Override
    public void addMapElement(MapElement mapElement) {
        mapElementMapper.addMapElement(mapElement);
    }

@Override
    public MapElement findById(Long id) {
        return mapElementMapper.findById(id);
    }

mybatis mapper.xml配置文件中的sql语句

<insert id="addMapElement" parameterType="com.honorzhang.postgresql.model.MapElement" useGeneratedKeys="true" keyProperty="id">
        <selectKey keyProperty="id" resultType="Long" order="BEFORE">
            SELECT nextval('map_elements_id_seq'::regclass)
        </selectKey>
        insert into map_elements(name, longitude, latitude, element_location)
        values (#{name}, #{longitude}, #{latitude}, ST_GeomFromText(#{geoStr}, 4326))
    </insert>
<select id="findById" parameterType="Long" resultMap="MapElementMap">
        SELECT id, name, longitude, latitude,
            ST_AsGeoJson(element_location) as element_location

            FROM map_elements
            where id = #{id}
    </select>

ST_GeomFromText是PostGis自带的函数，用以把文本信息转换成对应的地理几何信息。ST_AsGeoJson是把几何地理信息转换成json字符串的函数。可以参考W3CSchool关于PostgreSQL的介绍。

在添加一条数据，不用传入id，id是主键自增的。数据添加完毕以后，需要依据本地数据的id得到添加的数据。

useGeneratedKeys="true" keyProperty="id"

这句话意思就是返回添加数据后自动生成的主键id。

<selectKey keyProperty="id" resultType="Long" order="BEFORE">
            SELECT nextval('map_elements_id_seq'::regclass)
        </selectKey>

这是PostgreSQL所特有的生成自增主键的方法，相比mySQL会麻烦一些。

测试结果

PostMan测试结果可以看到，在添加数据时，geostr为空，在controller层实现了把经纬度转换成相应的text信息，利用ST_GeomFromText函数生成了对应的地理几何信息存储入数据库。在查询返回结果的时候，利用ST_AsGeoJson函数，把几何信息转换成Json字符串。可以利用json函数取出字段对应的值。

2.数据删除

controller层

/**
     * @param id id
     * @return 是否删除成功
     */
    @DeleteMapping("/{id}")
    public Boolean deleteMapElement(@PathVariable Long id){
        Boolean deleteMapElementSuccess = true;
        try{
            mapService.deleteMapElement(id);
        }catch (Exception e){
            log.info("删除失败：" + e);
            deleteMapElementSuccess = false;
        }
        return deleteMapElementSuccess;
    }

myBatis的mapper.xml

!--        id删除-->
    <delete id="deleteMapElement" parameterType="Long">
        delete from map_elements where id = #{id}
    </delete>

测试结果

3.数据修改

controller层

/**
     * 数据更改
     * @param mapElement
     * @return 更改后的数据
     */
    @PutMapping()
    public MapElement updateMapElement(@RequestBody MapElement mapElement){
        mapElement.setGeoStr(geometryToString(mapElement.getLongitude(), mapElement.getLatitude()));
        mapService.updateMapElement(mapElement);
        Long id = mapElement.getId();
        return mapService.findById(id);
    }

myBatis的mapper.xml

<update id="updateMapElement"  parameterType="com.honorzhang.postgresql.model.MapElement" useGeneratedKeys="true" keyProperty="id" >
        UPDATE map_elements
        <trim prefix="set" suffixOverrides=",">
            <if test="name!=null">name=#{name},</if>
            <if test="longitude!=null">longitude=#{longitude},</if>
            <if test="latitude!=null">latitude=#{latitude},</if>
            <if test="geoStr!=null">element_location=ST_GeomFromText(#{geoStr}, 4326),</if>

        </trim>
        WHERE id=#{id}
    </update>

测试结果

总结

本文主要介绍了SpringBoot+MyBatis+PostgreSQL+PostGis实现地理几何元素的增删改，这部分其实前期需要了解的知识很多，必须了解的知识我已经放在了开头的参考文章中，如果不了解前期基础知识，这篇文章可能会读的比较费劲。

关于实现SpringBoot+MyBatis+PostgreSQL+PostGis实现地理几何元素查询因为涉及多个查询，这里另写了一篇文章，只讲解关于查询的问题。

本工程的全部代码已经放在GitHub上，需要按照自己实际，对配置文件做一定更改。

最后，如有不妥之处，还望指正，希望能一起进步。

Logo
开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

avatar 开放原子开发者工作坊

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

avatar 开放原子开发者工作坊

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

avatar 开放原子开发者工作坊