[Qgis-developer] save as spatialite very resource consuming
a.furieri at lqt.it
a.furieri at lqt.it
Thu Jan 30 09:29:53 PST 2014
Just few technical considerations (may be useful):
a) any SpatiaLite DB (even one completely empty) is usually
expected to consume about 4 MB.
this is because the complete EPSG dataset (geodesic parameters)
will be fully populated during the initial creation steps, and
there are about 4.000+ SRID definitions to be stored in the
Il 30/gen/2014 17:24 "kimaidou" <kimaidou at gmail.com> ha scritto:
> Could you try to use this commands in QGIS python console ?
> I have seen that the parameter 1 in the function initspatialmetadata
> is very important to have a very fast DB creation.
> from pyspatialite import dbapi2 as db
> sql = "select initspatialmetadata(1)"
> c.execute(sql) ; print [row for row in c]
b) surely yes: SQLite/SpatiaLite is a TRANSACTIONAL DBMS;
attempting to perform several thousands INSERTS not
opportunely contained within some BEGIN TRANSACTION statement
will certainly cause a painful slowness.
because in this case the SQL engine will then wait for every
single row to be physically written to the hard disk before
passing to process the next row ... an obvious performance
starting from the more recent versions of spatialite
(> 4.0) SELECT InitSpatialMetadata(1) will automatically
begin a transaction (1 stands for "with transaction")
c) may be I'm wrong about this, but from my personal tests
it looks like as if a new DB will unconditionally be
created for each SHP to be exported.
any attempt to select an already existing DB will simply
cause the whole DB to be overwritten.
this seems to be a rather extravagant implementation,
because after all the main advantage in using SpatiaLite
is in that you can easily store many hundredths vector
layers into the same DB file.
but I'm unable to see any button like "add this layer
to an already existing DB"
d) I was able to successfully save a POINT and a LINESTRING
SHP, but while attempting to save a MULTIPOLYGON SHP
I got an impressive list of severe errors complaining
about mismatching Geometries.
I strongly suspect that the layer type has been incorrectly
defined in this more complex case.
e) last but not least.
I read in the "save as" dialog this definition:
"SpatiaLite [OGR] [*.sqlite *.SQLITE"]
so I presume that this export operation isn't directly
handled by the spatialite's own data-provider, but is
indirectly handled by OGR.
anyway, AFAIK OGR nicely supports several specific
arguments allowing to finely control how to handle
TRANSACTIONs, allowing to create a new layer into
an already existing DB, and even allowing to nicely
control mixed-type Geoms (i.e. POLYGON/MULTIPOLYGON
from the same layer).
so I imagine that some kind of improvement should be
surely possible by carefully checking the arguments
passed to OGR (the actual implementation seems to be
rather simplistic and not fully adequate).
More information about the Qgis-developer