четверг, сентября 11, 2014

Развлекаемся с геоданными с MariaDB и ElasticSearch

Собираем геоданные

У Google есть классное API для геокодирования адресов. Я получил кучу данных, когда я искал адрес моего любимого футбольного клуба:
curl -XGET https://maps.googleapis.com/maps/api/geocode/json?address=Stadion+Feijenoord
Смотрите сами https://maps.googleapis.com/maps/api/geocode/json?address=Stadion+Feijenoord
Но больше всего нам интересны геоданные:
"geometry" : {
    "location" : {
        "lat" : 51.8939035,
        "lng" : 4.5231352
    },
}
Широта(lat) и долгота (lng) это координаты, которые определяют положение на сфере, в нашем случае на нашей родной планете.
С этими данным можно делать много классных вычислений типа определения дистанции между двумя точками или вычисления какие места расположены в определенном радиусе от точки.

Сохраняем геоданные в MariaDB

В MySQL 5.6 появилась возможность хранения пространственных данных. Теперь вы можете хранить геоданные в специальном типе данных POINT. Примечание: Если хотите потренироваться сами, предлагаю сразу создать песочницу в виртуалке. Я описываю как это сделать в конце статьи. Создадим колонку с типом POINT:
CREATE DATABASE demo;
CREATE TABLE demo.important_locations (location POINT NULL DEFAULT NULL);
Теперь можно вставить широту и долготу:
INSERT INTO demo.important_locations(location) VALUES(GeomFromText('POINT(51.8939035 4.5231352)',0));
А теперь давайте посчитаем расстояние от стадиона до мэрии, где мы отпразднуем наш успех!

Расчет расстояния в MariaDB

Вы ожидаете что будет простая функция для расчета расстояния между координатами? Ну, на самом деле да. Она называется st_distance(g1, g2) и доступна с MySQL 5.6. Но есть нюанс: расстояние вычисляется использую систему координат на плоскости вместо сферических координат. Вы можете прочитать про это в этой статье по Google Maps API.
Кратко: это выражение для расчета дистанции от стадиона к мэрии в координатах (51.9228644,4.4792299):
SELECT (
  6371 * acos(
    cos(radians(51.9228644)) * cos(radians(x(location))) * cos(radians(y(location)) - radians(4.4792299))
    +
    sin(radians(51.9228644)) * sin(radians(x(location)))
  )
) AS distance
FROM demo.important_locations
ORDER BY distance;
И расстояние - 4.409 километра!
+--------------------+
| distance           |
+--------------------+
| 4.4092536956929855 |
+--------------------+
1 row in set (0.00 sec)

Храним геоданные в ElasticSearch

