<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hi,</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I'm looking to open a sqlite3 (not spatialite) database in QGIS.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I have a table in the db with a lat column & a lon column with EPSG:4326 coordinates<br>
</div>
<div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I have written a VRT file to enable opening the table with a virtual geometry column. This works fine with ogrinfo on the command line, but creates an empty layer with no geometry type, columns or features in QGIS.
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
The VRT file contains:</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<OGRVRTDataSource>
<div> <OGRVRTLayer name="test"></div>
<div> <SrcDataSource>test.db</SrcDataSource> </div>
<div> <SrcLayer>test</SrcLayer> </div>
<div> <GeometryType>wkbPoint</GeometryType></div>
<div> <LayerSRS>WGS84</LayerSRS></div>
<div> <GeometryField encoding="PointFromColumns" x="lon" y="lat"/> </div>
<div> </OGRVRTLayer></div>
</OGRVRTDataSource><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Testing with ogrinfo is successful, the text returned is below. <br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
The table contents are:</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
sqlite> select * from test;
<div>id|lat|lon</div>
1|-41.354|174.567<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Can anyone explain why this fails in QGIS? <br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I'm running QGIS v3.16.11 on Linux (deb platform)<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Thanks,</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Brent Wood<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
ogrinfo result:<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<span style="font-family: "Courier New", monospace;">ogrinfo -geom=YES -al test.vrt</span>
<div><span style="font-family: "Courier New", monospace;">INFO: Open of `test.vrt'</span></div>
<div><span style="font-family: "Courier New", monospace;"> using driver `OGR_VRT' successful.</span></div>
<div><span style="font-family: "Courier New", monospace;">- 'VirtualXPath' [XML Path Language - XPath]</span></div>
<div><br>
</div>
<div><span style="font-family: "Courier New", monospace;">Layer name: test</span></div>
<div><span style="font-family: "Courier New", monospace;">Geometry: Point</span></div>
<div><span style="font-family: "Courier New", monospace;">Feature Count: 1</span></div>
<div><span style="font-family: "Courier New", monospace;">Extent: (-41.354000, 174.567000) - (-41.354000, 174.567000)</span></div>
<div><span style="font-family: "Courier New", monospace;">Layer SRS WKT:</span></div>
<div><span style="font-family: "Courier New", monospace;">GEOGCRS["WGS 84",</span></div>
<div><span style="font-family: "Courier New", monospace;"> DATUM["World Geodetic System 1984",</span></div>
<div><span style="font-family: "Courier New", monospace;"> ELLIPSOID["WGS 84",6378137,298.257223563,</span></div>
<div><span style="font-family: "Courier New", monospace;"> LENGTHUNIT["metre",1]]],</span></div>
<div><span style="font-family: "Courier New", monospace;"> PRIMEM["Greenwich",0,</span></div>
<div><span style="font-family: "Courier New", monospace;"> ANGLEUNIT["degree",0.0174532925199433]],</span></div>
<div><span style="font-family: "Courier New", monospace;"> CS[ellipsoidal,2],</span></div>
<div><span style="font-family: "Courier New", monospace;"> AXIS["geodetic latitude (Lat)",north,</span></div>
<div><span style="font-family: "Courier New", monospace;"> ORDER[1],</span></div>
<div><span style="font-family: "Courier New", monospace;"> ANGLEUNIT["degree",0.0174532925199433]],</span></div>
<div><span style="font-family: "Courier New", monospace;"> AXIS["geodetic longitude (Lon)",east,</span></div>
<div><span style="font-family: "Courier New", monospace;"> ORDER[2],</span></div>
<div><span style="font-family: "Courier New", monospace;"> ANGLEUNIT["degree",0.0174532925199433]],</span></div>
<div><span style="font-family: "Courier New", monospace;"> ID["EPSG",4326]]</span></div>
<div><span style="font-family: "Courier New", monospace;">Data axis to CRS axis mapping: 2,1</span></div>
<div><span style="font-family: "Courier New", monospace;">FID Column = rowid</span></div>
<div><span style="font-family: "Courier New", monospace;">id: Integer (0.0)</span></div>
<div><span style="font-family: "Courier New", monospace;">lat: Real (0.0)</span></div>
<div><span style="font-family: "Courier New", monospace;">lon: Real (0.0)</span></div>
<div><span style="font-family: "Courier New", monospace;">OGRFeature(test):1</span></div>
<div><span style="font-family: "Courier New", monospace;"> id (Integer) = 1</span></div>
<div><span style="font-family: "Courier New", monospace;"> lat (Real) = <span>-41.354</span></span></div>
<div><span style="font-family: "Courier New", monospace;"> lon (Real) = <span>174.567</span></span></div>
<span style="font-family: "Courier New", monospace;"> POINT (-41.354 174.567)</span><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<br>
</div>
<table style="width: 600px; font-size: 8pt; font-family: Arial" cellspacing="20">
<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">
Facebook</a> <a href="https://www.linkedin.com/company/niwa">LinkedIn</a> <a href="https://twitter.com/niwa_nz">
Twitter</a> <a href="https://www.instagram.com/niwa_science">Instagram</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</font>
</body>
</html>