Re: [Qgis-user] columns in spatialite

[Luigi Pirelli]

For different reasons I'm not fan or either geopackage and spatialite...
Btw, you can reduce the size of the spatialite if you create it only with
CR's of your data (and much Custer to create). A spatiallite with all CRS
is 4mb... With a single crs is some k but with all spatial operators
indside.


On Thursday, 12 November 2015, Paulo van Breugel 
wrote:

>
>
> On Thu, Nov 12, 2015 at 1:30 PM, Matthias Kuhn  > 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
>>
>
>

-- 
Luigi Pirelli

**
* Boundless QGIS Support/Development: lpirelli AT boundlessgeo DOT com
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Mastering QGIS:
https://www.packtpub.com/application-development/mastering-qgis
**
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Micha Silver]

  
  


On 11/13/2015 01:51 AM, Matthias Kuhn
  wrote:


  

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.


  


Good to know, thanks!


-- 
  
Micha Silver
Arava Drainage Authority
+972-523-665918
  

  

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Micha Silver]

  
  
This thread drifted off into some very interesting discussions of
data exchange formats.

But going back to the original problem - new columns, added in SL do
not appear in QGIS:
Sandro Furieri posted on the spatialite-users forum a pretty full
explanation of what is happening, and a suggested solution:

https://groups.google.com/forum/#!topic/spatialite-users/uGDZr1TCTn8

The above link has been added to the hub.qgis.org issue #8923. It
involves only two SQL statements that need be run whenever a
Spatialite database on an exisiting connection is changed (i.e.
"refresh connection").  Perhaps someone on the qgis-dev list can
report the proposed solution to developers?

Thanks,
Micha

On 11/10/2015 04:32 PM, Matthias Kuhn
  wrote:


  
  Hi,
  
  You are probably hitting issue 8923. This is more of a spatialite
  issue than a QGIS issue. But there is a workaround for it, see
  here:
  https://hub.qgis.org/issues/8923#note-5
  
  Best regards
  Matthias
  
  On 11/10/2015 03:26 PM, Micha Silver
wrote:
  
  


I have found that QGIS cannot "see" any spatialite column that
comes after the "geometry" column. When I alter a table, adding
a column in a Spatialite, the new column which is after the
geometry column in the database table just does not appear in
QGIS. If I recreate the table, reordering the columns so that
the geometry is last, all is good again.

Is this a bug? or am I missing something?

It's quite annoying considering that sqlite does not have any
simple way to shuffle the columns around, other than dropping
and recreating the table. And if I have foreign constraints
defined, it becomes a whole row of dominoes that come down...

Thanks,
Micha



___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
  
  
  -- 
Help to improve QGIS rendering possibilities before Nov 30, 2015
http://www.opengis.ch/2015/11/02/qgis-crowdfunding-2-5d-rendering
  
  
  This mail was received via Mail-SeCure System.
  
  
  
  ___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
This mail was received via Mail-SeCure System.



-- 
  
Micha Silver
Arava Drainage Authority
+972-523-665918
  

  

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [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 :
>>
>>> 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@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

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Micha Silver]

  
  
On 12/11/2015 14:10, Paulo van Breugel
  wrote:


  

  On Thu, Nov 12, 2015 at 1:05 PM,
Bernd Vogelgesang 
wrote:
Am
  12.11.2015, 08:01 Uhr, schrieb Paolo Cavallini :
  
  
Il 12/11/2015 00:31, Bernd Vogelgesang ha scritto:


  
  
  
  I repeatedly ask for this, and do not receive much
  positive echo. What are the reasons for people to prefer
  ESRI shapes over sqlite files? Are they not aware of the
  benefits or do I just miss sth completely obvious that is
  an argument against sqlite?
  

  

  


Well there's still the problem of exchanging data with users of the
other proprietary GIS software. Spatialite is still, unfortunately,
not that popular.



  

  

  The only reason for me not to use sqlite in QGIS is, that
  it is not well integrated into the workflows (and ftools,
  which I do not use at all since there is Processing), but
  that seems not to be the fault of sqlite but of user and
  developer habits (?)
  
  At least an option in Processing settings for choosing the
  default file type for temporary layers would meet my needs
  to 80% I guess.



+1 No idea either how difficult that would be to
  implement, but it would be a great improvement

 


  
  Thanks for your attention
  Bernd
  
  
  
  -- 
  Bernd Vogelgesang
  Siedlerstraße 2
  91083 Baiersdorf/Igelsdorf
  Tel: 09133-825374
  ___
  Qgis-user mailing list
  Qgis-user@lists.osgeo.org
  List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
  Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
  
  

  
  
  This mail was received via Mail-SeCure System.
  
  
  
  ___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
