<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Bo Victor,</p>
<p>there was indeed a bug in the OGR GPKG driver preventing you to
do what you wanted. Fixed per
<a class="moz-txt-link-freetext" href="https://github.com/OSGeo/gdal/pull/7848">https://github.com/OSGeo/gdal/pull/7848</a></p>
<p>The issue was specific to the fact that the geometry column of
the view is computed, and not just the simple selection of one of
a table.</p>
<p>Note that such GeoPackage will probably only be readable by
implementations that install Spatialite (or Spatialite compatible
functions). It might also be prudent to wrap the result of
ST_Multi() with AsGPB() to force creating a GeoPackage geometry
blob instead of a Spatialite one. The OGR GPKG driver is tolerant
to receiving both types of geometries, but technically the content
should be a GeoPackage one.</p>
<p>Inserting into gpkg_ogr_contents is optional. Its purpose is to
accelerate counting the number of features. But for layers <
let's say 100 k features, it probably doesn't impact performance
much.<br>
</p>
<p>Even<br>
</p>
<div class="moz-cite-prefix">Le 27/05/2023 à 22:08, Bo Victor
Thomsen a écrit :<br>
</div>
<blockquote type="cite"
cite="mid:524937cf-9890-6165-9949-8a549b867dd0@gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<p>Hi list -</p>
<p>I'm working with making spatial views "visible" for QGIS, i.e.
so they look like a read-only table from the data source
manager.</p>
<p>However, I have one problem: QGIS doesn't recognize the crs
for the view. I've tracked the problem down to that <b>OGR</b>
dosn't recognize the crs for the view. Hence this mail...</p>
<p>What I've done: <br>
</p>
<p><b><font face="monospace">- creating the view: A union of
parcels with the same owner (CVR-number)<br>
CREATE VIEW marker_2019_cvr AS<br>
SELECT "CVR", SUM(COALESCE(st_area(geom),0.0)) as area_m2,
count(*) as antal, st_multi(st_union(geom)) as geom from
marker_2019 group by 1;<br>
<br>
-- insert or update gpkg_contents<br>
INSERT OR REPLACE INTO
gpkg_contents(table_name,data_type,identifier,min_x,min_y,max_x,max_y,srs_id)<br>
SELECT <br>
'marker_2019_cvr' AS table_name, <br>
'features' AS data_type,<br>
'marker_2019_cvr' AS identifier, <br>
MIN(ST_MinX(geom)) AS min_x, <br>
MIN(ST_MinY(geom)) AS min_y, <br>
MAX(ST_MaxX(geom)) AS max_x, <br>
MAX(ST_MaxY(geom)) AS max_y,<br>
25832 AS srs_id -- same srs as the base layer for the
view<br>
FROM marker_2019_cvr;<br>
<br>
-- insert or update gpkg_geometry_columns<br>
INSERT OR REPLACE INTO gpkg_geometry_columns ( table_name,
column_name , geometry_type_name, srs_id, z, m)<br>
VALUES ('marker_2019_cvr', 'geom', 'MULTIPOLYGON', 25832,
0, 0);<br>
<br>
-- insert or update gpkg_ogr_columns (necessary ?)<br>
INSERT OR REPLACE INTO
gpkg_ogr_contents(table_name,feature_count)<br>
SELECT <br>
'marker_2019_cvr' AS table_name,<br>
count(*) AS feature_count <br>
FROM marker_2019_cvr;<br>
</font></b></p>
<p>AFAIK, this is the necessary steps to make a spatial view
visible in a GeoPackage. And it works; sort of...</p>
<p>The QGIS datasource manager shows the view as a spatial table.
I can add it to the QGIS map, but... it can't find the CRS for
the layer and defaults to the projects CRS.</p>
<p>Digging a litle bit deeper: Using ogrinfo like this: <br>
</p>
<p><b><font face="monospace">ogrinfo -ro -so
data_marker_vandloeb_soe.gpkg marker_2019_cvr</font></b></p>
<p>shows the layers SRS WKT as (unknown).</p>
<p>Using the same command on the base layer <b><font
face="monospace">marker_2019 </font></b>gives the correct
WKT.</p>
<p>25832 is the correct EPSG number for the CRS. And the
definition exists in the <b><font face="monospace">gpkg_spatial_ref_sys</font></b>
table.<br>
</p>
<p>What am I doing wrong ? Or is it just "out of scope" for OGR ??
<br>
</p>
<pre class="moz-signature" cols="72">--
Med venlig hilsen / Best regards
Bo Victor Thomsen</pre>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
gdal-dev mailing list
<a class="moz-txt-link-abbreviated" href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/gdal-dev">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a>
</pre>
</blockquote>
<pre class="moz-signature" cols="72">--
<a class="moz-txt-link-freetext" href="http://www.spatialys.com">http://www.spatialys.com</a>
My software is free, but my time generally not.</pre>
</body>
</html>