[sqlite] storing coordinate pairs efficiently?

2016-04-20 Thread Wolfgang Enzinger
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

[sqlite] storing coordinate pairs efficiently?

2016-04-20 Thread Simon Slavin
On 20 Apr 2016, at 2:16pm, Wolfgang Enzinger wrote: > CREATE INDEX "pdata_spatial_index" ON "pdata"("gx","gy"); > > [snip] > > However, EQP also shows that it is only used with the value of gx, not gy: It would be used if you searched for one x value and a range of y values. If you do have

[sqlite] storing coordinate pairs efficiently?

2016-04-20 Thread Wolfgang Enzinger
Simon, Gunter, thanks for your input. I also noticed that an index on (gx,gy) would help on covering index searches, however as you both noticed as well there are more fields in this table. But wait - probably for the first step (which points are located in the region in question anyway?) it might

[sqlite] storing coordinate pairs efficiently?

2016-04-20 Thread Hick Gunter
An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] storing coordinate pairs efficiently? Dear group, since a couple of years now, I'm using SQLite for storing graphic objects like polygons and lines, with great success. Using the rtree extension makes spatial queries very efficien

[sqlite] storing coordinate pairs efficiently?

2016-04-20 Thread Wolfgang Enzinger
Dear group, since a couple of years now, I'm using SQLite for storing graphic objects like polygons and lines, with great success. Using the rtree extension makes spatial queries very efficient. In my lastest project, however, these objects are just single points (coordinate pairs). As I understa