This mail was received via Mail-SeCure System.





-- 
  
Micha Silver
Arava Drainage Authority
cell: +972-523-665918
  

  

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Paulo van Breugel]

On Thu, Nov 12, 2015 at 1:05 PM, Bernd Vogelgesang  wrote:

> Am 12.11.2015, 08:01 Uhr, schrieb Paolo Cavallini :
>
> Il 12/11/2015 00:31, Bernd Vogelgesang ha scritto:
>>
>> So, to answer to Paolo's question about what is missing in DBManager:
>>> Actually nothing in my case, cause I really do not want to use it at
>>> all. I just would like to be able to act with spatialite layers as I am
>>> able to do with ESRI shapefile layers.
>>>
>>
>> Geopackage might be more in line with what you are looking for (still a
>> lengthy way to go, anyway).
>> All the best.
>>
>
> Hi Paolo,
>
> as far as I understand, Geopackage is going to be more or less sth like a
> File-Geodatabase, so a container for all kinds of data which can be easily
> shipped.
> The "lengthy" way to Geopackage seems to be already long, and in the
> meanwhile, ESRI shape will stay the unquestioned standard?
>
> I really have no clue of the interiors of the technology, but would it be
> really this hard to streamline QGIS functions in a way, that sqlite files
> are the default output of all functions, so also as temporary files from
> Processing?
>
> I repeatedly ask for this, and do not receive much positive echo. What are
> the reasons for people to prefer ESRI shapes over sqlite files? Are they
> not aware of the benefits or do I just miss sth completely obvious that is
> an argument against sqlite?
>
> The only reason for me not to use sqlite in QGIS is, that it is not well
> integrated into the workflows (and ftools, which I do not use at all since
> there is Processing), but that seems not to be the fault of sqlite but of
> user and developer habits (?)
>
> At least an option in Processing settings for choosing the default file
> type for temporary layers would meet my needs to 80% I guess.
>

+1 No idea either how difficult that would be to implement, but it would be
a great improvement


>
> Thanks for your attention
> Bernd
>
>
>
> --
> Bernd Vogelgesang
> Siedlerstraße 2
> 91083 Baiersdorf/Igelsdorf
> Tel: 09133-825374
> ___
> Qgis-user mailing list
> Qgis-user@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Bernd Vogelgesang]

Am 12.11.2015, 08:01 Uhr, schrieb Paolo Cavallini :


Il 12/11/2015 00:31, Bernd Vogelgesang ha scritto:


So, to answer to Paolo's question about what is missing in DBManager:
Actually nothing in my case, cause I really do not want to use it at
all. I just would like to be able to act with spatialite layers as I am
able to do with ESRI shapefile layers.


Geopackage might be more in line with what you are looking for (still a
lengthy way to go, anyway).
All the best.


Hi Paolo,

as far as I understand, Geopackage is going to be more or less sth like a  
File-Geodatabase, so a container for all kinds of data which can be easily  
shipped.
The "lengthy" way to Geopackage seems to be already long, and in the  
meanwhile, ESRI shape will stay the unquestioned standard?


I really have no clue of the interiors of the technology, but would it be  
really this hard to streamline QGIS functions in a way, that sqlite files  
are the default output of all functions, so also as temporary files from  
Processing?


I repeatedly ask for this, and do not receive much positive echo. What are  
the reasons for people to prefer ESRI shapes over sqlite files? Are they  
not aware of the benefits or do I just miss sth completely obvious that is  
an argument against sqlite?


The only reason for me not to use sqlite in QGIS is, that it is not well  
integrated into the workflows (and ftools, which I do not use at all since  
there is Processing), but that seems not to be the fault of sqlite but of  
user and developer habits (?)


At least an option in Processing settings for choosing the default file  
type for temporary layers would meet my needs to 80% I guess.


Thanks for your attention
Bernd



--
Bernd Vogelgesang
Siedlerstraße 2
91083 Baiersdorf/Igelsdorf
Tel: 09133-825374
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Matthias Kuhn]

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?).

All the best
Matthias
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Neumann, Andreas]

 

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 

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. 

Andreas 

On 2015-11-12 15:36, Paulo van Breugel wrote: 

> On Thu, Nov 12, 2015 at 1:30 PM, Matthias Kuhn  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@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

  ___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Paulo van Breugel]

On Thu, Nov 12, 2015 at 1:30 PM, Matthias Kuhn  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@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Neumann, Andreas]

 

