[QGIS-Developer] Regular vs spatialite sqlite database

[enrico chiaradia]

Dear List,

I’m developing a python qgis based application the creates several new 
empty layers that will be filled by the user using already existing qgis 
editing tools and some tables of parameters that I will provide through 
the plugin. I’m dealing with the choose of the best file format to store 
this information. The ideal file format should be:

1) completely supported by qgis (I/O, editing and simple db function as 
joining)

2) store the data in a single file on the hard disk (server connection 
feature is not required at the moment)

3) manage both geometry and simple tables of data (i.e. with not 
geometry fields)

4) fast I/O

5) the generated file must be of smallest size as the user will be able 
to deliver it in the faster manner or creates multiple copies of it.

My first implementation used ESRI shapefile format that obviously didn’t 
respect requirement n. 2 but generated files are very small (5) and 
creation process is very fast (4). On the contrary, the creation of 
simple DBF file is not so straightforward in pyqgis (I used ogr) and DBF 
is very limited itself.

My second implementation uses Spatialite DB but I found it surprisingly 
slower than shapefile and the file created is very big (more than 5 MB) 
also for empty tables. Requirements number 2 and 3 are successfully 
satisfied.

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.

Currently, I'm looking to a definitely solution to the problem. My last 
idea is to store all data in a regular sqlite file (not spatialite) but 
I didn’t find any example about it.

Regular sqlite DB seems to be fully supported by QGIS and its file is 
lighter than Spatialite. Before starting the implementation of this 
solution. I’d like to ask you which disadvantages I might encounter and 
if someone has experiences with the use of regular sqlite DB for curstom 
qgis application.

Thank you for your time,

enrico

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20170724/d4572f00/attachment-0001.html>