富山のホームページ製作会社・グリーク スタッフブログ - ブログ -
  1. グリークトップ
  2. プログラマー
  3. ブログ

SymfonyとDoctrine ORMでMySQLの空間インデックス(SPATIAL INDEX)を扱う方法

アプリ開発においては気軽に位置情報を取得できるため、ユーザーの緯度経度から何かをする機会が多くあります。
たとえば、「現在地に近い順にお店を検索する」といった要件である場合、

  1. ユーザーの位置情報をサーバーに送信
  2. サーバーはお店の座標から距離を計算し、近い順にデータを返す

このような処理が必要になります。
MySQLを利用してこの処理を行う場合は、座標データをgeometry型で保存し、
空間インデックスをつかうことで高速に処理することができます。

MySQL5.7からはストレージエンジンがInnoDBでもSPATIAL INDEXを使えるようになりました。
これによってSymfony+Doctrine ORMの組み合わせでも空間インデックスの恩恵を受けられるようになりました。
Symfony3 + MySQL5.7の組み合わせでSPATIAL INDEXを構築し、2点間の距離を取得する方法を紹介します。

何らかの事情でMySQL5.7が使えない方は別の方法がありますので、次のセクションは読み飛ばして下さい。

 

Symfonyのセットアップ

まずはDoctrineでgeometry型を使うためのライブラリをインストールします。

Doctrine2-Spatial
https://github.com/creof/doctrine2-spatial

Symfonyプロジェクトに次の通り依存関係を追加します。

composer require creof/doctrine2-spatial

次にconfig.ymlにDoctrineのカスタムタイプを定義します。

doctrine:
    dbal:
        types:
            geometry:   CrEOF\Spatial\DBAL\Types\GeometryType
            point:      CrEOF\Spatial\DBAL\Types\Geometry\PointType
            polygon:    CrEOF\Spatial\DBAL\Types\Geometry\PolygonType
            linestring: CrEOF\Spatial\DBAL\Types\Geometry\LineStringType

つづけてカスタムDQL関数の定義を追加します。

doctrine:
    orm:
        dql:
            numeric_functions:
                st_contains:     CrEOF\Spatial\ORM\Query\AST\Functions\MySql\STContains
                contains:        CrEOF\Spatial\ORM\Query\AST\Functions\MySql\Contains
                st_area:         CrEOF\Spatial\ORM\Query\AST\Functions\MySql\Area
                st_geomfromtext: CrEOF\Spatial\ORM\Query\AST\Functions\MySql\GeomFromText
                st_intersects:   CrEOF\Spatial\ORM\Query\AST\Functions\MySql\STIntersects
                st_distance:     CrEOF\Spatial\ORM\Query\AST\Functions\MySql\STDistance
                st_buffer:       CrEOF\Spatial\ORM\Query\AST\Functions\MySql\STBuffer
                point:           CrEOF\Spatial\ORM\Query\AST\Functions\MySql\Point
                glength:         CrEOF\Spatial\ORM\Query\AST\Functions\MySql\GLength

座標データを持つEntityの作成

Doctrineのカスタムタイプ “geometry” が追加されましたので、早速これを利用したEntityを作成します。

<?php

namespace AppBundle\Entity;

use CrEOF\Spatial\PHP\Types\Geometry\Point;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 * @ORM\Table(name="Location", indexes={
 *     @ORM\Index(columns={"latlng"}, flags={"SPATIAL"})
 * })
 */
class Location
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var string 場所の名前
     * @ORM\Column(name="name", type="string", length=128)
     */
    protected $name;

    /**
     * @var Point 座標
     * @ORM\Column(name="latlng", type="geometry")
     */
    protected $latlng;
}

ポイントは3点あります。

  • geometry型の列は type="geometry" とします。
  • geometry型の列は CrEOF\Spatial\PHP\Types\Geometry\Pointオブジェクトを保管します。
  • @ORM\TableでSPATIALインデックスを定義します。注意点としてSPATIAL INDEXを貼る列はNOT NULLでなければいけません。

座標を登録してみます。

