Объединить точки долготы/широты в ограничительной рамке в зависимости от радиуса в MySQL

Вот фото того, чего я в основном хочу достичь:

Итак, как следует из названия, я хочу объединить точки долготы/широты, радиус которых (например, 25 км) касается внутри ограничивающей рамки точек долготы/широты.

Вот моя очень простая структура БД:

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

Вот мой запрос до сих пор:

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

Итак, мой запрос просто объединяет все точки внутри воображаемой ограничивающей рамки без учета радиуса.

Я знаю, что мне, вероятно, придется работать с формулой Хаверсина, но я плохо разбираюсь в математике и MySQL, что немного усложняет задачу. Действительно, я мог бы в конечном итоге объединить точки, если бы у меня был только один радиус, но каждая точка имеет свой собственный радиус, и я борюсь.

Это для студенческого проекта, и любая помощь будет очень признательна.

Использованная литература:

-Мой запрос на SQL Fiddle: http://sqlfiddle.com/#!2/3a42b/2 (содержит пример SQL Fiddle для формулы Haversine в комментарии)

- Формула Haversine в запросе MySQL: (работает для проверки всех точек внутри заданного радиуса)

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
;

mysql haversine
person user3561021    schedule 23.04.2014    source источник
comment
Возможно, взгляните на en.wikipedia.org/wiki/Spatial_database, а также о том, как вы обрабатываете уровень масштабирования?   -  person Risto Novik    schedule 23.04.2014
comment
Привет, посмотрю спасибо. Я не уверен, что понял ваш вопрос, но уровень масштабирования будет регулироваться путем изменения радиуса каждой точки в зависимости от этого масштабирования.   -  person user3561021    schedule 23.04.2014

Ответы (1)


arrow_upward
0
arrow_downward

Эта операция может быть слишком сложной для выполнения без помощи PHP или другого языка программирования. Вот как вы могли бы сделать это в 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>";
    }
?>

Вывод этого кода на предоставленных вами данных:

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

Обратите внимание, что это просто удаляет перекрывающиеся точки, а не усредняет позиции. Хотя вы могли бы легко добавить это. Надеюсь это поможет.

person cantelope    schedule 13.12.2015