[Qgis-user] columns in spatialite

[Bernd Vogelgesang]

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/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20151112/118962bc/attachment.html>