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,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。