rouault
commented
6 years ago @aaime
I tried to reproduce your test case by generating a SQLite database that should be representative of a GeoPackage database with many tiles, except that they payload is a small one with the following Python script:
import sqlite3
conn = sqlite3.connect("huge.db")
conn.execute("PRAGMA journal_mode = off")
conn.execute("CREATE TABLE test(id INTEGER PRIMARY KEY, blob, zoom_level INTEGER NOT NULL, tile_row INTEGER NOT NULL, tile_column INTEGER NOT NULL, UNIQUE(zoom_level, tile_row, tile_column))")
cursor = conn.cursor()
id = 0
for level in range(0,14):
print(level)
for row in range(0, 2**level):
for col in range(0, 2**level):
id += 1
cursor.execute("INSERT INTO test VALUES (?,'foo',?,?,?)", (id,level,row,col))
conn.commit()
conn.close()Then I timed echo "select * from test where zoom_level = 13 and tile_row between 1000 and 1002 and tile_column between 1000 and 1002;" | sqlite3 huge.db. It runs in a few tens of milliseconds.
The size of the file is 3.7 GB.
If I drop the UNIQUE constraint in the generating script and re-runs this, the request runs in 6.8 seconds. And the database size is 1.9 GB. So it shows that both a index is created due to the UNIQUE constraint and that it is actually used.
So I don't have an explanation for the behaviour you are seeing. I guess you were experimenting with Geoserver. Do you know how it reads sqlite database ? Deos it use a native libsqlite3 underneath or is this a Java implementation that would perhaps lack some features of the native library ?
aaime
jyutzler
gramnation
This ticket is in response to an email exchange, excerpted below.
At a minimum, we should try to get some clarity on the observed behavior and if necessary, publish an article for geopackage.org on how to index a tiles table properly. While I don't believe this is the case at this time, it is possible that this report will lead to a new GeoPackage extension.