<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@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;}
/* Style Definitions */
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;}
span.Shkpostityyli22
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 2.0cm 70.85pt 2.0cm;}
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]-->
</head>
<body lang="FI" link="blue" vlink="purple" style="word-wrap:break-word">
<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 lang="EN-US" style="mso-fareast-language:EN-US">I found
<a href="https://github.com/opengeospatial/geopackage/issues/282">https://github.com/opengeospatial/geopackage/issues/282</a> from 5 years ago that has also discussion about views and spatial indexes.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">-Jukka-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language: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>Lähettäjä:</b> Even Rouault <even.rouault@spatialys.com> <br>
<b>Lähetetty:</b> sunnuntai 28. toukokuuta 2023 22.52<br>
<b>Vastaanottaja:</b> Rahkonen Jukka <jukka.rahkonen@maanmittauslaitos.fi>; Bo Victor Thomsen <bo.victor.thomsen@gmail.com>; gdal-dev@lists.osgeo.org<br>
<b>Aihe:</b> Re: [gdal-dev] Spatial view in GeoPackage<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p>Jukka,<o:p></o:p></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)<o:p></o:p></p>
<p>Jukka, Bo,<o:p></o:p></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 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 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><o:p></o:p></p>
<p>The <a 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.<o:p></o:p></p>
<p>Even<o:p></o:p></p>
<div>
<p class="MsoNormal">Le 28/05/2023 à 19:12, Rahkonen Jukka a écrit :<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Hi,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language: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?</span><o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">Somehow I do not feel comfortable. Maybe there should be a warning in
</span><o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><a href="https://gdal.org/drivers/vector/gpkg.html#spatial-views">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.</span><o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">-Jukka Rahkonen-</span><o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> </span><o:p></o:p></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 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 href="mailto:bo.victor.thomsen@gmail.com">
<bo.victor.thomsen@gmail.com></a>; <a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> Re: [gdal-dev] Spatial view in GeoPackage</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-US"> </span><o:p></o:p></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">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>
<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">gdal-dev@lists.osgeo.org</a><o:p></o:p></pre>
<pre><a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev">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/">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>
</blockquote>
<pre>-- <o:p></o:p></pre>
<pre><a href="http://www.spatialys.com/">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>
</body>
</html>