<?php
$location = new Location();
$location->setName('glic');
$location->setLatlng(new Point(36.6705583, 137.239612));

/* @var \Doctrine\Common\Persistence\ObjectManager $manager */
$manager->persist($location);
$manager->flush();

これだけで、後の処理はライブラリがいい感じにやってくれます。

 

2点間の距離を計算し、ソートする

先ほどのLocationテーブルにいくつかデータを登録したとして、ユーザーの入力データをもとに距離の近い順(または遠い順)に並び替えをしてみたいと思います。
ユーザーが入力した緯度/経度がそれぞれ$lat/$lngに保存されているとします。

<?php

$manager->getRepository('AppBundle:Location')
    ->createQueryBuilder('l')
    ->addSelect('ST_Distance(POINT(:lat, :lng), l.latlng) as HIDDEN distance')
    ->setParameter('lat', $lat)
    ->setParameter('lng', $lng)
    ->orderBy('distance', 'ASC')
    ->setMaxResults(10)
    ->getQuery()
    ->getResult();

一連のST_Distance()はorderByに指定できないため、一旦SELECTの対象にする必要があります。
ただしLocationテーブルへのマッピングはしない(できない)ためHIDDENを指定します。
HIDDENを外すとgetResult()の結果が意図しないものになりますので、興味がある方はやってみてください。

このように、空間インデックスを活用することで気軽に距離計算ができるようになります。

geometry型がつかえない場合の計算方法

geometry型が使えないのであれば、自力でやるしかありません。
精度は落ちますが、次の計算式を用います。

  • A = ((地点1の緯度 – 地点2の緯度) / 0.0111) の2乗
  • B = ((地点1の経度 – 地点2の経度) / 0.0091) の2乗
  • 答え = (A + B)の平方根

次のようなSQLになります

SQRT(POWER((:lat1 - :lat2) / 0.0111, 2) + POWER((:lng1 - :lng2) / 0.0091, 2))

お気づきになったかもしれませんが、このままでは”DQL”としては動作しません。
SQRTもPOWERもDoctrineが有効な関数として認識しないためです。
ST_Distance関数のように、上記の2つもカスタム関数として登録する必要があります。
この時オススメのライブラリがbeberlei/DoctrineExtensionsです。

beberlei/DoctrineExtensions
https://github.com/beberlei/DoctrineExtensions

こちらのドキュメントを参考に、使いたいカスタム関数を登録してください。

カスタム関数の作成

このままでも距離計算ができるようになりましたが、少しSQLが長くて読みにくいため、先ほどのクエリをラップする独自の関数を作りたいと思います。

DISTANCE(:lat1, :lng1, :lat2, :lng2)
<?php

namespace AppBundle\Extension\Doctrine\DQL;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class DistanceFunction extends FunctionNode
{

    public $lat1;
    public $lat2;
    public $lng1;
    public $lng2;

    /**
     * @param SqlWalker $sqlWalker
     *
     * @return string
     */
    public function getSql(SqlWalker $sqlWalker)
    {
        $lat1 = $sqlWalker->walkArithmeticPrimary($this->lat1);
        $lng1 = $sqlWalker->walkArithmeticPrimary($this->lng1);
        $lat2 = $sqlWalker->walkArithmeticPrimary($this->lat2);
        $lng2 = $sqlWalker->walkArithmeticPrimary($this->lng2);

        return "SQRT(POWER((" . $lat1 . " - " . $lat2 . ") / 0.0111, 2) + POWER((" . $lng1 . " - " . $lng2 . ") / 0.0091, 2))";
    }

    /**
     * @param Parser $parser
     */
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->lat1 = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->lng1 = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->lat2 = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->lng2 = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

}

作成したカスタム関数を例によって登録します。

doctrine:
    orm:
        dql:
            numeric_functions:
                distance: AppBundle\Extension\Doctrine\DQL\DistanceFunction

これでDISTANCE関数がつかえるようになりました。

リファレンス

この記事を書いたスタッフ
Kazuyuki Hayashi
Symfony / Node.js / React.js / Swift を中心にテクノロジー系の記事を書きます。趣味はコーヒーです。