<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Jukka,</p>
<p>currently the driver doesn't try to use the RTree of the base
table (it could probably do it, but it would require first to
check that both the FID column and geometry column of the same
underlying table are available)</p>
<p>Jukka, Bo,<br>
</p>
<p>Regarding the use of Spatialite functions, I think the cleanest
way is to define an extension to register such computed geometry
column. That's what I've done in
<a class="moz-txt-link-freetext" href="https://github.com/OSGeo/gdal/pull/7848/commits/8d7468f5342e3e9e54c11c913b8ca53f3effc4b3">https://github.com/OSGeo/gdal/pull/7848/commits/8d7468f5342e3e9e54c11c913b8ca53f3effc4b3</a>
. It is the responsibility of the user creating the view to
register the extension for its table, as show in
<a class="moz-txt-link-freetext" href="https://github.com/OSGeo/gdal/blob/8d7468f5342e3e9e54c11c913b8ca53f3effc4b3/doc/source/drivers/vector/gpkg_spatialite_computed_geom_column.rst#extension-name-or-template">https://github.com/OSGeo/gdal/blob/8d7468f5342e3e9e54c11c913b8ca53f3effc4b3/doc/source/drivers/vector/gpkg_spatialite_computed_geom_column.rst#extension-name-or-template</a></p>
<p>The
<a class="moz-txt-link-freetext" href="https://github.com/OSGeo/gdal/blob/master/swig/python/gdal-utils/osgeo_utils/samples/validate_gpkg.py">https://github.com/OSGeo/gdal/blob/master/swig/python/gdal-utils/osgeo_utils/samples/validate_gpkg.py</a>
script (once updated by the PR) will use that to avoid erroring
out.<br>
</p>
<p>Even<br>
</p>
<div class="moz-cite-prefix">Le 28/05/2023 à 19:12, Rahkonen Jukka a
écrit :<br>
</div>
<blockquote type="cite"
cite="mid:AM6PR09MB3222E7F36C80B6A5806759C1FD459@AM6PR09MB3222.eurprd09.prod.outlook.com">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<style>@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}pre
{mso-style-priority:99;
mso-style-link:"HTML-esimuotoiltu Char";
margin:0cm;
font-size:10.0pt;
font-family:"Courier New";}span.HTML-esimuotoiltuChar
{mso-style-name:"HTML-esimuotoiltu Char";
mso-style-priority:99;
mso-style-link:HTML-esimuotoiltu;
font-family:"Consolas",serif;}span.Shkpostityyli21
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}div.WordSection1
{page:WordSection1;}</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-US">Fixed for this use case, but what if user wants
to use ST_Tranform(… in the view? Or something that may
change the geometry type like ST_Intersection, ST_Buffer
etc? In these cases I think that at least the usage of the
spatial index of the main table should be disabled. But how
could GDAL know when to skip the spatial index and when not?
With a special column in gpkg_ogr_contents perhaps?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-US">Somehow I do not feel comfortable. Maybe there
should be a warning in
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-US"><a
href="https://gdal.org/drivers/vector/gpkg.html#spatial-views"
moz-do-not-send="true" class="moz-txt-link-freetext">https://gdal.org/drivers/vector/gpkg.html#spatial-views</a>
that SpatiaLite functions should be used carefully in the
views and such databases should be delivered only for known
users, while views with uncomputed geometries should be safe
and interoperable.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-US">-Jukka Rahkonen-<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US">Lähettäjä:</span></b><span
lang="EN-US"> gdal-dev
<a class="moz-txt-link-rfc2396E" href="mailto:gdal-dev-bounces@lists.osgeo.org"><gdal-dev-bounces@lists.osgeo.org></a>
<b>Puolesta </b>Even Rouault<br>
<b>Lähetetty:</b> sunnuntai 28. toukokuuta 2023 0.39<br>
<b>Vastaanottaja:</b> Bo Victor Thomsen
<a class="moz-txt-link-rfc2396E" href="mailto:bo.victor.thomsen@gmail.com"><bo.victor.thomsen@gmail.com></a>;
<a class="moz-txt-link-abbreviated" href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> Re: [gdal-dev] Spatial view in GeoPackage<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p>Bo Victor,<o:p></o:p></p>
<p>there was indeed a bug in the OGR GPKG driver preventing you
to do what you wanted. Fixed per
<a href="https://github.com/OSGeo/gdal/pull/7848"
moz-do-not-send="true" class="moz-txt-link-freetext">https://github.com/OSGeo/gdal/pull/7848</a><o:p></o:p></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.<o:p></o:p></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.<o:p></o:p></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.<o:p></o:p></p>
<p>Even<o:p></o:p></p>
<div>
<p class="MsoNormal">Le 27/05/2023 à 22:08, Bo Victor Thomsen
a écrit :<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p>Hi list -<o:p></o:p></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.<o:p></o:p></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...<o:p></o:p></p>
<p>What I've done: <o:p></o:p></p>
<p><b><span style="font-family:"Courier New"">-
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;</span></b><o:p></o:p></p>
<p>AFAIK, this is the necessary steps to make a spatial view
visible in a GeoPackage. And it works; sort of...<o:p></o:p></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.<o:p></o:p></p>
<p>Digging a litle bit deeper: Using ogrinfo like this: <o:p></o:p></p>
<p><b><span style="font-family:"Courier New"">ogrinfo
-ro -so data_marker_vandloeb_soe.gpkg marker_2019_cvr</span></b><o:p></o:p></p>
<p>shows the layers SRS WKT as (unknown).<o:p></o:p></p>
<p>Using the same command on the base layer <b><span
style="font-family:"Courier New"">marker_2019
</span></b>gives the correct WKT.<o:p></o:p></p>
<p>25832 is the correct EPSG number for the CRS. And the
definition exists in the
<b><span style="font-family:"Courier New"">gpkg_spatial_ref_sys</span></b>
table.<o:p></o:p></p>
<p>What am I doing wrong ? Or is it just "out of scope" for
OGR ?? <o:p></o:p></p>
<pre>-- <o:p></o:p></pre>
<pre>Med venlig hilsen / Best regards<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>Bo Victor Thomsen<o:p></o:p></pre>
<p class="MsoNormal"><br>
<br>
<o:p></o:p></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>gdal-dev mailing list<o:p></o:p></pre>
<pre><a href="mailto:gdal-dev@lists.osgeo.org" moz-do-not-send="true" class="moz-txt-link-freetext">gdal-dev@lists.osgeo.org</a><o:p></o:p></pre>
<pre><a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" moz-do-not-send="true" class="moz-txt-link-freetext">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><o:p></o:p></pre>
</blockquote>
<pre>-- <o:p></o:p></pre>
<pre><a href="http://www.spatialys.com/" moz-do-not-send="true" class="moz-txt-link-freetext">http://www.spatialys.com</a><o:p></o:p></pre>
<pre>My software is free, but my time generally not.<o:p></o:p></pre>
</div>
</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>