<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hi, Jesse,</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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:</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt;">
<span style="color: rgb(0, 0, 0);"><b> DELETE FROM </b>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></div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hope that helps.</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
- Patrick O'Toole</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Full-Stack Developer</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Wyoming Natural Diversity Database</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
University of Wyoming</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> 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</font>
<div> </div>
</div>
<style>
<!--
@font-face
{font-family:"Cambria Math"}
@font-face
{font-family:Aptos}
p.x_MsoNormal, li.x_MsoNormal, div.x_MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Aptos",sans-serif}
span.x_EmailStyle17
{font-family:"Aptos",sans-serif;
color:windowtext}
.x_MsoChpDefault
{font-size:11.0pt}
@page WordSection1
{margin:1.0in 1.0in 1.0in 1.0in}
div.x_WordSection1
{}
-->
</style>
<div lang="EN-US" link="#467886" vlink="#96607D" style="word-wrap:break-word">
<div style="border:solid red 2.25pt; padding:1.0pt 4.0pt 1.0pt 4.0pt">
<p class="x_MsoNormal" style="line-height:11.35pt">◆ This message was sent from a non-UWYO address. Please exercise caution when clicking links or opening attachments from external sources.</p>
</div>
<br>
<div>
<div class="x_WordSection1">
<p class="x_MsoNormal">BTW If this question is too specific to SQL and I should seek aid elsewhere, please let me know.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">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.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">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</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">So, collect feature centroids as a subexpression so gdal_get_pixel_value() has the inputs it needs.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">Unfortunately, attempts to mutate this into a delete from query has failed.</p>
<p class="x_MsoNormal"> sql_query = f"delete from {label_name} where ndvi < 600 from \</p>
<p class="x_MsoNormal"> (gdal_get_pixel_value('{raster_fp}', {ndvi_band_num}, 'georef', ST_X(cnt), ST_Y(cnt)) as ndvi from \</p>
<p class="x_MsoNormal"> (select ST_Centroid(geom) as cnt from {label_name}))"</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">Per tradition, the SQL parser doesn’t provide much insight, merely “near "from": syntax error”.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">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.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">Ideas?</p>
<p class="x_MsoNormal"> </p>
<div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt">Best,</span></p>
<p class="x_MsoNormal"><span style="font-size:12.0pt">Jesse</span></p>
<p class="x_MsoNormal"><span style="font-size:12.0pt"> </span></p>
<p class="x_MsoNormal"><span style="font-size:12.0pt">Lead Computer Scientist</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt">Science Systems and Applications, Inc.</span></p>
<p class="x_MsoNormal"><span style="font-size:12.0pt">Dr Compton Tucker Team</span></p>
</div>
</div>
<p class="x_MsoNormal"><span style="font-size:12.0pt">NASA Goddard Space Flight Center</span></p>
</div>
</div>
</div>
</body>
</html>