[Qgis-user] Reading of large SQLite files

Mon Oct 22 03:33:24 PDT 2012

Sorry for bringing back an old thread.
I now have the data in SpatiaLite format and am using that reader (the
yellowy cylinder icon), but unfortunately QGIS is doing the exact same
thing as it did when it was in SQLite format. This is a smaller dataset
(1,344,633 features), but QGIS still takes ~130 seconds to "add" it.
However once loaded, panning is instantaneous (it has spatial indexes).
Looking Process Explorer, I believe QGIS is "reading" the entire dataset on
"add".

Am I doing something wrong? Is that icon the native reader?

Thanks,
Jonathan

On 7 September 2012 13:45, Andreas Neumann <a.neumann at carto.net> wrote:

> Hi,
>
> I doubt that someone would work on it - if you report it as a bug.
>
> The OGR way often offers an alternative loading to the native data
> providers in QGIS. As an example there is a native Postgis provider that
> offers more options compared to loading Postgis through OGR. As the native
> method is faster and more powerful, the QGIS developers won't spend time to
> improve the slower, alternative option.
>
> The same is with SpatiaLite.
>
> But yes - I would also consider it a bug if it is offered through OGR.
>
> Andreas
>
>
> On Fri, 7 Sep 2012 13:05:20 +0100, Jonathan Moules wrote:
>
>> Hi Andreas,
>> Thanks for the prompt reply. FME 2013 (now in beta) has a SpatiaLite
>> writer in it which is what I'm going to try now I've seen how QGIS
>> handles this larger dataset (it handed the 73MB - ~200,000
>> feature SQLite file with no problems at all).
>>
>> My email was more a - should it take this long just to add it? - kind
>> of queston. It seems like a bug to me but I figured I'd ask before
>> reporting it. I do appreciate its not the recommended way to store
>> stuff but is this worth reporting as a bug? There certainly seems to
>> be something weird going on for it to take that long.
>> Cheers,
>> Jonathan
>>
>> On 7 September 2012 12:51, Andreas Neumann  wrote:
>>
>>  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
>>> ______________________________**_________________
>>> Qgis-user mailing list
>>> Qgis-user at lists.osgeo.org [1]
>>> http://lists.osgeo.org/**mailman/listinfo/qgis-user<http://lists.osgeo.org/mailman/listinfo/qgis-user>[2]
>>>
>>
>>  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.
>>
>>
>> Links:
>> ------
>> [1] mailto:Qgis-user at lists.osgeo.**org <Qgis-user at lists.osgeo.org>
>> [2] http://lists.osgeo.org/**mailman/listinfo/qgis-user<http://lists.osgeo.org/mailman/listinfo/qgis-user>
>> [3] mailto:a.neumann at carto.net
>>
>
> --
> --
> Andreas Neumann
> Böschacherstrasse 10A
> 8624 Grüt (Gossau ZH)
> Switzerland
>

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20121022/3439552e/attachment.html>