Java(SpringBoot)利用Postgresql+Postgis进行空间地理信息分析（道路偏移，进出电子围栏等）

大鱼>

已于 2024-10-10 10:16:02 修改

阅读量651

点赞数 3

分类专栏： GIS IOT套件笔记物联网文章标签： postgresql 数据库 GIS分析

于 2024-10-10 10:09:15 首次发布

本文链接：https://blog.csdn.net/qq_17486399/article/details/142813514

版权

物联网同时被 3 个专栏收录

43 篇文章 16 订阅

订阅专栏

IOT套件笔记

29 篇文章 2 订阅

订阅专栏

GIS

3 篇文章 0 订阅

订阅专栏

1.前言

之前写过一篇相同的文章，里面代码是基于C#语言《C#利用Postgresql+Postgis进行空间地理信息分析（道路偏移，进出电子围栏等）》，之后有不少同学问到有没有Java语言的，去年有个项目刚好有封装。在这里记录一下。

2.环境准备

针对复杂的GIS空间分析，同样我们可以使用Postgresql+PostGIS，首先我们需要在服务器上初始化好环境。我使用的是Ubuntu，安装环境可参考：Ubuntu 安装Postgresql与PostGIS

3.引入依赖

我们可以使用JDBC连接Postgresql数据库

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>2.5.2</version>
        </dependency>

4.实现

4.1.在yml spring节点中增加postgresql的配置

  postgresql:
    driver-class-name: org.postgresql.Driver
    jdbc-url: jdbc:postgresql://localhost:5432/你的安装postgis插件后的数据库?public
    username: 你的用户名
    password: 你的密码

4.2.初始化配置数据源

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

/**
 * PostgreSql 数据源配置类
 * @author Mr.Li
 * @date 2023-05-09
 */
@Configuration
public class PostgreSqlConfiguration {
    @Bean("postgreSqlDataSource")
    @ConfigurationProperties(prefix = "spring.postgresql")
    public DataSource postgreSqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean("postgreSqlJdbcTemplate")
    public JdbcTemplate postgreSqlJdbcTemplate(@Qualifier("postgreSqlDataSource") DataSource postgreSqlDataSource) {
        return new JdbcTemplate(postgreSqlDataSource);
    }
}

4.3.封装GIS分析方法

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * GIS server
 * @author Mr.Li
 * @date 2023-05-10
 */
