[Qgis-user] columns in spatialite

[Andrea Peri]

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



-- 
-----------------
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-----------------