Hi, 

I would prefer if we could concentrate on Geopackage. Geopackage is now
an OGC format and should be (at least in theory) better supported in
other (proprietary) GIS than SpatiaLite. 

Andreas 

On 2015-11-12 13:30, Matthias Kuhn 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?).
> 
> All the best
> Matthias
> ___
> Qgis-user mailing list
> Qgis-user@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

  ___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Paulo van Breugel]

On Thu, Nov 12, 2015 at 3:48 PM, Neumann, Andreas 
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.

> Andreas
>
> On 2015-11-12 15:36, Paulo van Breugel wrote:
>
>
>
> On Thu, Nov 12, 2015 at 1:30 PM, Matthias Kuhn 
> 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@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>
>
>
>
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [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 :
> 
>> 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@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Randal Hale]

It works - mostly. I've had a few problems reading spatialite in ArcGIS 
10.2.2 as of late - which may be to the fact I'm still on 10.2.2. My 
last attempt at moving a database from PostGIS to Spatialite and into 
ArcGIS ended up with ArcGIS crashing a lot - BUT - it could read the data.


Randy


On 11/12/2015 12:01 PM, Alex M wrote:

Sure you can clarify there.

I have tested spatialite in ArcGIS 10.2+

http://blog.geomusings.com/2013/08/07/spatialite-and-arcgis-10-dot-2/
http://www.northrivergeographic.com/archives/spatialite-arcgis-qgis

Enjoy,
Alex

On 11/12/2015 08:57 AM, Andrea Peri wrote:

As the better ML for ask for any differnce between the spatialite and
geopackage is the ML Spatialite.
Also for ask if the Arcgis is really capable to read a true spatialite
or instead is reading someone surely is not the QGIS ML,
perhaps the Spatialite ML could know something, but perhaps no.

A.


2015-11-12 17:54 GMT+01:00 Alex M :

On 11/12/2015 04:12 AM, Micha Silver wrote:

On 12/11/2015 14:10, Paulo van Breugel wrote:


On Thu, Nov 12, 2015 at 1:05 PM, Bernd Vogelgesang
<bernd.vogelges...@gmx.de> wrote:

 Am 12.11.2015, 08:01 Uhr, schrieb Paolo Cavallini
 <cavall...@faunalia.it>:

 Il 12/11/2015 00:31, Bernd Vogelgesang ha scritto:




 I repeatedly ask for this, and do not receive much positive echo. What are
 the reasons for people to prefer ESRI shapes over sqlite files? Are they
 not aware of the benefits or do I just miss sth completely obvious that is
 an argument against sqlite?


Well there's still the problem of exchanging data with users of the other
proprietary GIS software. Spatialite is still, unfortunately, not that popular.

FYI, ArcGIS as of 10.2 can read Spatialite files. Even more recent
ArcGIS can read GeoPackage.

-Alex
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user




___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user


--
-
Randal Hale
North River Geographic Systems, Inc
http://www.northrivergeographic.com
423.653.3611 rjh...@northrivergeographic.com
twitter:rjhale http://about.me/rjhale
http://www.northrivergeographic.com/introduction-to-quantum-gis
Southeast OSGEO: http://wiki.osgeo.org/wiki/Southeast_US

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[aperi2007]

I understand that the real spatialite format is not really knowed.

The test you report is a read only test on the spatialite-gui.
But the spatialite-gui is a all-version readable software.
This mean that it is capable to read and understand EVERY VERSION of the 
spatialite format.

Also the oldest one.

For the write test the report has used the QGIS 1.9.0 that is an old 
version

using the old driver spatialite 2.x

This mean that the arcgis is capable to read/write only the spatialite 
versione 2.x ? version.


Whoa ! This mean it is a very old format of spatialite.

After the format spatialite 2.x
there was the
spatialite 3.1 and the
spatialite 4.0 format.
And at now the spatialite 4.1, 4.2 and 4.3

But at binary level the major difference isbetween

the spatialite 2.x, 3.x and 4.0

You should ask to the ML spatialite to have more details.

A.



Il 12/11/2015 18:01, Alex M ha scritto:

Sure you can clarify there.

I have tested spatialite in ArcGIS 10.2+

http://blog.geomusings.com/2013/08/07/spatialite-and-arcgis-10-dot-2/
http://www.northrivergeographic.com/archives/spatialite-arcgis-qgis

Enjoy,
Alex

On 11/12/2015 08:57 AM, Andrea Peri wrote:

