各種DBでの平面座標位置検索まとめ
概要
いくつかの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クエリを利用して検索します。
実行例(curl)
geo_pointタイプのフォーマットでデータに緯度経度を付与して登録するだけでは位置検索に利用できないため、事前にマッピングをしてインデックスを作成
{
"mappings": {
"_doc": {
"properties": {
"location": {
"type": "geo_point"
}
}
}
}
}
$ curl -X PUT -H "Content-Type:application/json" -d @mapping.json http://localhost:9200/sample
データをbulkで登録
位置情報をlocationというフィールドで配列（[経度, 緯度]）で保存
{"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": {
"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
{
"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": {
"bool": {
"must": {
"match_all": {}
},
"filter": {
"geo_distance": {
"distance": "7km",
"location": {
"lat" : 35.699129,
"lon" : 139.773948
}
}
}
}
}
}
{
"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()で座標間の距離を算出して検索します。
実行例(sql)
POINT型のlocationカラムを持つplacesテーブルを作成
> CREATE TABLE places (name VARCHAR(32) NOT NULL, location POINT NOT NULL);
緯度経度をST_GeomFromText()で内部ジオメトリ形式に変換してデータ登録
ST_GeomFromText("POINT(経度 緯度)")
> 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)"));
ST_Distance_Sphere()で基点とlocationの距離(m)を算出し、その算出距離と閾値となる距離を比較して検索
基点となる位置も内部ジオメトリ形式に変換
POINT型から値を取得する場合はST_X()および_ST_Y()を利用
> 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;
name lng lat
Tokyo Station 139.76732 35.681246
> SET @threshold = 5000;
> SELECT name, ST_X(location) as lng, ST_Y(location) as lat FROM places WHERE ST_Distance_Sphere(location, @base) <= @threshold;
name lng lat
Tokyo Station 139.76732 35.681246
Ikebukuro Station 139.711214 35.729888
MongoDB
座標保持フィールドのインデックスに2dsphereを利用して、閾値にメートルを利用して検索します。
実行例(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