[Qgis-user] columns in spatialite

[Andrea Peri]

The better ML where ask for any difference between the spatialite and
the geopackega is the spatialite ML,
where there are user skilled on spatialite use.rather than the QGIS ML
where there are few user skilled in the spatialite use.

So you could ask to them.

Regards,

A.


2015-11-12 17:49 GMT+01:00 Andrea Peri <aperi2007 at gmail.com>:
> 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 àèìòù
> -----------------



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