数据库查询
# 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 sid
FROM business_master
WHERE 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);