As the better ML for ask for any differnce between the spatialite and
geopackage is the ML Spatialite.
Also for ask if the Arcgis is really capable to read a true spatialite
or instead is reading someone surely is not the QGIS ML,
perhaps the Spatialite ML could know something, but perhaps no.

A.


2015-11-12 17:54 GMT+01:00 Alex M :

On 11/12/2015 04:12 AM, Micha Silver wrote:

On 12/11/2015 14:10, Paulo van Breugel wrote:


On Thu, Nov 12, 2015 at 1:05 PM, Bernd Vogelgesang
<bernd.vogelges...@gmx.de> wrote:

 Am 12.11.2015, 08:01 Uhr, schrieb Paolo Cavallini
 <cavall...@faunalia.it>:

 Il 12/11/2015 00:31, Bernd Vogelgesang ha scritto:




 I repeatedly ask for this, and do not receive much positive echo. What are
 the reasons for people to prefer ESRI shapes over sqlite files? Are they
 not aware of the benefits or do I just miss sth completely obvious that is
 an argument against sqlite?


Well there's still the problem of exchanging data with users of the other
proprietary GIS software. Spatialite is still, unfortunately, not that popular.

FYI, ArcGIS as of 10.2 can read Spatialite files. Even more recent
ArcGIS can read GeoPackage.

-Alex
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user





___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Alex M]

Yes we should implement a cheat in QGIS (core) to handle this issue.
Other SQLite GUIs do this. Spatialite GUI and SQLite Manager (Firefox
plugin).

The basic process:
1. Make a new table the way you actually want it.
2. SQL INSERT records from Old to New (Mapping old columns to new if
name changed).
3. Remove old table
4. Rename new table to old name
5. Vacuum to recover extra pages allocated in the shuffle.

Nothing else should be needed, since you use the same name, spatial
indexes and geometry_column records should stay the same unless the
Geometry column is renamed.

This method will apply to both GeoPackage (If editing is allowed),
Spatialite tables, and any non-spatial sqlite tables.

-Alex

On 11/12/2015 04:30 AM, Matthias Kuhn 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?).
> 
> All the best
> Matthias
> ___
> Qgis-user mailing list
> Qgis-user@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
> 

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Alex M]

Sure you can clarify there.

I have tested spatialite in ArcGIS 10.2+

http://blog.geomusings.com/2013/08/07/spatialite-and-arcgis-10-dot-2/
http://www.northrivergeographic.com/archives/spatialite-arcgis-qgis

Enjoy,
Alex

On 11/12/2015 08:57 AM, Andrea Peri wrote:
> As the better ML for ask for any differnce between the spatialite and
> geopackage is the ML Spatialite.
> Also for ask if the Arcgis is really capable to read a true spatialite
> or instead is reading someone surely is not the QGIS ML,
> perhaps the Spatialite ML could know something, but perhaps no.
> 
> A.
> 
> 
> 2015-11-12 17:54 GMT+01:00 Alex M :
>> On 11/12/2015 04:12 AM, Micha Silver wrote:
>>> On 12/11/2015 14:10, Paulo van Breugel wrote:


 On Thu, Nov 12, 2015 at 1:05 PM, Bernd Vogelgesang
 <bernd.vogelges...@gmx.de> wrote:

 Am 12.11.2015, 08:01 Uhr, schrieb Paolo Cavallini
 <cavall...@faunalia.it>:

 Il 12/11/2015 00:31, Bernd Vogelgesang ha scritto:




 I repeatedly ask for this, and do not receive much positive echo. What 
 are
 the reasons for people to prefer ESRI shapes over sqlite files? Are 
 they
 not aware of the benefits or do I just miss sth completely obvious 
 that is
 an argument against sqlite?

>>>
>>> Well there's still the problem of exchanging data with users of the other
>>> proprietary GIS software. Spatialite is still, unfortunately, not that 
>>> popular.
>>
>> FYI, ArcGIS as of 10.2 can read Spatialite files. Even more recent
>> ArcGIS can read GeoPackage.
>>
>> -Alex
>> ___
>> Qgis-user mailing list
>> Qgis-user@lists.osgeo.org
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
> 
> 
> 

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Alex M]

The spatialite db contains spatial reference tables of projections.
Thats where those MB come from. As Andrea pointed out the full answer of
what else is in those MB would be on the Spatialite mailing list.

-Alex

On 11/12/2015 08:49 AM, Andrea Peri wrote:
> 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 :
>> Am 12.11.2015, 16:17 Uhr, schrieb Paulo van Breugel
>> :
>>
>>
>>
>> On Thu, Nov 12, 2015 at 3:48 PM, Neumann, Andreas 
>> 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 
>>> 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@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@lists.osgeo.org
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
> 
> 
> 

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Even Rouault]

