php实现lbs实现商品筛选
PHP实现LBS商品筛选的方法
数据库设计
在MySQL中存储商品信息时需包含经纬度字段（如latitude和longitude），推荐使用POINT空间数据类型。创建表时可添加空间索引加速查询：
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
location POINT NOT NULL,
SPATIAL INDEX(location)
);距离计算
使用Haversine公式计算两点间距离（单位：公里）：
function getDistance($lat1, $lon1, $lat2, $lon2) {
$earthRadius = 6371;
$dLat = deg2rad($lat2 - $lat1);
$dLon = deg2rad($lon2 - $lon1);
$a = sin($dLat/2) * sin($dLat/2) +
cos(deg2rad($lat1)) * cos(deg2rad($lat2)) *
sin($dLon/2) * sin($dLon/2);
$c = 2 * atan2(sqrt($a), sqrt(1-$a));
return $earthRadius * $c;
}MySQL空间查询
对于MySQL 5.7+版本，可直接使用ST_Distance_Sphere函数：
SELECT
id,
name,
ST_Distance_Sphere(location, POINT(:user_lon, :user_lat)) / 1000 AS distance_km
FROM
products
WHERE
ST_Distance_Sphere(location, POINT(:user_lon, :user_lat)) <= :radius_km * 1000
ORDER BY
distance_km;Redis GEO优化
对高频访问场景可使用Redis GEO数据结构：
$redis->geoAdd('products:locations', $longitude, $latitude, $productId);
$results = $redis->geoRadius('products:locations', $userLon, $userLat, $radius, 'km');分页处理
结合距离筛选时需注意分页逻辑，建议先获取符合条件的所有ID，再分页查询详细信息：
$page = $_GET['page'] ?? 1;
$perPage = 10;
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare("
SELECT SQL_CALC_FOUND_ROWS p.*
FROM products p
WHERE ST_Distance_Sphere(location, POINT(?, ?)) <= ? * 1000
LIMIT ? OFFSET ?
");
$stmt->execute([$userLon, $userLat, $radius, $perPage, $offset]);性能优化
- 确保
latitude和longitude字段建立复合索引 - 对固定区域商品可先进行地理边界框预筛选
- 使用缓存存储热门区域的查询结果
- 考虑使用Elasticsearch的geo_point类型实现高级搜索
前端交互
通过浏览器获取用户位置后传递给后端：
navigator.geolocation.getCurrentPosition(position => {
const {latitude, longitude} = position.coords;
// 发送AJAX请求到PHP后端
});完整示例
后端接口处理示例：
// 获取前端传递的经纬度和半径
$userLat = $_GET['lat'];
$userLon = $_GET['lon'];
$radius = $_GET['radius'] ?? 5; // 默认5公里
$products = $pdo->prepare("
SELECT id, name,
ST_Distance_Sphere(location, POINT(?, ?)) / 1000 AS distance
FROM products
WHERE ST_Distance_Sphere(location, POINT(?, ?)) <= ? * 1000
ORDER BY distance
")->execute([$userLon, $userLat, $userLon, $userLat, $radius]);
header('Content-Type: application/json');
echo json_encode($products->fetchAll());