@Service
public class GisFunctionService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 判断两个经纬度点之间的距离
     * @param lonLat1 经纬度1
     * @param lonLat2 经纬度2
     * @return 直线距离（单位m）
     */
    public List<Map<String, Object>> getPointDistance(String lonLat1, String lonLat2){
        String sql = String.format("SELECT ST_Distance(ST_Transform(ST_SetSRID(ST_MakePoint(%s),4326), 26986) " +
                        ",ST_Transform(ST_SetSRID(ST_MakePoint(%s),4326), 26986)) as distance",
                lonLat1,lonLat2);
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * 查询点与点之间相距小于某个值的集合
     * @param lonLat 点
     * @param points 点集
     * @param distance 距离
     * @return
     */
    public List<Map<String, Object>> getPointsDWithin(String lonLat, List<String> points, int distance){
        StringBuilder sbSql= new StringBuilder();
        for (int i = 0; i < points.size(); i++) {
            if (i == 0) {
                sbSql.append(String.format("SELECT %s AS index, st_dwithin(ST_Transform(ST_SetSRID(ST_MakePoint(%s),4326), 26986)" +
                        ",ST_Transform(ST_SetSRID(ST_MakePoint(%s),4326), 26986),%s) AS status",i,lonLat,points.get(i),distance));
            }
            else {
                sbSql.append(String.format(" union all SELECT %s AS index, st_dwithin(ST_Transform(ST_SetSRID(ST_MakePoint(%s),4326), 26986)" +
                        ",ST_Transform(ST_SetSRID(ST_MakePoint(%s),4326), 26986),%s) AS status",i,lonLat,points.get(i),distance));
            }
        }
        return jdbcTemplate.queryForList(sbSql.toString());
    }

    /**
     * 查询点与点之间相距小于某个值的集合
     * @param lonLat 点
     * @param points 点集
     * @param distance 距离
     * @return
     */
    public List<Map<String, Object>> getPointsDWithin(String lonLat, List<String> points, List<Integer> distance){
        StringBuilder sbSql= new StringBuilder();
        for (int i = 0; i < points.size(); i++) {
            if (i == 0) {
                sbSql.append(String.format("SELECT %s AS index, st_dwithin(ST_Transform(ST_SetSRID(ST_MakePoint(%s),4326), 26986)" +
                        ",ST_Transform(ST_SetSRID(ST_MakePoint(%s),4326), 26986),%s) AS status",i,lonLat,points.get(i),distance.get(i)));
            }
            else {
                sbSql.append(String.format(" union all SELECT %s AS index, st_dwithin(ST_Transform(ST_SetSRID(ST_MakePoint(%s),4326), 26986)" +
                        ",ST_Transform(ST_SetSRID(ST_MakePoint(%s),4326), 26986),%s) AS status",i,lonLat,points.get(i),distance.get(i)));
            }
        }
        return jdbcTemplate.queryForList(sbSql.toString());
    }

    /**
     * 判断点是否在圆形围栏内
     * @param lonLat
     * @param points
     * @param distance
     * @return
     */
    public boolean isPointsDWithin(String lonLat, List<String> points, List<Integer> distance){
        List<Map<String, Object>> list=getPointsDWithin(lonLat, points, distance);
        if(list!=null&&list.size()>0){
            for(Map<String, Object> map:list){
                if(map.get("status").equals(true)){
                    return true;
                }
            }
            return false;
        }else{
            return false;
        }
    }

    /**
     * 判断某个经纬度点是否在某条路线上
     * @param lonLat 经纬度点
     * @param points 路线经纬度集合
     * @param buffer 缓冲区域，及在这个缓冲区域的点都可以判断为在这条路上，可以称作道路边缘到中心距离的宽度
     * @return
     */
    public List<Map<String, Object>> getPointOnTheLine(String lonLat, List<String> points, int buffer){
        StringBuilder sbSql= new StringBuilder();
        for (int i = 0; i < points.size(); i++) {
            sbSql.append(points.get(i).replace(',', ' ') + ",");
        }
        String strSql = String.format("select ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('LINESTRING(%s)')),%s))" +
                ",st_astext(geography(ST_GeomFromText('POINT(%s)')))) AS status", StringUtils.stripEnd(sbSql.toString(),","), buffer, lonLat.replace(',', ' '));
        return jdbcTemplate.queryForList(strSql);
    }

    /**
     * 判断某个经纬度点是否在这些路线上的其中一条
     * @param lonLat 经纬度点
     * @param lines 多条路线经纬度集合
     * @param buffer 缓冲区域，及在这个缓冲区域的点都可以判断为在这条路上，可以称作道路边缘到中心距离的宽度
     * @return
     */
    public List<Map<String, Object>> getPointOnOneLine(String lonLat, List<List<String>> lines, int buffer){
        StringBuilder sbLines= new StringBuilder();
        for(int lineIndex=0;lineIndex<lines.size();lineIndex++){
            List<String> points=lines.get(lineIndex);
            StringBuilder sbLine= new StringBuilder();
            for (int i = 0; i < points.size(); i++)
            {
                sbLine.append(points.get(i).replace(',', ' ') + ",");
            }
            sbLines.append(String.format("(%s),", StringUtils.stripEnd(sbLine.toString(),",")));
        }

        String strSql = String.format("select ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('MULTILINESTRING(%s)')),%s))" +
                ",st_astext(geography(ST_GeomFromText('POINT(%s)')))) AS status", StringUtils.stripEnd(sbLines.toString(),","), buffer, lonLat.replace(',', ' '));
        return jdbcTemplate.queryForList(strSql);
    }

    /**
     * 判断某个经纬度点所在路线的状态
     * @param lonLat
     * @param lines
     * @param buffer
     * @return
     */
    public List<Map<String, Object>> getPointOnLinesStatus(String lonLat, List<List<String>> lines, int buffer){
        StringBuilder sbSql = new StringBuilder();
        for(int lineIndex=0;lineIndex<lines.size();lineIndex++){
            List<String> points=lines.get(lineIndex);
            StringBuilder sbLine= new StringBuilder();
            for (int i = 0; i < points.size(); i++) {
                sbLine.append(points.get(i).replace(',', ' ') + ",");
            }
            if (lineIndex == 0) {
                sbSql.append(String.format("select %s AS index, ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('LINESTRING(%s)')),%s)) " +
                        ",st_astext(geography(ST_GeomFromText('POINT(%s)')))) AS status",lineIndex, StringUtils.stripEnd(sbLine.toString(),","),buffer,lonLat.replace(',', ' ')));
            }
            else {
                sbSql.append(String.format(" union all select %s AS index, ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('LINESTRING(%s)')),%s)) " +
                        ",st_astext(geography(ST_GeomFromText('POINT(%s)')))) AS status",lineIndex, StringUtils.stripEnd(sbLine.toString(),","),buffer,lonLat.replace(',', ' ')));
            }
        }
        return jdbcTemplate.queryForList(sbSql.toString());
    }

    /**
     * 判断某个经纬度点所在路线的状态
     * @param lonLat
     * @param lines
     * @param buffers
     * @return
     */
    public List<Map<String, Object>> getPointOnLinesStatusBuffers(String lonLat, List<List<String>> lines, List<Integer> buffers){
        StringBuilder sbSql = new StringBuilder();
        for(int lineIndex=0;lineIndex<lines.size();lineIndex++){
            List<String> points=lines.get(lineIndex);
            StringBuilder sbLine= new StringBuilder();
            for (int i = 0; i < points.size(); i++) {
                sbLine.append(points.get(i).replace(',', ' ') + ",");
            }
            if (lineIndex == 0) {
                sbSql.append(String.format("select %s AS index, ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('LINESTRING(%s)')),%s)) " +
                        ",st_astext(geography(ST_GeomFromText('POINT(%s)')))) AS status",lineIndex, StringUtils.stripEnd(sbLine.toString(),","),buffers.get(lineIndex),lonLat.replace(',', ' ')));
            }
            else {
                sbSql.append(String.format(" union all select %s AS index, ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('LINESTRING(%s)')),%s)) " +
                        ",st_astext(geography(ST_GeomFromText('POINT(%s)')))) AS status",lineIndex, StringUtils.stripEnd(sbLine.toString(),","),buffers.get(lineIndex),lonLat.replace(',', ' ')));
            }
        }
        return jdbcTemplate.queryForList(sbSql.toString());
    }

    /**
     * 判断某个经纬度点是否在这个区域内
     * @param lonLat 经纬度点
     * @param points 围栏点集
     * @return
     */
    public List<Map<String, Object>> getPointOnTheArea(String lonLat,List<String> points){
        StringBuilder sbSql= new StringBuilder();
        for (int i = 0; i < points.size(); i++) {
            sbSql.append(points.get(i).replace(',', ' ') + ",");
        }
        String strSql = String.format("SELECT ST_Contains(ST_MakePolygon(ST_GeomFromText('LINESTRING(%s)')) ,st_point(%s)) AS status"
                , StringUtils.stripEnd(sbSql.toString(),","), lonLat);
        return jdbcTemplate.queryForList(strSql);
    }

    /**
     * 判断点是否多个区域的状态
     * @param lonLat 经纬度点
     * @param areas 多个区域的经纬度集合
     * @return
     */
    public List<Map<String, Object>> getPointOnAreasStatus(String lonLat,List<List<String>> areas){
        StringBuilder sbSql = new StringBuilder();
        for (int i = 0; i < areas.size(); i++)
        {
            StringBuilder sbArea = new StringBuilder();
            List<String> points=areas.get(i);
            for (int j = 0; j < points.size(); j++) {
                sbArea.append(points.get(j).replace(',', ' ') + ",");
            }
            if (i == 0) {
                sbSql.append(String.format("SELECT %s AS index, ST_Contains( ST_MakePolygon(ST_GeomFromText('LINESTRING(%s)')) " +
                        ",st_point(%s)) AS status",i, StringUtils.stripEnd(sbArea.toString(),","),lonLat));
            }
            else {
                sbSql.append(String.format(" union all SELECT %s AS index, ST_Contains( ST_MakePolygon(ST_GeomFromText('LINESTRING(%s)')) " +
                        ",st_point(%s)) AS status",i, StringUtils.stripEnd(sbArea.toString(),","),lonLat));
            }
        }
        return jdbcTemplate.queryForList(sbSql.toString());
    }

    /**
     * 获取路线长度
     * @param points 路线经纬度点集合
     * @return
     */
    public List<Map<String, Object>> getLineLength(List<String> points){
        StringBuilder sbSql= new StringBuilder();
        for (int i = 0; i < points.size(); i++) {
            sbSql.append(points.get(i).replace(',', ' ') + ",");
        }
        String strSql = String.format("select ST_Length(Geography(ST_GeomFromText('LINESTRING(%s)'))) AS length"
                , StringUtils.stripEnd(sbSql.toString(),","));
        return jdbcTemplate.queryForList(strSql);
    }
}

