SQLiteで空間検索

、といっても、SpatiaLiteのことではないです。。。

SQLiteは、たくぼさんの記事にあるように、MapServerでは、OGR経由で表示することができる。けれども、空間インデックスや空間検索のようなものをもっていないので、道路ネットワークのノードみたいな、たくさんのポイントを、layerObj.queryByPointで検索をかけると、みつけるのにとっても時間がかかる。常套手段としては、Shapefileに変換して、MS4Wに含まれるshptree(C:¥ms4w¥tools¥mapserv¥shptree)で空間インデックスを作ると速くなる。座標系変換も時間がかかるので、これもあらかじめあわせておくとなおよい。

ただ今回は、SQLite上では動的にポイントデータをつくったりするので、SQLiteのままで使いたい。

そこでググってみると、とってもいい回答をしているスレッドを見つけた。

Re: [sqlite] Spatial searches

対象はポイントデータなのでなおよい。
・緯度経度やXYのカラムを作っておいて、インデックスを作成
create table pts (
id integer primary key,
lat real,
lng real,
data text
);

create index lat_idx on pts(lat);
create index lng_idx on pts(lng);

・select文で、between を使って絞り込む
select * from pts where id in
(
select id from pts where lat between :min_lat and :max_lat
intersect
select id from pts where lng between :min_lng and :max_lng
);


