<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=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><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:Aptos;
panose-1:2 11 0 4 2 2 2 2 2 4;}
@font-face
{font-family:"Segoe UI Symbol";
panose-1:2 11 5 2 4 2 4 2 2 3;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:12.0pt;
font-family:"Aptos",sans-serif;}
p.xmsonormal, li.xmsonormal, div.xmsonormal
{mso-style-name:x_msonormal;
margin:0in;
font-size:11.0pt;
font-family:"Aptos",sans-serif;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Aptos",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
mso-ligatures:none;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
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="EN-US" link="#467886" vlink="#96607D" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt">Hi Patrick,<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">I’m working with a GeoPackage database file, using the SQLITE dialect, and submitting the query through the ExecuteSQL interface on the database object in Python. This makes the art of crafting SQL queries
a bit tricky because ontop of SQL concerns there’s also the abstraction OGR must also maintain.<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">Looks like:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">delete from layer_name where gdal_get_pixel_value(…) < 600<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">Gets me further along, with your advice, thanks! I originally put the centroid determination in a subexpression because I hesitated with the thought that the SQL engine couldn’t / wouldn’t cache the result
for the parameters (and our geometries can be quite dense). But I’ll worry about that later, and I think SQLite can optimize this case. That sent me down a needlessly complicated path I think.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<div>
<div>
<p class="MsoNormal">Best,<o:p></o:p></p>
<p class="MsoNormal">Jesse<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Lead Computer Scientist<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Science Systems and Applications, Inc.<o:p></o:p></p>
<p class="MsoNormal">Dr Compton Tucker Team<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal">NASA Goddard Space Flight Center<span style="font-size:11.0pt"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<div id="mail-editor-reference-message-container">
<div>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal" style="margin-bottom:12.0pt"><b><span style="color:black">From:
</span></b><span style="color:black">P O'Toole <P.OToole@uwyo.edu><br>
<b>Date: </b>Wednesday, January 8, 2025 at 4:49</span><span style="font-family:"Arial",sans-serif;color:black"> </span><span style="color:black">PM<br>
<b>To: </b>gdal-dev@lists.osgeo.org <gdal-dev@lists.osgeo.org>, Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] <jesse.r.meyer@nasa.gov><br>
<b>Subject: </b>[EXTERNAL] Re: Seeking SQL wizardry<o:p></o:p></span></p>
</div>
<table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" align="left" style="border:solid black 1.5pt">
<tbody>
<tr>
<td width="100%" style="width:100.0%;border:none;background:#FFEB9C;padding:3.75pt 3.75pt 3.75pt 3.75pt">
<p class="MsoNormal" style="mso-element:frame;mso-element-frame-hspace:2.25pt;mso-element-wrap:around;mso-element-anchor-vertical:paragraph;mso-element-anchor-horizontal:column;mso-height-rule:exactly">
<b><span style="font-size:10.0pt;color:black">CAUTION:</span></b><span style="color:black">
</span><span style="font-size:10.0pt;color:black">This email originated from outside of NASA. Please take care when clicking links or opening attachments. Use the "Report Message" button to report suspicious messages to the NASA SOC.</span><span style="color:black">
</span><o:p></o:p></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal" style="margin-bottom:12.0pt"><br>
<br>
<o:p></o:p></p>
<div>
<div>
<p class="MsoNormal"><span style="color:black">Hi, Jesse,<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">I assume you're working in SQLAlchemy or something, so there may be more to this than I'm thinking, but if the parsing errors are actually coming from the Postgres server(?), there should be plenty of ways around
your issue, one way or another. Before you try to get especially fancy, I would try something simple like this, cutting down on the amount of aliasing and cross-referencing you're doing to just find the rows to kill:<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><b><span style="color:black"> DELETE FROM </span></b><span style="color:black">some_table
<b>WHERE </b>some_table.row_id = ( <b>SELECT</b> rowid <b>FROM </b>some_table <b>WHERE
</b>gdal_get_pixel_value('raster.tif', ndvi_band_num, 'georef', ST_X(ST_Centroid(geom)), ST_Y(ST_Centroid(geom)) < 600);</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">I may have a few details wrong because I don't have your SQL schema in front of me, but you essentially just want to find a simple SELECT-query that will return the desired row-IDs. You'll then just slap that into
subquery inside the WHERE-clause of your DELETE statement. You can fool around with the SELECT statement on its own until you know that's right first. If you're doing additional processing on these rows before you delete, I'd consider using a transaction so
any analysis you do happens on the exact same rows as those you're deleting.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">Hope that helps.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">- Patrick O'Toole<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">Full-Stack Developer<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">Wyoming Natural Diversity Database<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">University of Wyoming<o:p></o:p></span></p>
</div>
<div class="MsoNormal" align="center" style="text-align:center">
<hr size="0" width="100%" align="center">
</div>
<div id="divRplyFwdMsg">
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:black">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;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>Sent:</b> Wednesday, January 8, 2025 1:53 PM<br>
<b>To:</b> gdal-dev@lists.osgeo.org <gdal-dev@lists.osgeo.org><br>
<b>Subject:</b> [gdal-dev] Seeking SQL wizardry</span> <o:p></o:p></p>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
</div>
<div>
<div style="border:solid red 2.25pt;padding:1.0pt 4.0pt 1.0pt 4.0pt">
<p class="xmsonormal" style="line-height:11.35pt"><span style="font-family:"Segoe UI Symbol",sans-serif">◆</span> This message was sent from a non-UWYO address. Please exercise caution when clicking links or opening attachments from external sources.<o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="xmsonormal">BTW If this question is too specific to SQL and I should seek aid elsewhere, please let me know.<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">I am able to return a layer of rowids associated with features whose geotiff band value at the centroid location is above a particular threshold. I then turn back around and delete all such features manually in Python. But it would be
nice to do this with a delete from query.<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">The working select query is: select rowid, gdal_get_pixel_value(path/to/raster.tif, ndvi_band_num, ‘georef’, ST_X(cnt), ST_Y(cent)) as ndvi from (select ST_Centroid(geom) as cnt from layer_name) where ndvi < 600<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">So, collect feature centroids as a subexpression so gdal_get_pixel_value() has the inputs it needs.<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">Unfortunately, attempts to mutate this into a delete from query has failed.<o:p></o:p></p>
<p class="xmsonormal"> sql_query = f"delete from {label_name} where ndvi < 600 from \<o:p></o:p></p>
<p class="xmsonormal"> (gdal_get_pixel_value('{raster_fp}', {ndvi_band_num}, 'georef', ST_X(cnt), ST_Y(cnt)) as ndvi from \<o:p></o:p></p>
<p class="xmsonormal"> (select ST_Centroid(geom) as cnt from {label_name}))"<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">Per tradition, the SQL parser doesn’t provide much insight, merely “near "from": syntax error”.<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">Simpler queries like “delete from layer_name where ST_Area(GEOMETRY)” just work, without needing to pass through rowids around from subexpressions. So I hope it is just a matter of SQL spellcraft on my side that’s lacking, but I do know
some database engines don’t allow deleting from tables that source that table in subexpressions, so AFAIK this may just not be possible.<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">Ideas?<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<div>
<div>
<p class="xmsonormal"><span style="font-size:12.0pt">Best,</span><o:p></o:p></p>
<p class="xmsonormal"><span style="font-size:12.0pt">Jesse</span><o:p></o:p></p>
<p class="xmsonormal"><span style="font-size:12.0pt"> </span><o:p></o:p></p>
<p class="xmsonormal"><span style="font-size:12.0pt">Lead Computer Scientist</span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="font-size:12.0pt">Science Systems and Applications, Inc.</span><o:p></o:p></p>
<p class="xmsonormal"><span style="font-size:12.0pt">Dr Compton Tucker Team</span><o:p></o:p></p>
</div>
</div>
<p class="xmsonormal"><span style="font-size:12.0pt">NASA Goddard Space Flight Center</span><o:p></o:p></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>