4.4.对外提供API

package com.gnss.gis.controller;

import com.gnss.gis.model.*;
import com.gnss.gis.service.GisFunctionService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * GIS 接口
 * @author Mr.Li
 * @date 2023-08-09
 */
@RestController
@RequestMapping("/gis")
public class GisFunctionController {

    @Autowired
    private GisFunctionService gisFunctionService;

    /**
     * 判断两个经纬度点之间的距离
     * @param param
     * @return
     */
    @RequestMapping(value = "getPointDistance")
    public Object getPointDistance(@RequestBody PointDistanceParam param){
        return gisFunctionService.getPointDistance(param.getLonLat1(),param.getLonLat2());
    }

    /**
     * 查询点与点之间相距小于某个值的集合
     * @param param 点
     * @return
     */
    @RequestMapping(value = "getPointsWithin")
    public Object getPointsWithin(@RequestBody PointsWithinParam param){
        return gisFunctionService.getPointsDWithin(param.getLonLat(),param.getPoints(),param.getDistance());
    }

    /**
     * 查询点与点之间相距小于某个值的集合
     * @param param
     * @return
     */
    @RequestMapping(value = "getPointsWithinDistances")
    public Object getPointsWithinDistances(@RequestBody PointsWithinParam param){
        return gisFunctionService.getPointsDWithin(param.getLonLat(),param.getPoints(),param.getDistances());
    }

