[Qgis-user] columns in spatialite

[Alex M]

SQLite stores the geometries differently than Spatialite.
http://www.gdal.org/drv_sqlite.html
FORMAT=WKB/WKT/SPATIALITE

Biggest side effect I can think of is that you can't use the spatialite
query mechanisms on WKB/WKT without converting. Which includes spatial
indexes (not sure anything in QGIS utilizes these currently). I'm also
not sure that SQLite files via OGR are even editable (never tried). If I
recall QGIS has a spatialite driver built in and does not use the OGR
one in order to access more of the features.

I'm sure someone else will know more.

-Alex

On 11/12/2015 10:17 AM, Bernd Vogelgesang wrote:
> Am 12.11.2015, 17:49 Uhr, schrieb 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.
>>
> 
> Well, this time I didn't refer to the difference between GeoPackage and
> SpatiaLite, but between what is called "spatialite" and "sqlite" in the
> Save as .. dialog in QGIS.
> 
> The sqlite files are really small. For normal work where people do not
> need to set up views or whatever db internal stuff,  but just want to
> get intermediate results while working with data, and have long column
> names and unrestricted field lengths, those sqlite files seem to be so
> much better usable than standard ESRI shapes.
> 
> So again, what would be the caveats using this small sqlite stuff as
> default? (Besides the column resorting/renaming stuff for which
> workarounds/fixes see to be available)?
> 
> Cheers
> Bernd
> 
> 
> 
>> 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
>>>
>>>
>>>
>>>
>>>
> 
>