[Qgis-user] columns in spatialite

[Matthias Kuhn]

On 11/12/2015 07:25 PM, Alex M wrote:
> 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
An index will make a difference when you make a select with a bounding
box like it's done with every repaint.

> 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
>>>>
>>>>
>>>>
>>>>
>>>>
>>
> _______________________________________________
> 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

-- 
Matthias Kuhn
OPENGIS.ch - https://www.opengis.ch
Spatial • (Q)GIS • PostGIS • Open Source