mysql 空间查询 多边形内的点

数据库查询

# 1新增空间point类型坐标字段
ALTER TABLE `gaoxin_isdp`.`business_master` 
ADD COLUMN `location2` point NULL AFTER `location`;# 2从原字段更新点位字段，原字段poi1是字符串106.474596,29.464360
UPDATE business_master SET location = POINT(substr(poi1, 1,10), substr(poi1, 12));# 3传入多边形坐标查询内部点位数据
SELECT SUM(a.qd28) AS 'zjrs', SUM(a.qj09) AS 'yfrs', SUM(a.qd27) AS 'glrs' 
FROM business_division03 a 
where sid in (SELECT sidFROM business_masterWHERE ST_Within(location, ST_GeomFromText('POLYGON((106.479029 29.487577, 106.476155 29.48044, 106.492216 29.485124, 106.483772 29.48525, 106.479029 29.487577))'))
)

接口查询示例

调试时可以在百度地图上取点测试 ： https://api.map.baidu.com/lbsapi/getpoint/index.html
== 测试用的多边形点位需要首尾相同 ==
前端构造成POLYGON格式字符串调用即可，如果使用magic-api,接口控制台代码如下：

var sql = "select sum(qd28) as 'zjrs',sum(qj09) as 'yfrs',sum(qd27) as 'glrs' from business_division03";
if(sid!=null && sid!='') {sql+=" where sid = #{sid}";
}else if(polygon!=null && polygon!=''){sql+=" where sid in (SELECT sid FROM business_master WHERE ST_Within(location, ST_GeomFromText(#{polygon})))"
}
return db.select(sql);