[Qgis-user] Reading of large SQLite files

[Andreas Neumann]

 Hi Jonathan,

 The recommended way to store, load and save SQLite data is through 
 SpatiaLite. The other method you describe is through FDO and GDAL (with 
 other libraries in between), a less direct connection.

 So please convert your data to SpatiaLite if you want to use it in 
 QGIS. OGR can do such conversions on the command line.

 You can also talk to the FME people and tell them to support SpatiaLite 
 directly in FME. These people usually listen to their customers. If 
 enough people demand SpatiaLite support they will react and provide it 
 with a future version.

 Andreas

 On Fri, 7 Sep 2012 12:44:55 +0100, Jonathan Moules wrote:
> Hi list,
> I have a large, ~9.5GB SQLite file (its FDO rather than SpatiaLite;
> I'm aware the later is preferred but FME 2012 doesn't write to them).
> My problem is that just adding it seems to be a massive task for 
> QGIS.
>
>  
> The database has 6 tables in it with a total of about 28.8 million
> features.
>  
> I do the following:
> Add Vector Layer -> select file -> Click Ok.
>
> QGIS now spends the next 9 minutes thrashing my hard disk.
>  
> I then get the "select the layers you want to add" box pop up. I
> select all the layers. QGIS then spends another 6 minutes thrashing 
> my
> hard drive before the first layer is added (I've already turned off
> "rendering" so there's no possiblity of drawing anything) - this 
> layer
> having about 20million features.
> Finally, 10 minutes after selecting the layers I want, and 20 minutes
> after I first clicked "add file", the layers are loaded to QGIS.
>  
>  
> Conversely, SQLiteStudio can detect there are six tables instantly. A
> "select count(*) from TABLENAME" takes about 6 seconds to run against
> the largest table (19,945,139 features). Thats all the information
> thats required for the "select vector layers to add" apart from
> geometry "type" which is a simple query to the geometry_columns 
> table.
> And again, that's just to get to the "select layers" page.
>  
> So; should QGIS be taking this long to load a SQLite file? Is this a
> known issue?
>  
> Panning around and actually using it is also a no-go for the largest
> layer, I guess because this format doesn't have spatial indexing
> (they're built dynamically). This I can understand, I'm just a bit
> confused as to why it takes to long to load in the first place.
>  
> Any thoughts?
>
> Jonathan
>
>  This transmission is intended for the named addressee(s) only and 
> may
> contain sensitive or protectively marked material up to RESTRICTED 
> and
> should be handled accordingly. Unless you are the named addressee (or
> authorised to receive it for the addressee) you may not copy or use
> it, or disclose it to anyone else. If you have received this
> transmission in error please notify the sender immediately. All email
> traffic sent to or from us, including without limitation all GCSX
> traffic, may be subject to recording and/or monitoring in accordance
> with relevant legislation.

-- 
 --
 Andreas Neumann
 Böschacherstrasse 10A
 8624 Grüt (Gossau ZH)
 Switzerland