Le jeudi 12 novembre 2015 18:02:59, Alex M a écrit :
> The spatialite db contains spatial reference tables of projections.
> Thats where those MB come from.

I confirm. Spatialite by defaults populates the spatial_ref_sys table with all 
the EPSG catalog (but you can actually remove all the ones you don't use and 
do a VACUUM to shrink the file. And through the InitSpatialMetaData() SQL 
function you can also decide to have an empty table or just with EPSG:4326), 
whereas in GeoPackage you only have 3 mantatory SRS (undefined geographic, 
undefined projected and EPSG:4326) + the other ones you actually used. 
Spatialite has also a few extra metadata tables, but their inpact on the file 
size should be neglectable.

> As Andrea pointed out the full answer of
> what else is in those MB would be on the Spatialite mailing list.
> 
> -Alex
> 
> On 11/12/2015 08:49 AM, Andrea Peri wrote:
> > 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 :
> >> Am 12.11.2015, 16:17 Uhr, schrieb Paulo van Breugel
> >> :
> >> 
> >> 
> >> 
> >> On Thu, Nov 12, 2015 at 3:48 PM, Neumann, Andreas 
> >> 
> >> 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 
> >>> 
> >>> 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
> >>> 
> >>> 

Re: [Qgis-user] columns in spatialite

[Bernd Vogelgesang]

Am 12.11.2015, 16:17 Uhr, schrieb Paulo van Breugel  
:





On Thu, Nov 12, 2015 at 3:48 PM, Neumann, Andreas   
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   
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@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@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [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 :
> 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 :
>> Am 12.11.2015, 16:17 Uhr, schrieb Paulo van Breugel
>> :
>>
>>
>>
>> On Thu, Nov 12, 2015 at 3:48 PM, Neumann, Andreas 
>> 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 
>>> 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@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@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 àèìòù
-

Re: [Qgis-user] columns in spatialite

[Alex M]

On 11/12/2015 04:12 AM, Micha Silver wrote:
> On 12/11/2015 14:10, Paulo van Breugel wrote:
>>
>>
>> On Thu, Nov 12, 2015 at 1:05 PM, Bernd Vogelgesang 
>> <bernd.vogelges...@gmx.de> wrote:
>>
>> Am 12.11.2015, 08:01 Uhr, schrieb Paolo Cavallini
>> <cavall...@faunalia.it>:
>>
>> Il 12/11/2015 00:31, Bernd Vogelgesang ha scritto:
>>
>>
>>
>>
>> I repeatedly ask for this, and do not receive much positive echo. What 
>> are
>> the reasons for people to prefer ESRI shapes over sqlite files? Are they
>> not aware of the benefits or do I just miss sth completely obvious that 
>> is
>> an argument against sqlite?
>>
> 
> Well there's still the problem of exchanging data with users of the other 
> proprietary GIS software. Spatialite is still, unfortunately, not that 
> popular.

FYI, ArcGIS as of 10.2 can read Spatialite files. Even more recent
ArcGIS can read GeoPackage.

-Alex
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Alex M]

You are correct. In my mind the workflow is get GeoPackage -> up convert
to Spatialite -> do work -> Convert to GeoPackage to give to other people.

This is actually the same workflow that's been pushed in other software
too. Get exchange format, import to internal best database format, do
work, export to exchange format.

The export to exchange of whole projects/databases needs to be made
super simple (1-2 clicks) for it to become common.

-Alex

On 11/12/2015 06:48 AM, Neumann, Andreas 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 
> 
> 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. 
> 
> Andreas 
> 
> On 2015-11-12 15:36, Paulo van Breugel wrote: 
> 
>> On Thu, Nov 12, 2015 at 1:30 PM, Matthias Kuhn  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@lists.osgeo.org
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
> 
>   
> 
> 
> 
> ___
> Qgis-user mailing list
> Qgis-user@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
> 

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [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 :
> Am 12.11.2015, 16:17 Uhr, schrieb Paulo van Breugel
> :
>
>
>
> On Thu, Nov 12, 2015 at 3:48 PM, Neumann, Andreas 
> 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 
>> 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@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@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 àèìòù
-
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Even Rouault]

Le jeudi 12 novembre 2015 19:25:20, Alex M a écrit :
> SQLite stores the geometries differently than Spatialite.
> http://www.gdal.org/drv_sqlite.html
> FORMAivT=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.

