<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>You don't necessarily need to pass a literal value. That can be
combined with any other SQLite function that return the
appropriate data type, like Spatialite functions:
<a class="moz-txt-link-freetext" href="https://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.1.html">https://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.1.html</a></p>
<div class="moz-cite-prefix">Le 12/12/2022 à 15:47, Meyer, Jesse R.
(GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] a écrit :<br>
</div>
<blockquote type="cite"
cite="mid:EB0E7291-CEAC-495A-BB48-E375A48FF13C@nasa.gov">
<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: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;}div.WordSection1
{page:WordSection1;}</style>
<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
<a class="moz-txt-link-rfc2396E" href="mailto:gdal-dev-bounces@lists.osgeo.org"><gdal-dev-bounces@lists.osgeo.org></a> on behalf of
"Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND
APPLICATIONS INC] via gdal-dev"
<a class="moz-txt-link-rfc2396E" href="mailto:gdal-dev@lists.osgeo.org"><gdal-dev@lists.osgeo.org></a><br>
<b>Reply-To: </b>"Meyer, Jesse R. (GSFC-618.0)[SCIENCE
SYSTEMS AND APPLICATIONS INC]"
<a class="moz-txt-link-rfc2396E" href="mailto:jesse.r.meyer@nasa.gov"><jesse.r.meyer@nasa.gov></a><br>
<b>Date: </b>Monday, December 12, 2022 at 9:44 AM<br>
<b>To: </b>Even Rouault
<a class="moz-txt-link-rfc2396E" href="mailto:even.rouault@spatialys.com"><even.rouault@spatialys.com></a>, Rahkonen Jukka
<a class="moz-txt-link-rfc2396E" href="mailto:jukka.rahkonen@maanmittauslaitos.fi"><jukka.rahkonen@maanmittauslaitos.fi></a>,
<a class="moz-txt-link-rfc2396E" href="mailto:gdal-dev@lists.osgeo.org">"gdal-dev@lists.osgeo.org"</a>
<a class="moz-txt-link-rfc2396E" href="mailto:gdal-dev@lists.osgeo.org"><gdal-dev@lists.osgeo.org></a><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
<a class="moz-txt-link-rfc2396E" href="mailto:even.rouault@spatialys.com"><even.rouault@spatialys.com></a><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]" <a class="moz-txt-link-rfc2396E" href="mailto:jesse.r.meyer@nasa.gov"><jesse.r.meyer@nasa.gov></a>,
Rahkonen Jukka
<a class="moz-txt-link-rfc2396E" href="mailto:jukka.rahkonen@maanmittauslaitos.fi"><jukka.rahkonen@maanmittauslaitos.fi></a>,
<a class="moz-txt-link-rfc2396E" href="mailto:gdal-dev@lists.osgeo.org">"gdal-dev@lists.osgeo.org"</a>
<a class="moz-txt-link-rfc2396E" href="mailto:gdal-dev@lists.osgeo.org"><gdal-dev@lists.osgeo.org></a><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"
moz-do-not-send="true">
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 style="color:black" lang="FI">From:
</span></b><span style="color:black" lang="FI">Rahkonen
Jukka
<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi"
moz-do-not-send="true"><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"
moz-do-not-send="true">
<jesse.r.meyer@nasa.gov></a>, <a
href="mailto:gdal-dev@lists.osgeo.org"
moz-do-not-send="true">"gdal-dev@lists.osgeo.org"</a>
<a href="mailto:gdal-dev@lists.osgeo.org"
moz-do-not-send="true"><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 style="font-size:11.0pt"
lang="FI"> </span><o:p></o:p></p>
</div>
<p class="MsoNormal"><span style="font-size:11.0pt" lang="FI">Hi,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt" lang="FI"> </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 style="font-size:11.0pt"
lang="FI">Lähettäjä:</span></b><span
style="font-size:11.0pt" lang="FI"> gdal-dev
<a href="mailto:gdal-dev-bounces@lists.osgeo.org"
moz-do-not-send="true"><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"
moz-do-not-send="true" class="moz-txt-link-freetext">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" 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://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" moz-do-not-send="true">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" moz-do-not-send="true">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>