[Qgis-developer] save as spatialite very resource consuming

[a.furieri at lqt.it]

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
    "spatial_ref_sys" table.

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
> conn=db.connect('/tmp/test.db')
> c=conn.cursor()
> 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
    killer.
    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).

bye Sandro