<html 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=utf-8">
<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:0in;
font-size:12.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 Preformatted Char";
margin:0in;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
span.EmailStyle22
{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:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style>
</head>
<body lang="EN-US" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt">Sorry for the spam – I meant the last message to be a question. The tests so far only account for explicitly provided values in the query string.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="color:black">From: </span></b><span style="color:black">gdal-dev <gdal-dev-bounces@lists.osgeo.org> on behalf of "Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] via gdal-dev" <gdal-dev@lists.osgeo.org><br>
<b>Reply-To: </b>"Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC]" <jesse.r.meyer@nasa.gov><br>
<b>Date: </b>Monday, December 12, 2022 at 9:44 AM<br>
<b>To: </b>Even Rouault <even.rouault@spatialys.com>, Rahkonen Jukka <jukka.rahkonen@maanmittauslaitos.fi>, "gdal-dev@lists.osgeo.org" <gdal-dev@lists.osgeo.org><br>
<b>Subject: </b>Re: [gdal-dev] [EXTERNAL] Re: SQL query to set column to GPKG raster pixel value of point intersection result<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
</div>
<p class="MsoNormal"><span style="font-size:11.0pt">Thanks Even, this looks good!<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">In my case, then the SQL query should resemble something like `select gdal_get_layer_pixel_value('byte', 1, 'georef', ST_X(geom), ST_Y(geom))`<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Where `geom` is a centroid.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="color:black">From: </span></b><span style="color:black">Even Rouault <even.rouault@spatialys.com><br>
<b>Date: </b>Thursday, December 8, 2022 at 5:04 PM<br>
<b>To: </b>"Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC]" <jesse.r.meyer@nasa.gov>, Rahkonen Jukka <jukka.rahkonen@maanmittauslaitos.fi>, "gdal-dev@lists.osgeo.org" <gdal-dev@lists.osgeo.org><br>
<b>Subject: </b>Re: [gdal-dev] [EXTERNAL] Re: SQL query to set column to GPKG raster pixel value of point intersection result<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
</div>
<p>Hi,<o:p></o:p></p>
<p>That sounds like a cool idea. I've had a crack at it in <a href="https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FOSGeo%2Fgdal%2Fpull%2F6877&data=05%7C01%7Cjesse.r.meyer%40nasa.gov%7C863e159aea4442e6870708dadc4f678f%7C7005d45845be48ae8140d43da96dd17b%7C0%7C0%7C638064530856204431%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=V8CA38M%2BnhrQIFDwbSnOuFxA7w0HdWG56%2Bo%2F3IhOWOk%3D&reserved=0">
https://github.com/OSGeo/gdal/pull/6877</a><o:p></o:p></p>
<p>Even<o:p></o:p></p>
<div>
<p class="MsoNormal">Le 08/12/2022 à 22:10, Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] via gdal-dev a écrit :<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span style="font-size:11.0pt">Hi,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">I want to perform this on tens of thousands of GPKG databases, each possessing upwards of millions of points, so automation (and performance) is a high value. The hope was that given both data sources leverage
the same driver, there was some hope of this working with the proper incantation, especially considering the library routines available by Spatialite in SQL queries. But without something like burn available in a SQL query then this can’t work at the query
level. This is easy to write in C/C++ but wanted to see if the database engine could handle it directly.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Jesse</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span lang="FI" style="color:black">From: </span></b><span lang="FI" style="color:black">Rahkonen Jukka
<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi"><jukka.rahkonen@maanmittauslaitos.fi></a><br>
<b>Date: </b>Thursday, December 8, 2022 at 3:45 PM<br>
<b>To: </b>"Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC]" <a href="mailto:jesse.r.meyer@nasa.gov">
<jesse.r.meyer@nasa.gov></a>, <a href="mailto:gdal-dev@lists.osgeo.org">"gdal-dev@lists.osgeo.org"</a>
<a href="mailto:gdal-dev@lists.osgeo.org"><gdal-dev@lists.osgeo.org></a><br>
<b>Subject: </b>[EXTERNAL] Re: SQL query to set column to GPKG raster pixel value of point intersection result</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span lang="FI" style="font-size:11.0pt"> </span><o:p></o:p></p>
</div>
<p class="MsoNormal"><span lang="FI" style="font-size:11.0pt">Hi,</span><o:p></o:p></p>
<p class="MsoNormal"><span lang="FI" style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">It feels like quite special tool. Have you considered to use the Sample Raster Values tool in QGIS? The description of the tool is:</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Sample raster values</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">This algorithm creates a new vector layer with the same attributes of the input layer and the raster values corresponding on the point location.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">If the raster layer has more than one band, all the band values are sampled.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">-Jukka Rahkonen-</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span lang="FI" style="font-size:11.0pt">Lähettäjä:</span></b><span lang="FI" style="font-size:11.0pt"> gdal-dev
<a href="mailto:gdal-dev-bounces@lists.osgeo.org"><gdal-dev-bounces@lists.osgeo.org></a>
<b>Puolesta </b>Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] via gdal-dev<br>
<b>Lähetetty:</b> torstai 8. joulukuuta 2022 20.07<br>
<b>Vastaanottaja:</b> <a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> [gdal-dev] SQL query to set column to GPKG raster pixel value of point intersection result</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><span lang="FI"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Hi,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">I have GPKG database with features represented by point geometries. I’d like to update these features, in a single SQL statement, to add a new column whose value is the pixel value of an external GPKG raster
for which the point geometry intersects with. I was hoping to find an OGR SQL function similar to burn for this, but I’ve come up empty handed.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Is this functionality available? Ideally, without resorting to manually updating each feature individually in a user application. Otherwise, perhaps a feature request is in order.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Thanks,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Jesse</span><o:p></o:p></p>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span style="font-size:11.0pt"><o:p> </o:p></span></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://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fgdal-dev&data=05%7C01%7Cjesse.r.meyer%40nasa.gov%7C863e159aea4442e6870708dadc4f678f%7C7005d45845be48ae8140d43da96dd17b%7C0%7C0%7C638064530856204431%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=KsjwkCtnoLmRw6cw8LS427cMe8isV1WzLW6vPS3Yeqc%3D&reserved=0">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><o:p></o:p></pre>
</blockquote>
<pre>-- <o:p></o:p></pre>
<pre><a href="https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.spatialys.com%2F&data=05%7C01%7Cjesse.r.meyer%40nasa.gov%7C863e159aea4442e6870708dadc4f678f%7C7005d45845be48ae8140d43da96dd17b%7C0%7C0%7C638064530856204431%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=gTxLbTlpgy0bBkF%2FtXI6tSGdOuZw2JbNGijxgxWHQoY%3D&reserved=0">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>