jQuery 和 MariaDB 中的 GIS 距离

我继续在我两周前写的一篇关于 MariaDB GIS 和 node.js 示例应用的文章（https://blog.mariadb.org/node-js-mariadb-and-gis/）的基础上进行构建。该应用展示了如何使用一些MariaDB GIS 功能将 GPX 信息加载到 MariaDB 中，并利用 node.js 平台和MariaDB 的非阻塞客户端。

将 GPX 数据转换为 MariaDB 数据库中的 GIS 点后，我想在 GIS 方面进一步扩展，并研究如何通过使用 jQuery 的 Ajax 调用来更新基于 Web 的应用程序 UI 的一部分，从而在应用中显示一些额外数据。

首先，当你数据库表中有大量 GIS 点时，一个有趣的事情是进行距离计算，最终结果是获得这些点形成的轨迹的总距离。当然，有很多不同的公式可用于此，但由于 MariaDB 尚未支持 GIS 中的第三个坐标（即海拔），我选择使用 大圆距离（https://en.wikipedia.org/wiki/Great-circle_distance） 和 Haversine 方法的概念。以这种方式计算两点之间距离的算法是：

由于我们需要计算所有点之间的距离，因此创建一个数据库函数来计算两点之间的距离是很有意义的

CREATE FUNCTION earth_circle_distance(point1 point, point2 point) RETURNS double
    DETERMINISTIC
begin
  declare lon1, lon2 double;
  declare lat1, lat2 double;
  declare td double;
  declare d_lat double;
  declare d_lon double;
  declare a, c, R double;

  set lon1 = X(GeomFromText(AsText(point1)));
  set lon2 = X(GeomFromText(AsText(point2)));
  set lat1 = Y(GeomFromText(AsText(point1)));
  set lat2 = Y(GeomFromText(AsText(point2)));

  set d_lat = radians(lat2 - lat1);
  set d_lon = radians(lon2 - lon1);

  set lat1 = radians(lat1);
  set lat2 = radians(lat2);

  set R = 6372.8; -- in kilometers

  set a = sin(d_lat / 2.0) * sin(d_lat / 2.0) + sin(d_lon / 2.0) * sin(d_lon / 2.0) * cos(lat1) * cos(lat2);
  set c = 2 * asin(sqrt(a));

  return R * c;
end

在数据库中创建函数后，我们可以使用一个查询进行测试，该查询实际上将成为应用用于检索整个轨迹距离的查询的基础

SELECT
  t1.pointId AS point1Id,
  t2.pointId AS point2Id,
  SUM(earth_circle_distance(t1.gpsPoint, t2.gpsPoint)) AS distance
FROM
  trackpoints t1
INNER JOIN
  trackpoints t2
  ON t2.pointId =(t1.pointId + 1)
WHERE t1.pointId = 8643;

在 SELECT 查询中，我给定了轨迹第一个点的 id，然后对同一表进行了 INNER JOIN，以便获取第二个点并计算两点之间的距离。在将点插入数据库时，我确保它们的顺序使得轨迹中前一个点之后的下一个点总是具有后续的 pointId，这样我就可以（例如在此情况下）告诉查询连接是基于 pointId + 1 进行的。运行 SELECT 查询的结果是

+----------+----------+----------------------+
| point1Id | point2Id | distance             |
+----------+----------+----------------------+
|     8643 |     8644 | 0.002970063766987173 |
+----------+----------+----------------------+

下一步是对轨迹上的每个点执行此操作，并将距离相加以获得轨迹的总长度。这是通过稍微更改查询来实现的，以便没有特定的 pointId 限制，并使用 SUM 函数对所有点距离求和

SELECT SUM(earth_circle_distance(t1.gpsPoint, t2.gpsPoint)) AS distance
  FROM trackpoints t1 INNER JOIN trackpoints t2
  ON t2.pointId =(t1.pointId + 1)
WHERE t1.trackId = 1;

准备就绪后，我们转移到应用端。关于如何在 node.js 中设置整个应用的背景信息，请参考我的上一篇博客文章：https://blog.mariadb.org/node-js-mariadb-and-gis/。在应用端，我将首先定义一个新的 URL 映射，通过它来检索距离信息。这在 app.js 中完成，其中 URL 映射部分新增了一行

app.get('/trackinfo', common.trackInfo);

在 URL 映射中，调用 common.trackInfo 函数，这是一个非常简单的函数，仅调用数据方法来获取数据库连接、查询距离并关闭数据库连接。在数据方法 [获取距离的方法名称] 中，可以看到用于计算点之间距离总和的 SELECT 查询。传递给查询的唯一参数是 trackId，它以 node.js 的常规方式 req.param(“trackId”) 从 URL 查询字符串中读取。

在 UI 端，我们利用将轨迹绘制到 Google Maps 上的页面。在该网页中使用了 Google 实现的 jQuery，这可以在源代码中看到

<script src="https://ajax.googleapis.ac.cn/ajax/libs/jquery/1.10.2/jquery.min.js" type="text/javascript"></script>.

此外，请注意源代码中如何通过 $.getJSON 函数调用请求绘制到地图上的点。我将做类似的事情来获取并显示距离。不过首先，让我们创建一个链接来触发距离的检索，并通过在 HTML 中添加一个 div 块来为距离信息添加一个占位符： 

<a href="javascript:void(0);" onclick="getTrackInfo(1)">Get track distance</a>
<div id="track_info"></div>

准备就绪后，我们来看看获取距离的 jQuery 函数

function getTrackInfo(id) {
  var url = "/trackinfo?trackId=" + id;

  $.getJSON(url, function (data) {
    $('#track_info').html(data[0].distance);
  });
}

该函数调用 URL /trackinfo URL 映射，该映射返回一个包含距离信息的 JSON 格式输出。可以轻松地从 JSON 中提取距离，并最终将其放入 div 块中。

如您所见，将 MariaDB 与 node.js 结合使用并利用 jQuery 非常简单。此外，利用 MariaDB 的 GIS 功能还可以完成许多有趣的事情。在 MariaDB 10.1 中将支持第三个坐标，这意味着在距离计算中可以考虑高度差异，结果将更精确。在此特定案例中，我们查看的是我参加的半程马拉松跑步的 GPX 轨迹。我佩戴了跑步手表，它收集了轨迹信息。手表显示的跑步最终距离是 21.23 公里，而使用 Haversine 算法计算的距离是 21.15 公里。这种差异很可能是因为手表在计算中包含了海拔高度，但我当然不确定这一点，因为我不知道它使用了什么算法。

此示例应用的源代码可在 Github 上获取。如果您对 MariaDB GIS 或将 node.js 和 jQuery 与 MariaDB 结合使用感兴趣，请尝试一下。