postgresql空间查询，查询画在的圆里面的数据，查询画在面里面的数据

空间查询首先要安装postgis

java中，面的查询mapper

<!--判断面是不是在里面，在里面是f，不在为t，第一行是前端传的数据，第二行是后端传的数据-->
<select id="getPlaceFlag" resultType="string">
	SELECT ST_Disjoint(
	st_geometryfromtext (#{polygon},0),
	st_geometryfromtext (#{point},0)
	)
</select>
前端数据例子：
polygon ((119.84055050233592 31.930246876770404,120.19691952753816 31.764078660780147,120.12207516579048 31.507273236067935,119.69086177884056 31.599970381351756,119.84055050233592 31.930246876770404))

后端数据例子：
String jd = projExcelVo.get(i).getJd().toString();
String wd = projExcelVo.get(i).getWd().toString();
String concat = "point (";
String point = concat.concat(jd).concat(" ").concat(wd).concat(")");

数据库操作sql

SELECT ST_Disjoint(
st_geometryfromtext ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))',0) ,
st_geometryfromtext ('POINT (20 10)',0)    
)

点，半径画圆的查询

java中，圆的查询mapper

<select id="getRoundFlag" resultType="string">
	<!--判断圆是不是在里面，在里面是f，不在为t-->
	        SELECT ST_Disjoint(
	        <!--pointCenter为点，radil为半径，int类型-->
	        st_buffer(st_geometryfromtext (#{pointCenter},0),#{radii}),
	         <!--point后端数据-->
	        st_geometryfromtext (#{point},0)
	        )
</select>
因为我数据库存的是经纬度，这里需要转为墨卡托数据才好使用半径查询

可以参考我的这篇文章：经纬度与墨卡托的互相转换工具类

数据库操作sql

-- 判断以点为圆心的半径为x的是否在圆内
SELECT st_contains(
st_buffer(st_geometryfromtext ('POINT (0 0)',0),100 ),
st_geometryfromtext ('POINT (0 0)',0)    
)

通过上面两个判断sql，可以与数据库的数据进行判断，获取到想要的数据
下面是我的代码，可参考：

@Override
    public List<ProjExcelVo> webNoPageLess(List<String> distIdList, List<String> whflList, ProjExcelVo proj, String jgkssj, String jgjssj, String xckssj, String xcjssj, String quxckssj, String quxcjssj, Integer zxdf, Integer zddf, String district,
                                           String polygon, String pointCenter, String typeMap, String radii) {
        // 存储面数据
        List<ProjExcelVo> listPlace = new ArrayList<>();
        // 存储点数据
        List<ProjExcelVo> listPoint = new ArrayList<>();
        // 如果不查询点或面直接返回所有数据
        if (CommUtils.isEmpty(typeMap)) {
            List<ProjExcelVo> projExcelVo = projMapper.excelNoPageLess(distIdList, whflList, proj, jgkssj, jgjssj, xckssj, xcjssj, quxckssj, quxcjssj, zxdf, zddf, district);
            for (int i = 0; i < projExcelVo.size(); i++) {
                projExcelVo.get(i).setNumber(i + 1);
                if (!CommUtils.isEmpty(projExcelVo.get(i).getSyfExpire())) {
                    String substring = projExcelVo.get(i).getSyfExpire().substring(0, 10);
                    projExcelVo.get(i).setSyfExpire(substring);
                }
            }
            return projExcelVo;
        } else if (!CommUtils.isEmpty(typeMap) && "0".equals(typeMap) && !CommUtils.isEmpty(polygon)) {
            // 如果type为0,面不为空则是面查询
            List<ProjExcelVo> projExcelVo = projMapper.excelNoPageLess(distIdList, whflList, proj, jgkssj, jgjssj, xckssj, xcjssj, quxckssj, quxcjssj, zxdf, zddf, district);
            for (int i = 0; i < projExcelVo.size(); i++) {
                projExcelVo.get(i).setNumber(i + 1);
                if (!CommUtils.isEmpty(projExcelVo.get(i).getSyfExpire())) {
                    String substring = projExcelVo.get(i).getSyfExpire().substring(0, 10);
                    projExcelVo.get(i).setSyfExpire(substring);
                }
                if (!CommUtils.isEmpty(projExcelVo.get(i).getJd()) && !CommUtils.isEmpty(projExcelVo.get(i).getWd())) {
                    String jd = projExcelVo.get(i).getJd().toString();
                    String wd = projExcelVo.get(i).getWd().toString();
                    String concat = "point (";
                    String point = concat.concat(jd).concat(" ").concat(wd).concat(")");
                    // 在里面是f，不在为t
                    String placeFlag = projMapper.getPlaceFlag(polygon, point);
                    if ("f".equals(placeFlag)) {
                        proj.setJd(Double.parseDouble(jd));
                        proj.setWd(Double.parseDouble(wd));
                        listPlace.add(projExcelVo.get(i));
                    }
                }
            }
            return listPlace;
        } else if (!CommUtils.isEmpty(typeMap) && "1".equals(typeMap) && !CommUtils.isEmpty(radii) && !CommUtils.isEmpty(pointCenter)) {
            // 如果type为1，半径，圆的中心点不为空则是圆查询
            List<ProjExcelVo> projExcelVo = projMapper.excelNoPageLess(distIdList, whflList, proj, jgkssj, jgjssj, xckssj, xcjssj, quxckssj, quxcjssj, zxdf, zddf, district);
            for (int i = 0; i < projExcelVo.size(); i++) {
                projExcelVo.get(i).setNumber(i + 1);
                if (!CommUtils.isEmpty(projExcelVo.get(i).getSyfExpire())) {
                    String substring = projExcelVo.get(i).getSyfExpire().substring(0, 10);
                    projExcelVo.get(i).setSyfExpire(substring);
                }
                if (!CommUtils.isEmpty(projExcelVo.get(i).getJd()) && !CommUtils.isEmpty(projExcelVo.get(i).getWd())) {
                    String jd = projExcelVo.get(i).getJd().toString();
                    String wd = projExcelVo.get(i).getWd().toString();
                    double[] doubles = ChangeXy.lonLat2Mercator(projExcelVo.get(i).getJd(), projExcelVo.get(i).getWd());
                    BigDecimal jdMkt = new BigDecimal(doubles[0]);
                    BigDecimal wdMkt = new BigDecimal(doubles[1]);
                    String concat = "point (";
                    String point = concat.concat(jdMkt.toPlainString()).concat(" ").concat(wdMkt.toPlainString()).concat(")");
//                    String point = concat.concat(jd).concat(" ").concat(wd).concat(")");
//                    System.err.println("墨卡托数据0：" + bd.toPlainString()+"---墨卡托数据1：" +bd1.toPlainString());
                    // 在里面是f，不在为t
                    String placeFlag = projMapper.getRoundFlag(pointCenter, point,Integer.parseInt(radii));
                    if ("f".equals(placeFlag)) {
                        proj.setJd(Double.parseDouble(jd));
                        proj.setWd(Double.parseDouble(wd));
                        listPoint.add(projExcelVo.get(i));
                    }
                }
            }
        }
        return listPoint;

    }