🗺️

各種DBでの平面座標位置検索まとめ

2022/07/13に公開約7,000字

概要

いくつかのDBで緯度経度を登録した地点を位置検索する検証を行ったため、備忘として残してみます。

  • Elasticsearch 6.8
  • MySQL 5.7
  • MongoDB 5.0.7

検証内容

東京駅と池袋駅の座標をDBに登録しておき、秋葉原駅の座標を基点に円形で範囲検索する方法で検証します。
それぞれの経度,緯度はざっくり以下としています。

  • 東京駅: 139.76732, 35.681246
  • 池袋駅: 139.711214, 35.729888
  • 秋葉原駅: 139.773948, 35.699129

Elasticsearch

データの位置情報にgeo_pointタイプを利用し、検索には円形のgeo_distanceクエリを利用して検索します。

https://www.elastic.co/guide/en/elasticsearch/reference/6.8/geo-point.html
https://www.elastic.co/guide/en/elasticsearch/reference/6.8/query-dsl-geo-distance-query.html
実行例(curl)

geo_pointタイプのフォーマットでデータに緯度経度を付与して登録するだけでは位置検索に利用できないため、事前にマッピングをしてインデックスを作成

mapping.json
{
  "mappings": {
    "_doc": {
      "properties": {
        "location": {
          "type": "geo_point"
        }
      }
    }
  }
}
$ curl -X PUT -H "Content-Type:application/json" -d @mapping.json http://localhost:9200/sample

データをbulkで登録
位置情報をlocationというフィールドで配列([経度, 緯度])で保存

data.json
{"index" : {}}
{"name": "Tokyo Station", "location": [139.76732, 35.681246]}
{"index" : {}}
{"name": "Ikebukuro Station", "location": [139.711214, 35.729888]}
$ curl -X PUT -H "Content-Type:application/json" --data-binary @data.json http://localhost:9200/sample/_doc/_bulk

秋葉原駅の座標から2.5km圏内を検索(東京駅のみ該当)

query.json
{
  "query": {
    "bool": {
      "must": {
        "match_all": {}
      },
      "filter": {
        "geo_distance": {
          "distance": "2.5km",
          "location": {
            "lat" : 35.699129,
            "lon" : 139.773948
          }
        }
      }
    }
  }
}
$ curl -X GET -H "Content-Type:application/json" -d @query.json  http://localhost:9200/sample/_search?pretty
response
{
  "took" : 7,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "sample",
        "_type" : "_doc",
        "_id" : "H-kV8YEBEuifxz1Re_VJ",
        "_score" : 1.0,
        "_source" : {
          "name" : "Tokyo Station",
          "location" : [
            139.76732,
            35.681246
          ]
        }
      }
    ]
  }
}

7km圏内を検索(東京駅、池袋駅の両方が該当)

query.json
{
  "query": {
    "bool": {
      "must": {
        "match_all": {}
      },
      "filter": {
        "geo_distance": {
          "distance": "7km",
          "location": {
            "lat" : 35.699129,
            "lon" : 139.773948
          }
        }
      }
    }
  }
}
response
{
  "took" : 11,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 2,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "sample",
        "_type" : "_doc",
        "_id" : "IOkV8YEBEuifxz1Re_VJ",
        "_score" : 1.0,
        "_source" : {
          "name" : "Ikebukuro Station",
          "location" : [
            139.711214,
            35.729888
          ]
        }
      },
      {
        "_index" : "sample",
        "_type" : "_doc",
        "_id" : "H-kV8YEBEuifxz1Re_VJ",
        "_score" : 1.0,
        "_source" : {
          "name" : "Tokyo Station",
          "location" : [
            139.76732,
            35.681246
          ]
        }
      }
    ]
  }
}

MySQL

空間データ型のうち点座標を保持できるPOINT型を使用し、ST_Distance_Sphere()で座標間の距離を算出して検索します。

https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-distance-sphere
実行例(sql)

POINT型のlocationカラムを持つplacesテーブルを作成

