三维地图poi点、线、面范围查询

三维地图poi点、线、面范围查询

​ 在三维GIS系统应用中，我们常常要根据一个范围（可能是关系面、可能是线周边或者是点周边）来查询所需POI点。目前遇到的一些关于需求，利用postgres数据库函数，来判断范围内poi点位的查询：

1、基础poi表（含geom属性 很重要！）

1、controller

    /**
     * 查询 15分钟生活圈 社区地点地名列表
     *
     * @param localParam 本地参数
     * @return {@link AjaxResult}
     */
    @ApiOperation("2-点、线、面范围查询-查询 15分钟生活圈")
    @PostMapping("/queryPointsLinesPolygon")
    public AjaxResult queryPointsLinesPolygon(@RequestBody LocalParam localParam) {
        try {
            List<Map<String, Object>> resultList = tylPoiService.queryPointsLinesPolygon(localParam);
            return AjaxResult.success(resultList);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.error(e.getMessage());
        }
    }

2、Iservice

    /**
     * query4 attr
     *
     * @param localParam 本地参数
     * @return {@link List}<{@link Map}<{@link String}, {@link Object}>>
     */
    List<Map<String, Object>> queryPointsLinesPolygon(LocalParam localParam);


}

3、serviceImpl

  /**
     * 查询点 线 多边形
     *
     * @param localParam 本地参数
     * @return {@link List}<{@link Map}<{@link String}, {@link Object}>>
     */
    @Override
    public List<Map<String, Object>> queryPointsLinesPolygon(LocalParam localParam) {
        System.out.println("getBuffer = " + localParam.getBuffer());
        System.out.println("getTableName = " + localParam.getTableName());
        List<Map<String, Object>> listResult = new ArrayList<>();
        try {
            if (StringUtils.isNotEmpty(localParam.getX()) || StringUtils.isNotEmpty(localParam.getY())) {
                //两点
                System.out.println("getX = " + localParam.getX());
                System.out.println("getY = " + localParam.getY());
                listResult = tylPoiMapper.queryPoint(localParam);
            } else if (StringUtils.isNotEmpty(localParam.getLineListParam())) {
                //划线
                System.out.println("getLineListParam = " + localParam.getLineListParam());
                listResult = tylPoiMapper.queryLine(localParam);
            } else if (StringUtils.isNotEmpty(localParam.getPolygonListParam())) {
                //多边形
                System.out.println("getPolygonListParam = " + localParam.getPolygonListParam());
                listResult = tylPoiMapper.queryPolygon(localParam);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return listResult;
    }

Mapper

package com.ruoyi.bus.mapper;

import com.ruoyi.bus.domain.LocalParam;
import com.ruoyi.bus.domain.TylPoi;
import org.apache.ibatis.annotations.Param;

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

/**
 * 兴趣点Mapper接口
 *
 * @author xingjs
 * @date 2024-06-26
 */
public interface TylPoiMapper {
    /**
     * 查询兴趣点
     *
     * @param tid 兴趣点主键
     * @return 兴趣点
     */
    public TylPoi selectTylPoiByTid(Long tid);

    /**
     * 查询兴趣点列表
     *
     * @param tylPoi 兴趣点
     * @return 兴趣点集合
     */
    public List<TylPoi> selectTylPoiList(TylPoi tylPoi);

    /**
     * 新增兴趣点
     *
     * @param tylPoi 兴趣点
     * @return 结果
     */
    public int insertTylPoi(TylPoi tylPoi);

    /**
     * 修改兴趣点
     *
     * @param tylPoi 兴趣点
     * @return 结果
     */
    public int updateTylPoi(TylPoi tylPoi);

    /**
     * 删除兴趣点
     *
     * @param tid 兴趣点主键
     * @return 结果
     */
    public int deleteTylPoiByTid(Long tid);

    /**
     * 批量删除兴趣点
     *
     * @param tids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteTylPoiByTids(Long[] tids);

    /**
     * 计数类型
     *
     * @param tylPoi 泰尔·波伊
     * @return {@link List}<{@link HashMap}<{@link String}, {@link Object}>>
     */
    List<HashMap<String, Object>> countType(TylPoi tylPoi);


    /**
     * 查询点
     *
     * @param localParam 本地参数
     * @return {@link List}<{@link Map}<{@link String}, {@link Object}>>
     */
    List<Map<String, Object>> queryPoint(@Param("result") LocalParam localParam);

    /**
     * 查询行
     *
     * @param localParam 本地参数
     * @return {@link List}<{@link Map}<{@link String}, {@link Object}>>
     */
    List<Map<String, Object>> queryLine(@Param("result") LocalParam localParam);

    /**
     * 查询多边形
     *
     * @param localParam 本地参数
     * @return {@link List}<{@link Map}<{@link String}, {@link Object}>>
     */
    List<Map<String, Object>> queryPolygon(@Param("result") LocalParam localParam);

}

mapperXml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.bus.mapper.TylPoiMapper">

    <resultMap type="com.ruoyi.bus.domain.TylPoi" id="TylPoiResult">
        <result property="id" column="id"/>
        <result property="code" column="code"/>
        <result property="type" column="type"/>
        <result property="name" column="name"/>
        <result property="producttime" column="producttime"/>
        <result property="deadtime" column="deadtime"/>
        <result property="modifytime" column="modifytime"/>
        <result property="address" column="address"/>
        <result property="x" column="x"/>
        <result property="y" column="y"/>
        <result property="tid" column="tid"/>
        <result property="geom" column="geom"/>
    </resultMap>

    <sql id="selectTylPoiVo">
        select id,
               code,
               type,
               name,
               producttime,
               deadtime,
               modifytime,
               address,
               x,
               y,
               tid,
               geom
        from tyl_poi
    </sql>

    <select id="selectTylPoiList" parameterType="com.ruoyi.bus.domain.TylPoi" resultMap="TylPoiResult">
        <include refid="selectTylPoiVo"/>
        <where>
            <if test="code != null  and code != ''">and code = #{code}</if>
            <if test="type != null  and type != ''">and type = #{type}</if>
            <if test="name != null  and name != ''">and name like concat('%', #{name}, '%')</if>
            <if test="producttime != null  and producttime != ''">and producttime = #{producttime}</if>
            <if test="deadtime != null  and deadtime != ''">and deadtime = #{deadtime}</if>
            <if test="modifytime != null  and modifytime != ''">and modifytime = #{modifytime}</if>
            <if test="address != null  and address != ''">and address = #{address}</if>
            <if test="x != null  and x != ''">and x = #{x}</if>
            <if test="y != null  and y != ''">and y = #{y}</if>
            <if test="geom != null  and geom != ''">and geom = #{geom}</if>
        </where>
    </select>

    <select id="selectTylPoiByTid" parameterType="java.lang.Long" resultMap="TylPoiResult">
        <include refid="selectTylPoiVo"/>
        where tid = #{tid}
    </select>

    <select id="countType" resultType="java.util.HashMap" parameterType="com.ruoyi.bus.domain.TylPoi">
        SELECT type, count(*)
        FROM tyl_poi
        WHERE TYPE IS NOT NULL
        group by type
    </select>


    <!-- 点   where st_intersects(st_buffer(st_geomfromtext('POINT(${result.x} ${result.y})',4326), 1),geom)
     AND entity_c_3 in ('酒店住宿餐饮','金融保险','医疗卫生保健','文化教育','居民小区','商场超市')--> 
   <!-- 这个前台传过来的${result.buffer}缓冲值单位是米还是什么，数值越大查询的范围是越大，但是不像是米。有待考证，知道的请告诉我一下！！！  --> 
    <select id="queryPoint" resultType="java.util.Map">
        select *,st_astext(geom) as wkt from tyl_poi
        where ST_Covers(st_buffer(st_geomfromtext('POINT(${result.x} ${result.y})',4326), ${result.buffer}),geom)
    </select>

    <!-- 线   -->
    <select id="queryLine" resultType="java.util.Map">
        select *,st_astext(geom) as wkt from tyl_poi
        where ST_Covers(st_buffer(ST_LineFromText('linestring(${result.lineListParam})',4326),${result.buffer}),geom)
    </select>

    <!-- 面  where ST_Covers(ST_PolygonFromtext('polygon ((${result1.polygonListParam}))',4326),geom);  -->
    <select id="queryPolygon" resultType="java.util.Map">
        select *,st_astext(geom) as wkt from tyl_poi
        where ST_Covers(ST_PolygonFromtext('polygon ((${result.polygonListParam}))',4326),geom);
    </select>


    <insert id="insertTylPoi" parameterType="com.ruoyi.bus.domain.TylPoi">
        insert into tyl_poi
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="code != null">code,</if>
            <if test="type != null and type != ''">type,</if>
            <if test="name != null and name != ''">name,</if>
            <if test="producttime != null">producttime,</if>
            <if test="deadtime != null">deadtime,</if>
            <if test="modifytime != null">modifytime,</if>
            <if test="address != null">address,</if>
            <if test="x != null">x,</if>
            <if test="y != null">y,</if>
            <if test="tid != null">tid,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="code != null">#{code},</if>
            <if test="type != null and type != ''">#{type},</if>
            <if test="name != null and name != ''">#{name},</if>
            <if test="producttime != null">#{producttime},</if>
            <if test="deadtime != null">#{deadtime},</if>
            <if test="modifytime != null">#{modifytime},</if>
            <if test="address != null">#{address},</if>
            <if test="x != null">#{x},</if>
            <if test="y != null">#{y},</if>
            <if test="tid != null">#{tid},</if>
        </trim>
    </insert>

    <update id="updateTylPoi" parameterType="com.ruoyi.bus.domain.TylPoi">
        update tyl_poi
        <trim prefix="SET" suffixOverrides=",">
            <if test="id != null">id = #{id},</if>
            <if test="code != null">code = #{code},</if>
            <if test="type != null and type != ''">type = #{type},</if>
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="producttime != null">producttime = #{producttime},</if>
            <if test="deadtime != null">deadtime = #{deadtime},</if>
            <if test="modifytime != null">modifytime = #{modifytime},</if>
            <if test="address != null">address = #{address},</if>
            <if test="x != null">x = #{x},</if>
            <if test="y != null">y = #{y},</if>
        </trim>
        where tid = #{tid}
    </update>

    <delete id="deleteTylPoiByTid" parameterType="java.lang.Long">
        delete
        from tyl_poi
        where tid = #{tid}
    </delete>

    <delete id="deleteTylPoiByTids" parameterType="java.lang.String">
        delete from tyl_poi where tid in
        <foreach item="tid" collection="array" open="(" separator="," close=")">
            #{tid}
        </foreach>
    </delete>
</mapper>

delete
from tyl_poi
where tid = #{tid}

<delete id="deleteTylPoiByTids" parameterType="java.lang.String">
    delete from tyl_poi where tid in
    <foreach item="tid" collection="array" open="(" separator="," close=")">
        #{tid}
    </foreach>
</delete>