The OGR spatialite driver has editing capabilities (when linked against 
libspatialite, otherwise read-only), as well as use of spatial indices. I'm 
not sure which capabilities the QGIS builtin spatialite driver would have in 
addition. I guess the existence of both is more due to historical reasons than 
technical ones.

> 
> 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 :
> >> 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@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Paolo Cavallini]

Il 12/11/2015 00:31, Bernd Vogelgesang ha scritto:

> So, to answer to Paolo's question about what is missing in DBManager:
> Actually nothing in my case, cause I really do not want to use it at
> all. I just would like to be able to act with spatialite layers as I am
> able to do with ESRI shapefile layers.

Geopackage might be more in line with what you are looking for (still a
lengthy way to go, anyway).
All the best.

-- 
Paolo Cavallini - www.faunalia.eu
QGIS & PostGIS courses: http://www.faunalia.eu/training.html
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Bernd Vogelgesang]

Am 11.11.2015, 21:55 Uhr, schrieb X Of :


Am 11.11.2015 um 07:59 schrieb Micha Silver:

Unfortunately I easily forget how sql
works, so doing "easy" stuff becomes very time consuming and  
frustrating.


I think this is a usual problem for us non-regular SQL-users (for any  
database-system).


To avoid figuring out complex or even simple queries (imho all  
geo-spatial queries are relatively difficult) I always keep a  
code-editor-window open, where I copy-paste to and from all successful  
queries (and also comment on how they work, even if I think it's silly.  
Next morning I'm grateful for it already).


New queries I usually draft in the code-editor first. This way I don't  
even have to remember the exact table-names or column names, I can just  
start typing and wait for the editor's autocomplete-suggestions.


I think this is quite a good way of working, because this way I can  
always and easily reproduces my work, if anything should go wrong with  
the databases/views...


Hope this helps avoiding some frustrations. To me, spatialite rocks even  
with all it's quirks here and there! Thanks to everyone contributing to  
spatialite and QGIS.

_



Of course this is a nice strategy when you are disciplined person and  
always remember to open the editor and then not forget where you stored  
your file ;)


Actually, my use-case for spatialite is much more simple. I normally do  
not need any special/spacial queries, I just want to replace ESRI shape  
file as the standard exchange format (do not have to zip 3 + x files  
before sending by email) and also as the standard work format (getting rid  
of computer-stone-age dbf limitations like column names and field lengths).


But so far, I can not really introduce it in my daily work-flows  
efficiently, cause of limitations simple as renaming or reordering  
columns. Even when you really carefully plan an attribute table set-up,  
you ALWAYS forget sth or put it in the wrong order. Having to dig out sql  
for this, I'm simply to lazy and most other users are definitely out of  
the game at this moment. (Of course it would be very rewarding to learn  
sql by heart, but for everyday quick-and-dirty tasks, this is just to much  
rocket science to be efficient)


And even when you start out correctly with all your fields and names and  
everything, you use Processing and BAMM, everything's fucked up again by  
ESRI shape files when you just want to have a temporary layer and do not  
wish to save each and every trial or iteration to a file. Of course I  
COULD do this, but then the half of the benefits of Processing are lost.  
So I stay with the be-hated ESRI format.


My dream would be, to be able to simply use the spatialite format as  
seamless as QGIS works now with ESRI shapes.
Later on, the extended sql-abilities would be nice goody for special  
cases, but I am more for the bread-and-butter tasks at the moment.


So, to answer to Paolo's question about what is missing in DBManager:  
Actually nothing in my case, cause I really do not want to use it at all.  
I just would like to be able to act with spatialite layers as I am able to  
do with ESRI shapefile layers.


That's why I asked in the beginning about a Table-Manager-like plugin to  
at least be able to rename/reorder columns (Strange enough that Table  
Manager is not even installed and activated by default, though ESRI shape  
files are the standard work format, and novices will not so easily find  
out how to edit them, while in ArcGIS this is just possible without any  
user interaction)


Regards
Bernd

__

Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user



--
Bernd Vogelgesang
Siedlerstraße 2
91083 Baiersdorf/Igelsdorf
Tel: 09133-825374
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[kimaidou]

Hi,

You could probably try the last "master" QGIS and processing, as René-Luc
(rldhont) added a couple of days ago the ability to save scripts and algs
result layers into a spatialite database. Combined with the tool "Refactor"
( visible in the Processing toolbox), and using your initial spatialite
table, this could do the trick without the need to create a new plugin for
this.

Regards
Michaël

2015-11-10 15:42 GMT+01:00 Bernd Vogelgesang :

