Code for History

"Code for History"はIT技術を歴史学上の問題の解決に使うコミュニティです。強調したいのは、我々にとってIT技術は「手段」であって「目的」ではありません。「目的」は歴史学上の問題を解決する事であって、必要であればITでない手段も活用します。常に最優先なのは、問題を解決することです。

主要3空間DBでの、空間Index, 空間検索SQL記述の違いまとめ

最近、業務ではなく余暇に複数人で地図系開発をしているため、開発者の構築可能環境が揃えられずバックエンドが選べない状況でした。
なのでバックエンドを抽象化しないといけなかったのですが、その結果Web開発系の主要3空間DBでの空間検索記述差がわかったので簡単にまとめておきます。

座標値からのGeometryオブジェクトの作成

GeomFromTextで当然…と思ってましたが、PostGISだと通らなくなってます。
PostGISでは空間系関数は頭にSTをつけることで統一したよう。
頭にSTをつけた関数については、spatialiteも対応してますし、mysql最新の5.6でも未対応なものの、後述の通り関係性記述の関数では(既存関数との互換性のためとはいえ)ST_系を出してきているので、将来的にはST_系で統一されるものと思います。

蓄積済みデータに対する、検索クエリの差

蓄積済みデータに対して、MBRで篩をかけつつ、蓄積ジオメトリとの交差関係を得る際のクエリの違いです。
テーブル名をgeomtable、蓄積ジオメトリカラムをthegeom、検索ジオメトリをneedleとします。

PostGISの場合

WHERE needle && thegeom AND ST_Intersects(needle, thegeom)

安定です。10年近く?変わらないこの安定感。いぶし銀です。
まあ、その10年近く前にST_指定があったかどうかは覚えてないですが…。
簡単に説明すると、前半の&&は、thegeomカラム内の各ジオメトリの外接矩形で、インデクスによる篩をかけています。
その後のST_Intersectsで、正確な交差判定を行ってます。
この辺はもう定石なので、いちいち&&しなくてもST_Intersectsをする際には自動で&&処理をするようにするとかしないとかいう話があった気がしますが、実際に対応したかは未確認です。
もし最新版でしていたとしても、PostGISのバージョン依存しないようにするなら、やっておいて損はないかなと思います。

mysql5.6以降の場合

WHERE MBRIntersects(needle,thegeom) AND ST_Intersects(needle,thegeom)

mysqlは5.6で大きく空間検索系の機能が加わったようです。
それ以前ではできなかったジオメトリベースの交差検索等もできるようになった模様。
PostGISでいうところの&&がMBRIntersects関数になったと思えば、理解できると思います。

mysql5.5以前の場合

WHERE MBRIntersects(needle,thegeom) AND Intersects(needle,thegeom)

ST_が抜けただけですが、大きく違います。
残念ながら、mysql5.5以前では他のDBと同じ事はできません。
mysql5.5以前では、各ジオメトリの外接矩形同士の交差判定しかできないためです。
つまり、MBRIntersectsとIntersectsの返す結果は一緒です。
これに5.6でジオメトリ引き当ての関数を加えるにあたって、これまでMBRIntersectsとIntersectsが同じ結果を返すのがおかしかったのですが、後方互換性を重視して、MBRIntersectsとIntersectsは同じ動作を保ったまま、本来のIntersectsの動作をするST_Intersects関数を加える形にしたようです。
よって、mysql5.6でも、Intersects関数を使うと正確なジオメトリ引き当てはできません。

もっとも、正確なジオメトリ引き当てできないと話にならないGIS用途ならともかく、事実上矩形検索で十分のジオメディア用途ならば、日付変更線越えのケースでもない限り矩形検索で十分でしょう。

spatialiteの場合

これが一番やっかいでした。
意味を理解すれば、なるほどsqliteの基本構成をうまく使ってるな、とは思うのですが、直感的でないのでとっつきにくさは否めなかったです。

WHERE Intersects(needle, thegeom) AND ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name='geomtable' AND search_frame=needle);

Intersectsの部分は、他のDBの同名、或いはST_付き関数と一緒ですね。
後半ですが、そのまま読むと、
「SpatialIndexというテーブルの中で、f_table_nameカラムの値が'geomtable'、search_frameカラムがneedleの列を検索し、その結果に含まれるROWIDカラムの値リストと同じROWIDを持つ列を選択」
という意味になります。
spatialiteの各テーブルのSpatialIndex情報は、全てシステムが生成するSpatialIndexテーブルに収められるのですが、その中で空間検索を行い、その結果で元テーブルの検索条件をROWIDで絞り込む、という形のようです。
search_frame=needleのところがイマイチよく判りませんが、その辺はspatialiteの実装がよしなにやってくれているのでしょう。


とりあえずこのくらいのところを抽象化すれば、ジオメディア用途レベルでの空間検索では切り替えて運用できる感じです。
今は私的開発にはperlを使っているのですが、TengやSQL::Makerあたりとも、conditionをrow SQL指定するインタフェースを通じて、共存できてます。

おまけ:日付変更線越えの際の矩形挿入

空間DBにジオメトリ入れる際って、日付変更線越える場合、経緯度を-180〜180に正規化するとジオメトリの形が変わってきちゃうし、正規化しないと検索できなくなる領域が出てくる気がするんだけど、みんなどうやってるんだろう?
私はここ5年来ずっと、ジオメディアなので矩形挿入しか実質ないのだけど、下のように2つに分けるやり方でやってます。
あまりこの辺の情報聞かないので、一応共有。
むしろ他の事例を聞きたい。

sub latlng2wkt {
my ($min_lat, $min_lng, $max_lat, $max_lng) = @_;
my $wkt = '';

if (!defined($max_lat)) {
$wkt = 'POINT(' . $min_lng . ' ' . $min_lat . ')';
} else {
if ($min_lng > $max_lng) {
$wkt = 'MULTIPOLYGON(((' .
'-180.00 ' . $max_lat . ', ' .
$max_lng . ' ' . $max_lat . ', ' .
$max_lng . ' ' . $min_lat . ', ' .
'-180.00 ' . $min_lat . ', ' .
'-180.00 ' . $max_lat . ')), *1';
}
}
return $wkt;
}

*1:' . $min_lng . ' ' . $max_lat . ', ' . '180.00 ' . $max_lat . ', ' . '180.00 ' . $min_lat . ', ' . $min_lng . ' ' . $min_lat . ', ' . $min_lng . ' ' . $max_lat . ')))'; } else { $wkt = 'POLYGON((' . $min_lng . ' ' . $max_lat . ', ' . $max_lng . ' ' . $max_lat . ', ' . $max_lng . ' ' . $min_lat . ', ' . $min_lng . ' ' . $min_lat . ', ' . $min_lng . ' ' . $max_lat . '

© Code for History