В ElasticSearch тоже есть специальный тип данных geo_point для хранения геометрических данных.
Создадим новый индекс с полем типа geo_point:
curl -XPUT http://localhost:9200/important_locations -d '
{
  "mappings": {
    "location": {
      "properties": {
        "name": {"type": "string"},
        "location": {"type": "geo_point"}
      }
    }
  }
}'
Проверяем:
curl -XGET 'http://localhost:9200/important_locations/_mapping'
{
  "important_locations":{
    "location":{
      "properties":{
        "location":{
          "type":"geo_point"
        },
        "name":{
          "type":"string"
        }
      }
    }
  }
}
Теперь добавим несколько мест в наш индекс:
curl -XPOST http://localhost:9200/important_locations/location/ -d '{"name": "Fanshop Centraal Station Rotterdam", "location": {"lat": "51.924285", "lon": "4.469892"}}'
curl -XPOST http://localhost:9200/important_locations/location/ -d '{"name": "Fanshop Stadion", "location": {"lat": "51.893423", "lon": "4.525188"}}'
curl -XPOST http://localhost:9200/important_locations/location/ -d '{"name": "Fanshop Station de Kuip", "location": {"lat": "51.891288", "lon": "4.513916"}}'
curl -XPOST http://localhost:9200/important_locations/location/ -d '{"name": "Fanshop Coolsingel", "location": {"lat": "51.91862", "lon": "4.480092"}}'
Я хочу узнать какие фанатские магазины расположены рядом с мэрией с сортировкой результатов по расстоянию:
curl -XGET 'http://localhost:9200/important_locations/_search?pretty=true' -d '
{
    "sort" : [
        {
            "_geo_distance" : {
                "location" : {
                    "lat" : 51.92286439999999,
                    "lon" : 4.479229999999999
                },
                "order" : "asc",
                "unit" : "km"
            }
        }
    ],
    "query": {
        "filtered" : {
            "query" : {
                "match_all" : {}
            }
        }
    }
}'
Расстояние в поле sort :
{
  "took" : 173,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 4,
    "max_score" : null,
    "hits" : [ {
      "_index" : "important_locations",
      "_type" : "location",
      "_id" : "OpuBlM6nQFOZ5lCEcBVA8w",
      "_score" : null, "_source" : {"name": "Fanshop Coolsingel", "location": {"lat": "51.91862", "lon": "4.480092"}},
      "sort" : [ 0.47564430077142694 ]
    }, {
      "_index" : "important_locations",
      "_type" : "location",
      "_id" : "xL0Oy5XqRs-DgZQqjMgTiQ",
      "_score" : null, "_source" : {"name": "Fanshop Centraal Station Rotterdam", "location": {"lat": "51.924285", "lon": "4.469892"}},
      "sort" : [ 0.6595521711295553 ]
    }, {
      "_index" : "important_locations",
      "_type" : "location",
      "_id" : "RwhR9pXuRP2GBse3JmjdGA",
      "_score" : null, "_source" : {"name": "Fanshop Station de Kuip", "location": {"lat": "51.891288", "lon": "4.513916"}},
      "sort" : [ 4.241464778143902 ]
    }, {
      "_index" : "important_locations",
      "_type" : "location",
      "_id" : "5TV1jQm1ROqvbD3oFR0k7Q",
      "_score" : null, "_source" : {"name": "Fanshop Stadion", "location": {"lat": "51.893423", "lon": "4.525188"}},
      "sort" : [ 4.5449626829339085 ]
    } ]
  }
}
Меньше полу километра! Круто, правда? Можно еще лучше! Давайте оставим только магазины в километровом радиусе от стадиона:
curl -XGET 'http://localhost:9200/important_locations/_search?pretty=true' -d '
{
  "query": {
    "filtered" : {
        "query" : {
            "match_all" : {}
        },
        "filter" : {
            "geo_distance" : {
                "distance" : "1km",
                "location" : {
                    "lat" : 51.8939035,
                    "lon" : 4.5231352
                }
            }
        }
    }
  }
}'
{
  "took" : 15,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 2,
    "max_score" : 1.0,
    "hits" : [ {
      "_index" : "important_locations",
      "_type" : "location",
      "_id" : "RwhR9pXuRP2GBse3JmjdGA",
      "_score" : 1.0, "_source" : {"name": "Fanshop Station de Kuip", "location": {"lat": "51.891288", "lon": "4.513916"}}
    }, {
      "_index" : "important_locations",
      "_type" : "location",
      "_id" : "5TV1jQm1ROqvbD3oFR0k7Q",
      "_score" : 1.0, "_source" : {"name": "Fanshop Stadion", "location": {"lat": "51.893423", "lon": "4.525188"}}
    } ]
  }
}
Видим, что условию удовлетворяют только два из четырех магазинов. Надо отметить из на карте!

Совет профи: пользовательские типы в Doctrine.

Я показал как добавить POINT в MariaDB используя функцию GeomFromText. На практике мы используем Doctrine ORM чтобы управлять такими данными. Вы можете создать свой тип данных в Doctrine для таких вещей. И вам повезло, что кто-то уже это сделал. Я пробовал пакет creof/doctrine2-spatial. Он позволяет использовать тип POINT в анотации:
<?php
use CrEOF\Spatial\PHP\Types\Geometry\Point;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table(name="important_locations")
 * @ORM\Entity
 */
class ImportantLocation
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var Point
     *
     * @ORM\Column(name="location", type="point", nullable=true)
     */
    private $location;

    /**
     * @param Point $location
     */
    public function setLocation(Point $location)
    {
        $this->location = $location;
    }
}
Это позволяет добавлять местоположение еще проще:
$importantLocation = new ImportantLocation()
$importantLocation->setLocation(new Point(51.8939035, 4.5231352));


Настраиваем тестовое окружение

В этой статье я использовал песочнику от Vagrant на Ubuntu 14.04. Мой Vagrantfile:
Vagrant.configure(2) do |config|

  config.vm.box = "ubuntu/trusty64"

end
Потом ставил MariaDB и ElasticSearch:
sudo apt-get install software-properties-common -y
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
sudo add-apt-repository 'deb http://ams2.mirrors.digitalocean.com/mariadb/repo/10.0/ubuntu trusty main'
sudo apt-get update
sudo apt-get install mariadb-server -y

sudo apt-get install openjdk-7-jre-headless -y
wget -qO - http://packages.elasticsearch.org/GPG-KEY-elasticsearch | sudo apt-key add -
echo "deb http://packages.elasticsearch.org/elasticsearch/1.3/debian stable main" | sudo tee -a /etc/apt/sources.list
sudo apt-get update
sudo apt-get install elasticsearch
sudo service elasticsearch start

Оригинал статьи на английском языке: http://labs.qandidate.com/blog/2014/09/09/having-fun-with-geometry-data-in-mariadb-and-elasticsearch/
Отправить комментарий