场景:查询3千米以内的所有商家
数据库存的都是经纬度,这时候就需要先将经纬度转成距离,然后在进行查询排序分页等操作
先来一个原生sql
SELECT
distance
FROM
( SELECT st_distance_sphere ( point ( longitude, latitude ), point ( 108.933191, 34.348112 )) AS distance FROM `tp_store` WHERE `status` = 1 ) `r`
WHERE
( distance <= 1000 )
ORDER BY
distance ASC
LIMIT 0,
10
(建议把业务上的where条件写到子查询里面,增加sql运行效率)
thinkphp代码
$field = "st_distance_sphere(point(longitude,latitude),point(xxxx,xxxx)) as distance";
$sql = Store::where($where)->field($field)->buildSql();
$list = Db::table($sql)->alias('r')->order('distance asc')->select();