> Hi Micha,
>
> yeah, working with spatialite dbs "seriously" is still quite uncomfortable.
> When I try, I mostly have to use all available means to get the work done:
> DBManager, QSpatialite, SpatialiteGUI. Unfortunately I easily forget how
> sql works, so doing "easy" stuff becomes very time consuming and
> frustrating.
>
> Do you think it would be technically possible to create a plugin like
> Table Manager plugin, that records the changes you want to do to the table
> and then creates the necessary sql commands and performs all that stuff in
> one go?
>
> Cause this would be sth I really would be interested donating money on!
>
> Cheers
> Bernd
>
> Am 10.11.2015, 15:26 Uhr, schrieb Micha Silver :
>
> I have found that QGIS cannot "see" any spatialite column that comes after
> the "geometry" column. When I alter a table, adding a column in a
> Spatialite, the new column which is after the geometry column in the
> database table just does not appear in QGIS. If I recreate the table,
> reordering the columns so that the geometry is last, all is good again.
>
> Is this a bug? or am I missing something?
>
> It's quite annoying considering that sqlite does not have any simple way
> to shuffle the columns around, other than dropping and recreating the
> table. And if I have foreign constraints defined, it becomes a whole row of
> dominoes that come down...
>
> Thanks,
> Micha
>
>
>
>
> --
> Bernd Vogelgesang
> Siedlerstraße 2
> 91083 Baiersdorf/Igelsdorf
> Tel: 09133-825374
>
> ___
> Qgis-user mailing list
> Qgis-user@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Bernd Vogelgesang]

Am 10.11.2015, 16:00 Uhr, schrieb kimaidou :


Hi,

You could probably try the last "master" QGIS and processing, as  
René-Luc (rldhont) added a couple of days ago the ability to save  
>scripts and algs result layers into a spatialite database. Combined  
with the tool "Refactor" ( visible in the Processing toolbox), >and  
using your initial spatialite table, this could do the trick without the  
need to create a new plugin for this.


Nice! Seems to work so far!
Now some people should be really really brave and make the sqlite entry  
the first in the dropdown menu when saving to file and push ESRI shapefile  
to the end ;)


Thanx for the hint
Bernd



Regards
Michaël

2015-11-10 15:42 GMT+01:00 Bernd Vogelgesang :

Hi Micha,

yeah, working with spatialite dbs "seriously" is still quite  
uncomfortable.
When I try, I mostly have to use all available means to get the work  
done: DBManager, QSpatialite, SpatialiteGUI. Unfortunately >>I easily  
forget how sql works, so doing "easy" stuff becomes very time consuming  
and frustrating.


Do you think it would be technically possible to create a plugin like  
Table Manager plugin, that records the changes you want to >>do to the  
table and then creates the necessary sql commands and performs all that  
stuff in one go?


Cause this would be sth I really would be interested donating money on!

Cheers
Bernd

Am 10.11.2015, 15:26 Uhr, schrieb Micha Silver :

I have found that QGIS cannot "see" any spatialite column that comes  
after the "geometry" column. When I alter a table, adding >>>a column  
in a Spatialite, the new column which is after the geometry column in  
the database table just does not appear in QGIS. >>>If I recreate the  
table, reordering the columns so that the geometry is last, all is  
good again.


Is this a bug? or am I missing something?

It's quite annoying considering that sqlite does not have any simple  
way to shuffle the columns around, other than dropping and  
>>>recreating the table. And if I have foreign constraints defined, it  
becomes a whole row of dominoes that come down...


Thanks,
Micha




--Bernd Vogelgesang
Siedlerstraße 2
91083 Baiersdorf/Igelsdorf
Tel: 09133-825374

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user






--
Bernd Vogelgesang
Siedlerstraße 2
91083 Baiersdorf/Igelsdorf
Tel: 09133-825374___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Paolo Cavallini]

Il 11/11/2015 00:16, Bernd Vogelgesang ha scritto:

> 2015-11-10 15:42 GMT+01:00 Bernd Vogelgesang
> >:
> 
> Hi Micha,
> 
> yeah, working with spatialite dbs "seriously" is still quite
> uncomfortable.
> When I try, I mostly have to use all available means to get the
> work done: DBManager, QSpatialite, SpatialiteGUI. Unfortunately
> I easily forget how sql works, so doing "easy" stuff becomes
> very time consuming and frustrating.

Agreed. I know it's an old question, but I'm still interested: what
exactly is missing from stock QGIS (including DB Manager) to fulfill
your needs? Have you opened feature reqs?
Thanks.
-- 
Paolo Cavallini - www.faunalia.eu
QGIS & PostGIS courses: http://www.faunalia.eu/training.html
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Micha Silver]

  
  