query
> CREATE TABLE places (name VARCHAR(32) NOT NULL, location POINT NOT NULL);

緯度経度をST_GeomFromText()で内部ジオメトリ形式に変換してデータ登録
ST_GeomFromText("POINT(経度 緯度)")

query
> INSERT INTO places VALUES ("Tokyo Station", ST_GeomFromText("POINT(139.76732 35.681246)"));
> INSERT INTO places VALUES ("Ikebukuro Station", ST_GeomFromText("POINT(139.711214 35.729888)"));

https://dev.mysql.com/doc/refman/5.7/en/populating-spatial-columns.html

ST_Distance_Sphere()で基点とlocationの距離(m)を算出し、その算出距離と閾値となる距離を比較して検索
基点となる位置も内部ジオメトリ形式に変換
POINT型から値を取得する場合はST_X()および_ST_Y()を利用

query(秋葉原駅から5km以内)
> SET @base = ST_GeomFromText("POINT(139.773948 35.699129)");
> SET @threshold = 5000;
> SELECT name, ST_X(location) as lng, ST_Y(location) as lat FROM places WHERE ST_Distance_Sphere(location, @base) <= @threshold;
result
name	lng	lat
Tokyo Station	139.76732	35.681246
query(秋葉原駅から10km以内)
> SET @threshold = 5000;
> SELECT name, ST_X(location) as lng, ST_Y(location) as lat FROM places WHERE ST_Distance_Sphere(location, @base) <= @threshold;
result
name	lng	lat
Tokyo Station	139.76732	35.681246
Ikebukuro Station	139.711214	35.729888

MongoDB

座標保持フィールドのインデックスに2dsphereを利用して、閾値にメートルを利用して検索します。

https://www.mongodb.com/docs/manual/geospatial-queries/
実行例(mongosh)

placesコレクション作成

> db.createCollection("places", {})

locationフィールドに対し2dsphereインデックスを作成

> db.places.createIndex({"location": "2dsphere"})

検索対象となるドキュメント登録
locationフィールドはGeoJSONオブジェクトとして登録します。

> db.places.insertMany([
    {
        "name": "Tokyo Station",
        "location": {
            "type": "Point",
            "coordinates": [139.76732, 35.681246]
        }
    },
    {
        "name": "Ikebukuro Station",
        "location": {
            "type": "Point",
            "coordinates": [139.711214, 35.729888]
        }
    },
])

秋葉原駅(139.773948, 35.699129)を基点にして検索

秋葉原から2.5km圏内

> db.places.find(
    {
        location: {
            $near: {
                $geometry: {
                    type: "Point",
                    coordinates: [139.773948, 35.699129]
                },
                $minDistance: 0,
                $maxDistance: 2500
            }
        }
    }
)

結果

[
  {
    _id: ObjectId("6285e719d6b6ca8271dfd088"),
    name: 'Tokyo Station',
    location: { type: 'Point', coordinates: [ 139.76732, 35.681246 ] }
  }
]

秋葉原から7km圏内

> db.places.find(
    {
        location: {
            $near: {
                $geometry: {
                    type: "Point",
                    coordinates: [139.773948, 35.699129]
                },
                $minDistance: 0,
                $maxDistance: 7000
            }
        }
    }
)

結果

[
  {
    _id: ObjectId("6285e719d6b6ca8271dfd088"),
    name: 'Tokyo Station',
    location: { type: 'Point', coordinates: [ 139.76732, 35.681246 ] }
  },
  {
    _id: ObjectId("6285e719d6b6ca8271dfd089"),
    name: 'Ikebukuro Station',
    location: { type: 'Point', coordinates: [ 139.711214, 35.729888 ] }
  }
]

まとめ

シンプルな2次元座標の位置情報検索であれば多くのDBが対応していました。
しかし距離算出の精度やインデックスの利用による速度などは異なってくると思いますので、もし自由にDB選択できるのならばもう少し詰めて調査が必要そうです。
もし何かしら皆様の参考になれば幸いです。

Discussion

ログインするとコメントできます