これだけなのですが、SQLはNewbieな私にとっては、この解決策はとってもカッコよく思いました。SQL の中に、intersect というのがあるのですね。といっても、空間を対象にした演算子ではなく、2つのselect文の交差を取るということのようです(参照

layerObj.queryByPoint の引数の最後には、buffer というのがあり、与えられた点の周辺どのくらいの範囲を検索するかというパラメータが与えられています。
public int queryByPoint(
mapObj map,
pointObj point,
int mode,
double buffer
);

Public Function queryByPoint(ByVal map As mapObj, ByVal point As pointObj, ByVal mode As Integer, ByVal buffer As Double) As Integer


上記のselect文にある、:min_lat、:max_lat、min_lng and :max_lng の部分に、buffer 相当の値を入れれば、SQLite上で、layerObj.queryByPointとほぼ同じ機能を持つことになります。

buffer 相当の計算は、MapServerのマップ上で、たとえば5ピクセル相当の距離を出して与えれるようにすると、ちょうどよいです。

Dim _buffer As Double
_buffer = 5 * (map.extent.maxx - map.extent.minx) /map.width


さて、実際にマップ上で、OGR経由で表示させたSQLiteに対しては次のようにして、検索をかけるようにしました。
・マップ上のクリック点の取得→マウスのX,Yの値を取得
・Yaskeyさんのサイトにある、Pix2Geoを参考にして、緯度経度に変換。
・buffer を計算(前掲)
・上記select文を使って、毎回 Viewを作成

MapScriptが、layerObj.queryByPoint をテレんこテレんこと実行するよりか、コストがかかりそうな処理であろうとも、クリックされるたびに、Viewを消して作り直す処理のほうが、はるかに高速です。それに、SQLiteは、ローカルで自分だけが使っているし、そもそも高速だし^^;

(追記)
実際のポイントの点数として、7000点のものと、10万点、30万点のものでためしてみました。7000点と10万点はクリックした瞬間に結果を得ることができました。さすがに30万点は10秒くらい検索にかかりました。30万点でも、7000点、10万点と同じく、クリックした瞬間に結果を得ることができました(前掲訂正)。ポリゴンやラインでもMBR(最小包囲矩形)のmin,max を使うことで応用が利きそうです。

MapServerは、SQLiteのViewテーブルからでも地図にプロットすることができます。最後にマップファイル例を挙げておきます。

 Dim georefpoint As System.Drawing.PointF
 georefpoint = Pix2Geo(_map, clientPoint)
 '検索時のバッファーを現在の縮尺における5ピクセルあたりの長さにする
 Dim _dblBuffer As Double
 _dblBuffer = 5 * (_map.extent.maxx - _map.extent.minx) / _map.width
 
 strNWPt(0) = roadnw.CreateRoadNWView(georefpoint, _dblBuffer)


Public Function CreateRoadNWView(ByVal geopoint As PointF, ByVal buffer As Double) As String
'Dim conn As New SQLiteConnection("Version=3;Data Source=..¥map¥23.db;New=True;Compress=True;")

'データベースを SQLiteConnection parameters 通りにオープン
conn.Open()

'SQLコマンド用のクラス
'Dim cmd As New SQLiteCommand

'SQLコマンド用のクラスに開いたデータベースの情報を与える
cmd = conn.CreateCommand

'トランザクションクラス
'Dim Transact As SQLiteTransaction

'トランザクションスタート
Transact = conn.BeginTransaction

'SQLを登録
cmd.CommandText = "DROP view IF EXISTS test_v"

'SQLを実行
cmd.ExecuteNonQuery()

'SQLを登録
cmd.CommandText = _
"create view test_v as select * from nodenwSQ where OGC_FID in " & _
"( " & _
"select OGC_FID from nodenwSQ where x between " & geopoint.X - buffer & " and " & geopoint.X + buffer & _
" intersect " & _
"select OGC_FID from nodenwSQ where y between " & geopoint.Y - buffer & " and " & geopoint.Y + buffer & ")"

'Debug.WriteLine(cmd.CommandText)

'SQLを実行
cmd.ExecuteNonQuery()

'SQLを登録
cmd.CommandText = "select * from test_v"

'SQLのリーダークラス
Dim Rdr As SQLiteDataReader

'SQLを実行し、リーダーに格納
Rdr = cmd.ExecuteReader

Dim strTempValue As Integer

'Try
If Rdr.HasRows Then
Rdr.Read()
strTempValue = Rdr.GetInt32(2).ToString
Rdr.Close()
conn.Close()
Return Val(strTempValue)
Else
Rdr.Close()
'データをコミット
'Transact.Commit()
conn.Close()
Return 0
End If


Rdr.Close()

'データをコミット
Transact.Commit()

conn.Close()

Return strTempValue

End Function


■マップファイル SQLite Viewを使った表示
LAYER
CONNECTION "23.db,test_v"
CONNECTIONTYPE OGR
LABELCACHE OFF
NAME "SQLite_v"
POSTLABELCACHE TRUE
PROJECTION
"init=epsg:4612"
END
STATUS OFF
TEMPLATE "template.html"
TYPE POINT
UNITS METERS
CLASS
NAME "SQLite_v"
STYLE
ANGLE 360
COLOR 128 0 128
OPACITY 100
OUTLINECOLOR 255 128 128
SIZE 5
SYMBOL "circle"
WIDTH 2
END
END
END
bugna | SQLite | 09:29 | comments(0) | trackbacks(0) | - | - |

SQLiteってば速い(with トランザクション)

MapScriptやGDAL/OGRをデスクトップのアプリケーション開発に使いだすと、一時的に検索した地物などを書き出したくなる。

PostgeSQL+PostGISを使っていれば、select文の結果をテーブルにinsertすればよいのだけれども、デスクトップのアプリケーションは、ネットワークに接続できない事態も想定して、サーバーへの問い合わせをしなくもいいようにしたい。

MapScriptを使うのであれば、mapObj.saveQuerymapObj.loadQueryを使うと、複数の検索結果を保存したり読み込んだりできるようになって便利である。ただ、検索結果が数十万件もあるとちょっと使うが不便である。

VB(.NET)からSQLiteをODBCやADO.NET経由でアクセスするところを調べていた(1,2)のは、この一時置き場としてSQLiteが使えないかと思ったからである。

ローカルで使える SQLiteは、トランザクションの開始、コミットを使うと爆速である。
Googleで、『SQLite トランザクション』で検索すると、いろいろな人がその速さの比較記事を書いている通りで、80万件の書き込みで、1分足らずで終了する。Group By によるグループ化を実行して、30万点に圧縮するのに、3分かかる。

空間データ基盤25000には、道路ネットワークのリンクとノードの情報があるが、同じ位置に複数のノードがあったりして、ネットワークのデータベースを作る上でちょっと困るところがある。ノードとリンクがつながらなくなるからだ。

そこで、リンクだけを使って、MapScriptで、始点・終点の座標値X,Yを取得する。2本のリンクが端点で接してる場合、当然同じ点を登録(重複)することになるが、とりあえず、X、Yを取得したら、SQLiteでテーブルに書き込んでしまう。

この、
・MapInfoテーブル内のすべてのポリラインの始終点の取得
・SQLiteでテーブルに、Insert
・上記で作成されたテーブルのX、Yの値を使った文字列でGropu Byでグループ化
という処理が、MapScriptとSQLiteプロバイダーの組み合わせにも関わらず、びっくりするほど速くて、とても嬉しい裏切りをしてくれました。

1件あたり、40万のリンクに対して、重複した点がある場合の始終点のレコード数は約80万件はちょうど60秒。そこから重複点の除去のための、select * from ○○ group by X_Y みたいな処理で、ノードの数を圧縮するまでに、3分かかる。

処理したデータを永続化するのに、バイナリーファイルに書き込んだりしていたけれども、SQLで検索できてこのスピードがあるならば、いままでのノウハウはもういらん。
SQLiteを本格的に空間処理に応用しようかと思った次第だ。

当然、Python でも SQLite は使える。スクリプト言語であるけれども、どのくらいの速度が出るのか楽しみだ。
bugna | SQLite | 23:50 | comments(0) | trackbacks(0) | - | - |

VB(.NET)でSQLiteへ接続

AIR(Adobe Integrated Runtime)Google Gears でも、使われているSQLite ですが、MapServerはOGR経由でSQLiteに接続できるようで、たくぼさんのページに情報があります。

FWToolsに含まれている ogr2ogrでも、ずいぶん前からサポートしているようです。

バグな明保野では、VB(.NET)のWindows Forms で、MapScript(C#)を使い倒そうという主旨で情報をのっけていますが、まぁ、ニッチな分野なので、世のニーズは特に高くはありません。

デスクトップアプリケーションとして、汎用GIS並みの機能を作りこみ、さまざまな情報を地図に表示するのが目的であるので、雑多なポイント情報や、SQLを使って頻繁に検索をかけたい場合は、RDBを使いたくなります。

当然、PostgreSQL+PostGISを使えよ、というのが大方のアドバイスになるのですが、デスクトップアプリケーションは、大勢の人に使われることと、「オフライン」でも使えるようにというのが、条件であるため、PostgreSQLを採用は躊躇してしまいます。

で、SQLiteを使おうと思って、自分用のメモとして、やったことを挙げておきます。
続きを読む >>
bugna | SQLite | 11:03 | comments(0) | trackbacks(0) | - | - |
1/1PAGES | |

11
--
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--
>>
<<
--