根据MySQL中的半径在边界框内合并长点/经点 [英] Merge long/lat points within a bounding box depending on a radius in MySQL

问题描述

这是我基本上想要实现的目标的图片:

Here is a pic of what I basically want to achieve:

因此，正如标题所述，我想将长/纬度点的半径(例如25Kms)合并到长/纬度点的边界框内.

So as the title says, I want to merge long/lat points which they're radius (of 25Kms for example) touch inside a bounding box of long/lat points.

这是我非常简单的数据库结构:

Here is my very simple DB structure:

+-------+-------------+------------+
| id    |        long |        lat |
+-------+-------------+------------+
|     1 |   -90.27137 |   50.00702 |
|     2 |   -92.27137 |   52.00702 |
|     3 |   -87.27137 |   48.00702 |
|     4 |   -91.27137 |   51.00702 |
+-------+-------------+------------+

这是我到目前为止的查询:

Here is my query so far:

set @bottom_lat = -100.27137;
set @bottom_lon = 40.00702;

set @top_lat = -80.27137 ;
set @top_lon = 60.00702 ;
;

SELECT AVG(latitude), AVG(longitude)
FROM destination
WHERE latitude > @bottom_lat AND longitude > @bottom_lon AND latitude < @top_lat AND longitude < @top_lon

因此，我的查询仅合并了虚构边界框内的所有点，而没有考虑半径.

So my query just merging all points inside an imaginary bounding box without considering radius.

我知道我大概必须使用Haversine公式，但是我对数学和MySQL很不满意，这使事情变得有些困难.的确，如果我只有一个半径，但每个点都有自己的半径，并且很费力，我最终可以合并点.

I know that I would prorably have to work with the Haversine formula but I'm crap at maths and MySQL which make things a little bit difficult. Indeed, I could eventually merge points if I had just one radius but each points have its own radius and I'm struggling.

这是针对一个学生项目的，任何帮助将不胜枚举.

This is for a student project and any help will be much much apreciated.

参考文献:

-我对SQL Fiddle的查询: http://sqlfiddle.com/#!2/3a42b/2 (在注释中包含有关Haversine公式的SQL Fiddle示例)

-My query on SQL Fiddle: http://sqlfiddle.com/#!2/3a42b/2 ( contains a SQL Fiddle exemple for the Haversine Formula in comment )

-MySQL查询中的Haversine公式:(用于检查给定半径内的所有点的工作)

-The Haversine formula in MySQL query: (work for checking all points inside a given radius)

SELECT*, ( 6371* acos( cos( radians(
@my_lat) ) * cos( radians( 
destination.latitude ) ) * cos( radians( 
destination.longitude ) - radians(
@my_lon) ) + sin( radians(
@my_lat) ) * sin( radians( 
destination.latitude ) ) ) ) AS distance 
FROM destination
ORDER BY distance limit 1
;

推荐答案

如果没有PHP或其他编程语言的帮助，此操作可能太复杂而无法执行.这是您可以在PHP中完成的方法:

This operation may be too complicated to execute without the aid of PHP or another programming language. Here's how you could do it in PHP:

<?
    $link = mysqli_connect("host", "user", "pass", "database");

    // Grab all the points from the db and push them into an array
    $sql = "SELECT * FROM data";
    $res = $link->query($sql);
    $arr = array();
    for($i = 0; $i < mysqli_num_rows($res); $i++){
        array_push($arr, mysqli_fetch_assoc($res));
    }

    // Cycle through the point array, eliminating those points that "touch" 
    $rad = 1000; //radius in KM
    for($i = 0; $i < count($arr); ++$i){
        $lat1 = $arr[$i]['lat'];
        $lon1 = $arr[$i]['long'];
        for($j = 0; $j<count($arr); ++$j){
            if($i != $j && isset($arr[$i]) && isset($arr[$j])){ // do not compare a point to itself
                $lat2 = $arr[$j]['lat'];
                $lon2 = $arr[$j]['long'];
                // get the distance between each pair of points using the haversine formula
                $dist = acos( sin($lat1*pi()/180)*sin($lat2*pi()/180) + cos($lat1*pi()/180)*cos($lat2*pi()/180)*cos($lon2*PI()/180-$lon1*pi()/180) ) * 6371;
                if($dist < $rad){
                    echo "Removing point id:".$arr[$i]['id']."<br>";
                    unset($arr[$i]);
                }
            }
        }
    }

    //display results
    echo "Remaining points:<br>";
    foreach($arr as $val){
        echo "id=".$val['id']."<br>";
    }
?>

此代码在您提供的数据上的输出为:

The output of this code on the data you provided is:

    Removing point id:1
    Removing point id:2
    Remaining points:
    id=3
    id=4

请注意，这只会删除重叠的点，不会对位置进行任何平均.您可以轻松地添加它.希望这会有所帮助.

Note that this just removes the overlapping points, it doesn't do any averaging of positions. You could easily add that though. Hope this helps.

这篇关于根据MySQL中的半径在边界框内合并长点/经点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！