OK, here are a few test results ... 350,000 points with no additional data
... timings with all data in cache and 17 records selected:
#1
CREATE INDEX "pdata_spatial_index_x" ON "pdata"("gx");
CREATE INDEX "pdata_spatial_index_y" ON "pdata"("gy");
file size 24,200 KB, query time ~6.3 ms
#2
CREATE INDEX "pdata_spatial_index_x" ON "pdata"("gx","gy");
CREATE INDEX "pdata_spatial_index_y" ON "pdata"("gy","gx");
file size 27,360 KB, query time ~3.3 ms
#3
CREATE VIRTUAL TABLE "pdata_Shape_Index" USING rtree("IndexedObjectId"
INTEGER,"MinGX" DOUBLE,"MaxGX" DOUBLE,"MinGY" DOUBLE,"MaxGY" DOUBLE);
file size 32,064 KB, query time ~1.3 ms
Now that is something to balance ... I'll probably go with the rtree here
although it may look a bit like a misuse. :-)
Wolfgang
