|
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。 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
