javascript - Determine if two coordinates are within desired radius

Question

I'm working on aggregating my city's main big intersections with my city's collision data. What am trying to accomplish is to determine the number of accidents which happened in and around the intersection within a 20 meters radius.

Luckily, I'm already far off in the project and I have two tables in my database intersections and collisions

My intersections table:

 --------------------------------------------
 |  id  |  city  |  Latitude  |  Longitude  |
 --------------------------------------------
 |  1   |   1    |   34.44444 |   84.3434   |
 --------------------------------------------
 |  2   |   1    | 42.4666667 | 1.4666667   |
 --------------------------------------------
 |  3   |   1    |  32.534167 | 66.078056   |
 --------------------------------------------
 |  4   |   1    |  36.948889 | 66.328611   |
 --------------------------------------------
 |  5   |   1    |  35.088056 | 69.046389   |
 --------------------------------------------
 |  6   |   1    |  36.083056 |   69.0525   |
 --------------------------------------------
 |  7   |   1    |  31.015833 | 61.860278   |
 --------------------------------------------

MY collisions table:

--------------------------------------------
 |  id  |  cause |  Latitude  |  Longitude  |
 --------------------------------------------
 |  1   |   1    |   44.44444 |   81.3434   |
 --------------------------------------------
 |  2   |   1    | 32.4666667 | 1.4666667   |
 --------------------------------------------
 |  3   |   1    |  42.534167 | 63.078056   |
 --------------------------------------------
 |  4   |   1    |  46.948889 | 62.328611   |
 --------------------------------------------
 |  5   |   1    |  45.088056 | 61.046389   |
 --------------------------------------------
 |  6   |   1    |  46.083056 |   63.0525   |
 --------------------------------------------
 |  7   |   1    |  41.015833 | 69.860278   |
 --------------------------------------------

Note: Some fields were omitted for simplicity sake

As you can see, both tables posses latitude and longitude fields. Now to determine if intersection and collision coordinates are close, I thought of using a MySQL query that takes an id of an intersection which then queries the collisions table to get all collisions within 20 meters of the intersection.

What is this query (in MySQL or in Sequelize)?

Here's what I have now (it's in Sequelize using a MySQL database)

// api/collisions/{intersection_id}/count

exports.intersection_accident_count = (req, res) => {
    intersection.findOne({where: {equipement: req.params.intersection_id}}).then(intersection => {

        let intersectionLongitude = intersection.longitude;
        let intersectionLatitude = intersection.latitude;

        collision.count({where: {
            longitude: {
                $gt: intersectionLongitude
            },
            latitude: {
                $gt: intersectionLatitude
            },
        }}).then(count => {
            res.status(200).json({'number_of_accidents': count});
        });
    });
};

标签： javascriptmysqlnode.jsexpresssequelize.js

Answer 1

你应该从简单的方式开始：

SELECT
    i.*
FROM
    intersections AS i
    INNER JOIN collisions AS c ON (
        ST_Distance_Sphere(POINT(i.Longitude, i.Latitude), POINT(c.Longitude, c.Latitude)) < @dist
    )

这应该会给你结果，但是查询根本不会使用任何索引，如果你有很多记录，它会很慢。

您应该考虑在表中添加几何数据类型：

ALTER TABLE `collisions` ADD COLUMN `Location` POINT;
UPDATE `collisions` SET Location = POINT(Longitude, Latitude);
ALTER TABLE `intersections` ADD COLUMN `Location` POINT;
UPDATE `intersections` SET Location = POINT(Longitude, Latitude);

您可以添加触发器，以便在更新经度/纬度时自动填充位置。

然后：

SELECT
    i.*
FROM
    intersections AS i
    INNER JOIN collisions AS c ON (
        ST_Distance_Sphere(i.Location, c.Location) < @dist
    )

这会快一点。

如果您想让它更快，您可以将另一个几何 poligonArea列添加到您的交叉表和“预构建”区域（可以是圆形以提高准确性或正方形以提高速度）。之后，您将能够执行以下操作：

SELECT
    i.*
FROM
    intersections AS i
    INNER JOIN collisions AS c ON (
        ST_WITHIN(c.Location, i.Area)
    )