台球助教系统开发之会员、助教、球厅位置坐标存储计算SQL语言遍历代码（第八章）

        台球助教平台角色地理位置的获取，我前面也讲过了，并给出了实例代码，在台球助教系统里，会员，助教，球厅3个角色是多对多的对象计算。本篇文章讲关于这3这对象的缓存，数据字段存储，以及查询距离计算出实际距离位置，并由近及远进行排序显示的程序代码和SQL代码。

会员当前的位置坐标是实时推送给平台来作缓存存储。
球厅位置和经营场地是固定的，固定标注储存。
助教的位置定时更新标注，临时存储定期修改或平台审核更新。

        因为和工作地点固定有关，可以是坐标实时更新，也可以是标注一个他目前为止的固定点，在开发系统中，设置一个固定坐标点比较科学一点，例如当前助教的服务范围是他工作5公里内，那么基于他自己的位置在地图上标注一个位置。这个位置做一个长期存储。同时系统为了监管助教随意改动坐标信息，引起混乱的虚拟位置，程序里可以设置助教允许改变坐标位置的时间，在这个设置时间内不能更改坐标，只有周期到了后才能更改服务的坐标半径信息。

为了让大家更详细地了解地理位置周边存储信息。做了一个把商户信息存储在数据表里，用户的坐标位置信息和商户信息通过SQL查询语句实现地理位置的计算输出。

为了实现从数据库中读取商户的地理坐标信息，并计算这些商户与当前用户之间的距离，我们可以使用PHP来连接数据库并执行SQL查询。在SQL查询中，我们将嵌入Haversine公式以计算距离。这里假设你使用的是MySQL数据库。

其中SQL核心代码是这一句，相当于我们把程序代码的计算用SQL查询代码实现，计算负载让MYSQL数据来实现了

SELECT

            m.id,

            m.name,

            m.latitude,

            m.longitude,

            (

                2 * ? * ASIN(

                    SQRT(

                        POWER(SIN((? - m.latitude) * PI() / 180 / 2), 2) +

                        COS(? * PI() / 180) *

                        COS(m.latitude * PI() / 180) *

                        POWER(SIN((? - m.longitude) * PI() / 180 / 2), 2)

                    )

                )

            ) AS distance

        FROM merchants m

        HAVING distance <= ?

        ORDER BY distance ASC

对改语句做一个详细解析，以便让读者能更详细了解

选择字段

m.id, m.name, m.latitude, m.longitude：这些是从merchants表中选择的字段，分别表示商户的ID、名称、纬度和经度。

Haversine 公式

2 * ?：这里的问号代表地球半径（以公里为单位），在PHP代码中会被替换为实际值（例如6371.0）。这是Haversine公式的一部分，用于将中心角转换成距离。

ASIN(...)：反正弦函数，用来计算中心角的一半。最终结果会乘以2来得到完整的中心角。

SQRT(...)：平方根函数，它用于计算公式内部的和的平方根，这是Haversine公式的一个关键部分。

POWER(SIN((? - m.latitude) * PI() / 180 / 2), 2)：这部分计算纬度差的一半正弦值的平方。这里将度数转换为弧度（通过乘以PI()/180）。

COS(? * PI() / 180) * COS(m.latitude * PI() / 180)：这部分计算两个点纬度的余弦值的乘积，同样进行了度数到弧度的转换。

POWER(SIN((? - m.longitude) * PI() / 180 / 2), 2)：这部分计算经度差的一半正弦值的平方，也进行了度数到弧度的转换。

所有这些部分被组合起来形成了Haversine公式的完整表达式，用以计算两个地理坐标点之间的大圆距离。

1. 数据库表结构（假设）

我们假设有如下一个名为 merchants 的表，其中包含商户的名称、纬度和经度。

 

CREATE TABLE merchants (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(255) NOT NULL,

    latitude DECIMAL(9,6) NOT NULL,

    longitude DECIMAL(9,6) NOT NULL

);

<?php

// 数据库配置信息
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_username';
$password = 'your_password';

try {
    // 创建PDO实例
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 当前用户的实时地理位置坐标
    $userLatitude = 39.9042; // 北京的纬度
    $userLongitude = 116.4074; // 北京的经度

    // 地球半径（单位：公里）
    $earthRadius = 6371.0;

    // 准备SQL查询语句，内嵌Haversine公式
    $sql = "
        SELECT 
            m.id,
            m.name,
            m.latitude,
            m.longitude,
            (
                2 * ? * ASIN(
                    SQRT(
                        POWER(SIN((? - m.latitude) * PI() / 180 / 2), 2) +
                        COS(? * PI() / 180) *
                        COS(m.latitude * PI() / 180) *
                        POWER(SIN((? - m.longitude) * PI() / 180 / 2), 2)
                    )
                )
            ) AS distance
        FROM merchants m
        HAVING distance <= ?
        ORDER BY distance ASC
    ";

    // 预处理语句
    $stmt = $pdo->prepare($sql);

    // 执行查询，传递参数
    $stmt->execute([
        $earthRadius, // 地球半径
        $userLatitude, // 用户纬度
        $userLatitude, // 用户纬度（用于COS函数）
        $userLongitude, // 用户经度
        50 // 只选择距离小于等于50公里的商户
    ]);

    // 获取结果集
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 输出结果
    foreach ($results as $row) {
        echo "ID: " . htmlspecialchars($row['id']) . "<br>";
        echo "Name: " . htmlspecialchars($row['name']) . "<br>";
        echo "Latitude: " . htmlspecialchars($row['latitude']) . "<br>";
        echo "Longitude: " . htmlspecialchars($row['longitude']) . "<br>";
        echo "Distance: " . number_format($row['distance'], 2) . " km<br><br>";
    }

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

?>

 

解释：

PDO连接：使用PDO创建一个到MySQL数据库的连接。

用户位置：设置了一个变量来保存当前用户的实时地理位置坐标。

SQL查询：构建了一个SQL查询，该查询不仅选择了商户的信息，还通过Haversine公式计算了每个商户与当前用户之间的距离。注意，这里的SQL语句是预处理语句，可以防止SQL注入攻击。

HAVING子句：在SQL查询中添加了一个HAVING子句来过滤出距离小于等于指定值（例如50公里）的记录。

执行查询：通过PDO执行预处理语句，并将必要的参数传递给它。

结果输出：循环遍历查询结果，并格式化输出每个商户的信息及其与用户之间的距离。

请确保根据实际情况调整数据库配置信息和商户表的具体字段名。此外，在实际应用中，您可能还需要考虑对用户位置数据进行验证和清理，以保证安全性和准确性。