The need for improved spatialite plugins aside, I still think that
the QGIS provider for SL should pick up *all* attribute columns,
even those after the geometry column.


On 11/10/2015 04:42 PM, Bernd
  Vogelgesang wrote:


  
  
  
  Hi Micha,
  
  
  yeah, working with spatialite dbs "seriously" is still quite
uncomfortable.
  When I try, I mostly have to use all available means to get
the work done: DBManager, QSpatialite, SpatialiteGUI.
Unfortunately I easily forget how sql works, so doing "easy"
stuff becomes very time consuming and frustrating.
  
  
  Do you think it would be technically possible to create a
plugin like Table Manager plugin, that records the changes you
want to do to the table and then creates the necessary sql
commands and performs all that stuff in one go?
  
  
  Cause this would be sth I really would be interested donating
money on!
  
  
  Cheers
  Bernd
  
  
  Am 10.11.2015, 15:26 Uhr, schrieb Micha Silver
:
  
  
   I have found that QGIS cannot "see"
any spatialite column that comes after the "geometry" column.
When I alter a table, adding a column in a Spatialite, the new
column which is after the geometry column in the database table
just does not appear in QGIS. If I recreate the table,
reordering the columns so that the geometry is last, all is good
again.

Is this a bug? or am I missing something?

It's quite annoying considering that sqlite does not have any
simple way to shuffle the columns around, other than dropping
and recreating the table. And if I have foreign constraints
defined, it becomes a whole row of dominoes that come down...

Thanks,
Micha
  
  
  
  
  
-- 
Bernd Vogelgesang
  Siedlerstraße 2
  91083 Baiersdorf/Igelsdorf
  Tel: 09133-825374
  
  
  
  This mail was received via Mail-SeCure System.
  
  
  
  ___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
This mail was received via Mail-SeCure System.



-- 
  
Micha Silver
Arava Drainage Authority
+972-523-665918
  

  

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Matthias Kuhn]

Hi,

You are probably hitting issue 8923. This is more of a spatialite issue
than a QGIS issue. But there is a workaround for it, see here:
https://hub.qgis.org/issues/8923#note-5

Best regards
Matthias

On 11/10/2015 03:26 PM, Micha Silver wrote:
> I have found that QGIS cannot "see" any spatialite column that comes
> after the "geometry" column. When I alter a table, adding a column in
> a Spatialite, the new column which is after the geometry column in the
> database table just does not appear in QGIS. If I recreate the table,
> reordering the columns so that the geometry is last, all is good again.
>
> Is this a bug? or am I missing something?
>
> It's quite annoying considering that sqlite does not have any simple
> way to shuffle the columns around, other than dropping and recreating
> the table. And if I have foreign constraints defined, it becomes a
> whole row of dominoes that come down...
>
> Thanks,
> Micha
>
>
> ___
> Qgis-user mailing list
> Qgis-user@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

-- 
Help to improve QGIS rendering possibilities before Nov 30, 2015
http://www.opengis.ch/2015/11/02/qgis-crowdfunding-2-5d-rendering

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] columns in spatialite

[Bernd Vogelgesang]

Hi Micha,

yeah, working with spatialite dbs "seriously" is still quite uncomfortable.
When I try, I mostly have to use all available means to get the work done:  
DBManager, QSpatialite, SpatialiteGUI. Unfortunately I easily forget how  
sql works, so doing "easy" stuff becomes very time consuming and  
frustrating.


Do you think it would be technically possible to create a plugin like  
Table Manager plugin, that records the changes you want to do to the table  
and then creates the necessary sql commands and performs all that stuff in  
one go?


Cause this would be sth I really would be interested donating money on!

Cheers
Bernd

Am 10.11.2015, 15:26 Uhr, schrieb Micha Silver :

I have found that QGIS cannot "see" any spatialite column that comes  
after the "geometry" column. When I alter a table, adding a >column in a  
Spatialite, the new column which is after the geometry column in the  
database table just does not appear in QGIS. If I >recreate the table,  
reordering the columns so that the geometry is last, all is good again.


Is this a bug? or am I missing something?

It's quite annoying considering that sqlite does not have any simple way  
to shuffle the columns around, other than dropping and >recreating the  
table. And if I have foreign constraints defined, it becomes a whole row  
of dominoes that come down...


Thanks,
Micha




--
Bernd Vogelgesang
Siedlerstraße 2
91083 Baiersdorf/Igelsdorf
Tel: 09133-825374___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user