On Mon, 24 Jul 2017 11:32:17 +0200, enrico chiaradia wrote:
My second implementation uses Spatialite DB but I found it
surprisingly slower than shapefile ...
In my last implementation, I used a mixed approach, creating both
shapefiles to store geometries and regular sqlite db to store simple
tables. That is fast and space saving but it generates too much
files.
Hi Enrico,
SpatiaLite simply is an extension to SQLite, so both them will
have exactly the same identical speed when handling regular
(non Spatial) Tables.
Things are slightly different when handling Spatial Tables:
1. INSERT/UPDATE: SpatiaLite thoroughly checks any Geometry
before physically modifying the database; only geometries
safely matching the declared Type, SRID and Dimensions are
then accepted.
This leads to robustly consistent and well affordable
databases, but obviously requires some (small) extra
time.
2. if the Spatial Table is supported by one (or more)
Spatial Index any INSERT, UPDATE or DELETE will
always take care of properly synchronizing the Spatial
Index (a costly operation) so this will surely add some
further delay.
3. read operations (SELECT): SpatiaLite is usually faster
then Shapefiles, most notably when several layers are
involved. Accessing a monolithic SpatiaLite DB
containing tenths (or hundredths) layers is expected
to be a far more efficient process than accessing many
sparse Shapefiles.
4. and if your layers (Spatial Table) are adequately
supported by some Spatial Index you'll easily
discover that SpatiaLite can be surprisingly fast.
conclusion: it's a trade-off (as always).
we usually bargain some reasonable slowness when
writing so to get maximum speed when reading.
usually write operations happen rather infrequently
whilst read operations are very frequents, so the
final outcome is expected to be widely positive.
Final remark: you say us nothing about the fine
implementation details of your own Phyton code.
Both SQLite and SpatiaLite have an inherently
transactional nature: this practically means that
if you don't properly handle transactions (by
declaring BEGIN and COMMIT statements where
appropriate) all write operations (INSERT,
UPDATE and/or DELETE) can easily become slow
(_VERY_ slow, may well be deadly slow).
I'm not sure if this could be your specific
case, but carefully checking if the code
correctly handles transactions always is
a good programming practice.
bye Sandro
_______________________________________________
QGIS-Developer mailing list
[email protected]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
