<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hi,</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I'm trying to create an action to run an SQL on a spatialite database & show the result as a QGIS action.</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
In Linux this works by running a script in an xterm, but I can't work out how to do something similar under widows.</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Any suggestions?</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Thanks</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="Signature" style="color: inherit; background-color: inherit;">
<div style="margin-right: 0px; margin-left: 0px; font-family: Calibri, Arial, Helvetica, sans-serif;">
Brent Wood<br>
<br>
Principal Technician, Fisheries<br>
NIWA<br>
DDI: +64 (4) 3860529</div>
</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> QGIS-User <qgis-user-bounces@lists.osgeo.org> on behalf of Brent Wood via QGIS-User <qgis-user@lists.osgeo.org><br>
<b>Sent:</b> Wednesday, March 6, 2024 09:33<br>
<b>To:</b> 'Qgis-user@lists.osgeo.org' <qgis-user@lists.osgeo.org><br>
<b>Cc:</b> Nyall Dawson <nyall@north-road.com><br>
<b>Subject:</b> [Qgis-user] Problem: Can't open Spatialite tables in QGIS on Windows</font>
<div> </div>
</div>
<style type="text/css" style="display:none">
<!--
p
{margin-top:0;
margin-bottom:0}
-->
</style>
<div dir="ltr">
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
Hi,</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
We have been using QGIS & Spatialite for years and have recently encountered a problem in QGIS under Windows.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
We connect to a spatialite database fine, we can list the tables, but when we try to open them we get an error:</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
db <...> table <...> is an invalid layer - not loaded (this is also written to the log)</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
I can open the db in DBManager and enter the query "select * from t_transect;" which runs & shows the data in the output pane.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
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.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
This is using QGIS 3.34.1 on Windows.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
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.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
Spatialite access the tables fine, the basic metadata is present:</div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(237,92,87)">select * from geometry_columns;</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(237,92,87)">t_transect|geom_col|1|2|4326|0</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(237,92,87)">t_echoint_transect_1d|geom_col|1|2|4326|0</span></div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,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.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
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!).</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"><img id="x_image_0" width="469" height="287" size="56823" style="width:469px; height:287px" data-outlook-trace="F:2|T:2" src="cid:d8335c55-7ad0-42bf-a514-c2ff56fc3815"></span></div>
<div id="x_Signature">
<div style="margin-right:0px; margin-left:0px"><span style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">Brent Wood<br>
<br>
Principal Technician, Fisheries<br>
NIWA<br>
DDI: +64 (4) 3860529</span></div>
</div>
<table cellspacing="20" style="width:600px; font-size:8pt; font-family:Arial">
<tbody>
<tr>
<td><a href="https://www.niwa.co.nz/"><img src="https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png">
</a></td>
<td>
<table style="width:500px">
<tbody>
<span>Brent Wood <br>
<font color="#1793d2">Principal Technician - GIS and Spatial Data Management</font><br>
<font color="#1793d2">Programme Leader - Environmental Information Delivery</font><br>
+64-4-386-0529<br>
<br>
National Institute of Water & Atmospheric Research Ltd (NIWA)<br>
301 Evans Bay Parade Hataitai Wellington New Zealand<br>
<b>Connect with NIWA:</b> <a href="https://www.niwa.co.nz/">niwa.co.nz</a> <a href="https://www.facebook.com/nzniwa" originalsrc="https://www.facebook.com/nzniwa" shash="M19DkxF4PyFuaIPwSyk2vc33NV42cbTIQ+/jBQVUVsbLncoOpc1LmkdZruszzghyh09UqAnjeGNjESd9b2HpuEzxquKG7fn94k8jbqYYeM8oqNZu9MjX5WhyJRxr0UiGvBXWqo5jc3rTp6U7HXZiYxxIVru9f0OlqUoq8igCXa8=">
Facebook</a> <a href="https://www.linkedin.com/company/niwa" originalsrc="https://www.linkedin.com/company/niwa" shash="iOxCjwZAr+8dZ67QTGsIuL9os3paWt9bBIUsOl2+D7KcsyHK8JszDsO5aTPHnYG8wO+j1qXjeXa5BAR/J2X8RegaE4/U9h6XT/UtxHo1exc7tidwx6f/3Wmmayot/cH66rIHtqRpzpx0reJAWVJCydk8Vmtu15IDD45WnT2NfaA=">
LinkedIn</a> <a href="https://twitter.com/niwa_nz" originalsrc="https://twitter.com/niwa_nz" shash="NG5KY83c9pEVOW06mvI6bQH81dzzVB7sBi5DXMH2X7mpW4h019a640pz8RvdOffCJ4cTftDnV7CsBEPrHFMXRh7Rlf6+JSYiCxj9H0yLFTyCvDjCmryxoMgfEI/zVuKHRJsXy4KD5KvC33dAPylVmN/CyZkWwzn93IVpECNSG7s=">
Twitter</a> <a href="https://www.instagram.com/niwa_science" originalsrc="https://www.instagram.com/niwa_science" shash="gypwUX3Ry6z3MwBK5PHWJEA/8fBlaLG2orG5KjpgqBHiekvkFB6nFpz+kWvato/5WTWw/NXMcc1jv4BsmxHIW27q3ZIJmh9/5NnYYgSFb+B09EMpCWjbxavmEybZu0XpGSQz2/Tp2yfXmu3KP+YUd8kUJVgJLlA6pgatLyNCK+8=">
Instagram</a> <a href="https://www.youtube.com/channel/UCJ-j3MLMg1H59Ak2UaNLL3A" originalsrc="https://www.youtube.com/channel/UCJ-j3MLMg1H59Ak2UaNLL3A" shash="RH9Oo4cL5C6j5ZbVUhI4MiAFgO3RQwr51m/dIn857FG02rSYmGtIgBM/gNOKhzKQFb//yRRWOaWH1sO9KkAspi36zvD87WfCFB1ljJIKI1QJ8VCNHLlpkoCzMuFeq5uaB1sWZ1fQYdNblJeO/F2SjyRFQOHUYJ8Nxgc83NtsnPM=">
YouTube</a> </span>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
<font face="Arial" size="1">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.
<br>
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.
</font></div>
</body>
</html>