sqlite中的virtual表之空间索引

sqlite有多种虚表，如下：

 - 'VirtualShape' [direct Shapefile access]
 - 'VirtualDbf'  [direct DBF access]
 - 'VirtualXL'  [direct XLS access]
 - 'VirtualText'  [direct CSV/TXT access]
 - 'VirtualNetwork' [Dijkstra shortest path]
 - 'RTree'  [Spatial Index - R*Tree]
 - 'MbrCache'  [Spatial Index - MBR cache]
 - 'VirtualSpatialIndex' [R*Tree metahandler]
 - 'VirtualFDO'  [FDO-OGR interoperability]
 - 'SpatiaLite'  [Spatial SQL - OGC]

今天总结一下空间索引表的使用。空间索引表应该有多种形式，现在接触到的是rtree表的使用。

建立rtree表：

./spatialite $db "CREATE VIRTUAL TABLE sp_landuselines USING rtree(pkid,minx,maxx,miny,maxy);"
if [ $? != 0 ]
then
    echo "error in creating virtual table..."
    exit 1
fi

插入数据：

./spatialite $db "attach './merged_bj55_dh.sq3' as dh;insert into sp_landuselines (pkid, minx, maxx, miny, maxy) select Id,MbrMinX(GeomWGS84),MbrMaxX(GeomWGS84),MbrMinY(GeomWGS84),MbrMaxY(GeomWGS84) from dh_lue where landusetype = 1;"
if [ $? != 0 ]
then
    echo "error in insertting virtual table..."
    exit 1
fi

效率分析：

### why spalite index? ###
### test case : 392855      11620818.0  11620897.0  3986194.0   3986228.0
./spatialite $db ".timer on;select pkid from sp_landuselines where minx<=11620818.0 and maxx>=11620897.0 and miny<=3986194.0 and maxy>=3986228.0;"
./spatialite $dhdb ".timer on;select id from dh_lue where landusetype = 1 and MbrMinX(GeomWGS84)<=11620818.0 and MbrMaxX(GeomWGS84)>=11620897.0 and MbrMinY(GeomWGS84)<=3986194.0 and MbrMaxY(GeomWGS84)>=3986228.0;"

结果如下：

sqlite> select pkid from sp_landuselines where minx<=11620818.0 and maxx>=11620897.0 and miny<=3986194.0 and maxy>=3986228.0;
pkid      
----------
364690    
138510    
365788    
138995    
385418    
392855    
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> select id from dh_lue where landusetype = 1 and MbrMinX(GeomWGS84)<=11620818.0 and MbrMaxX(GeomWGS84)>=11620897.0 and MbrMinY(GeomWGS84)<=3986194.0 and MbrMaxY(GeomWGS84)>=3986228.0;
138510
138995
364690
365788
385418
392855
Run Time: real 0.198 user 0.120007 sys 0.076005

其实从结果是可以看出端倪的，效率有很大的提升。

所以如果有空间查询方面的需求，可以考虑一下sqlite。