[Qgis-user] columns in spatialite

Thu Nov 12 09:22:25 PST 2015

Le jeudi 12 novembre 2015 18:02:59, Alex M a écrit :
> The spatialite db contains spatial reference tables of projections.
> Thats where those MB come from.

I confirm. Spatialite by defaults populates the spatial_ref_sys table with all 
the EPSG catalog (but you can actually remove all the ones you don't use and 
do a VACUUM to shrink the file. And through the InitSpatialMetaData() SQL 
function you can also decide to have an empty table or just with EPSG:4326), 
whereas in GeoPackage you only have 3 mantatory SRS (undefined geographic, 
undefined projected and EPSG:4326) + the other ones you actually used. 
Spatialite has also a few extra metadata tables, but their inpact on the file 
size should be neglectable.

> As Andrea pointed out the full answer of
> what else is in those MB would be on the Spatialite mailing list.
> 
> -Alex
> 
> On 11/12/2015 08:49 AM, Andrea Peri wrote:
> > You need study better the spatialite formats.
> > 
> > :)
> > 
> > Also the shapefile format and also the geopackage format.
> > So you will be able to understand why spatialite is 4mb and other not.
> > 
> > :))
> > 
> > A.
> > 
> > 2015-11-12 17:34 GMT+01:00 Bernd Vogelgesang <bernd.vogelgesang at gmx.de>:
> >> Am 12.11.2015, 16:17 Uhr, schrieb Paulo van Breugel
> >> <p.vanbreugel at gmail.com>:
> >> 
> >> 
> >> 
> >> On Thu, Nov 12, 2015 at 3:48 PM, Neumann, Andreas <a.neumann at carto.net>
> >> 
> >> wrote:
> >>> I believe the main difference between SpatiaLite and Geopackage is that
> >>> a SpatiaLite database contains a lot of query functionality and
> >>> additional data (e.g. a big list of CRS) - while Geopackage does not
> >> 
> >> That would be a very big list of CRS, or does the query functionality
> >> take that much space? I often used spatialite, but given that I
> >> normally use it to store many layers, I had actually never noticed the
> >> large initial size. This is imho indeed a clear disadvantage when used
> >> for data sharing.
> >> 
> >>> When you checked filesizes for shp: did you only look at the shp, or
> >>> also include dbf, shx, prj, etc.? Otherwise you comparing a complete
> >>> dataset with attributes and metadata against just geometry.
> >> 
> >> All files, not only the *.shp file. I just created a simple vector layer
> >> in QGIS and saved it as shapefile, geopackage and spatialite file.
> >> 
> >> 
> >> Seems I'm a bit confused by the differences between spatialite and
> >> sqlite.
> >> 
> >> A spatialite db is always 4 MB + x which makes a huge difference for
> >> small layers, but I guess the differences become closer when having
> >> "normal" sized layers.
> >> 
> >> What is irritating me is, when I use a small layer and perform some
> >> processing function and then save it as .sqlite to a file, the result is
> >> only a few kb and not 4 MB.
> >> 
> >> So, having sqlite as default temporary output in processing would not
> >> make any difference to ESRI shape sizewise!
> >> 
> >> Example: a shapefile point layer with quite some attributes and 999
> >> features has 4.4 MB
> >> saving this as spatialiate: 4.6 MB
> >> saving as sqlite: 86 kB !
> >> 
> >> The sqlite file is 5 times smaller than the ESRI shape file while the
> >> spatialite file is about the same as the shape.
> >> 
> >> Here on 2.8.3 at the office I have no geopackage option to test with.
> >> 
> >> So, whats the difference between sqlite and spatialite in detail?
> >> 
> >> Cheers
> >> Bernd
> >> 
> >>> Andreas
> >>> 
> >>> On 2015-11-12 15:36, Paulo van Breugel wrote:
> >>> 
> >>> 
> >>> 
> >>> On Thu, Nov 12, 2015 at 1:30 PM, Matthias Kuhn <matthias at opengis.ch>
> >>> 
> >>> wrote:
> >>>> The main issues with spatialite are IMO: It's based on sqlite so
> >>>> deleting columns and renaming columns is not supported by design. We
> >>>> could offer some hacks to bypass this (annoying restriction) from the
> >>>> UI - there is a risk of side effects though. Another property of it
> >>>> is, that it's already 4-5MB big, even when empty. I consider this a
> >>>> major limiting factor as well. Other issues which we were not yet
> >>>> able to solve are its management of the information scheme which keep
> >>>> duplicate entries of tables and columns which need to be properly
> >>>> updated which we apparently do not manage (yet).
> >>>> 
> >>>> Geopackage is also based on sqlite, so the column delete/rename
> >>>> restrictions apply as well (with the same workaround possibilities). I
> >>>> haven't checked the file size, but if that's smaller, that would be
> >>>> quite nice (does somebody know?).
> >>> 
> >>> Just checked saving a shapefile of 941 bytes as Spatialite and
> >>> Geopackage file. The first is indeed 4.4MB. The Geopackage is 12.3kB,
> >>> i.e., larger then the shapefile, but the increase is small compared to
> >>> the spatialite file. I am not familiar with the differences, but this
> >>> makes the Geopackage a better candidate imho.
> >>> 
> >>>> All the best
> >>>> Matthias
> >>> 
> >>> _______________________________________________
> >>> Qgis-user mailing list
> >>> Qgis-user at lists.osgeo.org
> >>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> >>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
> >> 
> >> --
> >> Erstellt mit Operas E-Mail-Modul: http://www.opera.com/mail/
> >> 
> >> _______________________________________________
> >> Qgis-user mailing list
> >> Qgis-user at lists.osgeo.org
> >> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> >> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
> 
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com