[Qgis-user] Problem: Can't open Spatialite tables in QGIS on Windows

[Brent Wood]

Hi,

We have been using QGIS & Spatialite for years and have recently encountered a problem in QGIS under Windows.

We connect to a spatialite database fine, we can list the tables, but when we try to open them we get an error:
db <...> table <...> is an invalid layer - not loaded (this is also written to the log)

I can open the db in DBManager and enter the query "select * from t_transect;" which runs & shows the data in the output pane.
When I try to load the layer as a map layer I get an error message referring me to the error log, but the log is empty.

This is using QGIS 3.34.1 on Windows.

The same database file works perfectly on Linux, with QGIS v3.34.3. A colleague has tried this & v3.36 on Windows with the same problem. The databases are generated with echo sounder data by the ESP3 application, and we have been able to use these in QGIS for years without problems until now.

Spatialite access the tables fine, the basic metadata is present:
select * from geometry_columns;
t_transect|geom_col|1|2|4326|0
t_echoint_transect_1d|geom_col|1|2|4326|0

The QGIS error message is not particularly helpful in determining the problem. Our workaround is to run the SQL we would have run in the QGIS database manager directly from the spatialite command line, exported to CSV & then open the CSV in QGIS, which works, but is not ideal.

For some context about these data:  The figure shows each "ping" from the echo sounder, scaled by the magnitude of the echo from fish, showing the location and density of fish encountered on each transect (ESP3 is used for the acoustic analysis). This dataset was from surveying a hill off the west coast of New Zealand (generated on Linux!).

[cid:d8335c55-7ad0-42bf-a514-c2ff56fc3815]
Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529
[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png] <https://www.niwa.co.nz>
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz> Facebook<https://www.facebook.com/nzniwa> LinkedIn<https://www.linkedin.com/company/niwa> Twitter<https://twitter.com/niwa_nz> Instagram<https://www.instagram.com/niwa_science> YouTube<https://www.youtube.com/channel/UCJ-j3MLMg1H59Ak2UaNLL3A>
To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems.
Note: This email is intended solely for the use of the addressee and may contain information that is confidential or subject to legal professional privilege. If you receive this email in error please immediately notify the sender and delete the email.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20240305/81f9d06b/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 56580 bytes
Desc: image.png
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20240305/81f9d06b/attachment-0001.png>