    /**
     * 判断某个经纬度点是否在某条路线上
     * @param param
     * @return
     */
    @RequestMapping(value = "getPointOnTheLine")
    public Object getPointOnTheLine(@RequestBody PointOnTheLineParam param){
        return gisFunctionService.getPointOnTheLine(param.getLonLat(),param.getPoints(),param.getBuffer());
    }

    /**
     * 判断某个经纬度点是否在这些路线上的其中一条
     * @param param
     * @return
     */
    @RequestMapping(value = "getPointOnOneLine")
    public Object getPointOnOneLine(@RequestBody PointOnOneLineParam param){
        return gisFunctionService.getPointOnOneLine(param.getLonLat(),param.getLines(),param.getBuffer());
    }

    /**
     * 判断某个经纬度点所在路线的状态
     * @param param
     * @return
     */
    @RequestMapping(value = "getPointOnLinesStatus")
    public Object getPointOnLinesStatus(@RequestBody PointOnOneLineParam param){
        return gisFunctionService.getPointOnLinesStatus(param.getLonLat(),param.getLines(),param.getBuffer());
    }

    /**
     * 判断某个经纬度点所在路线的状态
     * @param param
     * @return
     */
    @RequestMapping(value = "getPointOnLinesStatusBuffers")
    public Object getPointOnLinesStatusBuffers(@RequestBody PointOnOneLineParam param){
        return gisFunctionService.getPointOnLinesStatusBuffers(param.getLonLat(),param.getLines(),param.getBuffers());
    }

    /**
     * 判断点是否在某个区域
     * @param param
     * @return
     */
    @RequestMapping(value = "getPointOnTheArea")
    public Object getPointOnTheArea(@RequestBody PointOnTheAreaParam param){
        return gisFunctionService.getPointOnTheArea(param.getLonLat(),param.getPoints());
    }

    /**
     * 判断点是否多个区域的状态
     * @param param
     * @return
     */
    @RequestMapping(value = "getPointOnAreasStatus")
    public Object getPointOnAreasStatus(@RequestBody PointOnAreasStatusParam param){
        return gisFunctionService.getPointOnAreasStatus(param.getLonLat(),param.getAreas());
    }

    /**
     * 获取路线长度
     * @param param
     * @return
     */
    @RequestMapping(value = "getLineLength")
    public Object getLineLength(@RequestBody LinePoints param){
        return gisFunctionService.getLineLength(param.getPoints());
    }
}