[gdal-dev] Seeking SQL wizardry

[Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC]]

BTW If this question is too specific to SQL and I should seek aid elsewhere, please let me know.

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.

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

So, collect feature centroids as a subexpression so gdal_get_pixel_value() has the inputs it needs.

Unfortunately, attempts to mutate this into a delete from query has failed.
    sql_query = f"delete from {label_name} where ndvi < 600 from \
        (gdal_get_pixel_value('{raster_fp}', {ndvi_band_num}, 'georef', ST_X(cnt), ST_Y(cnt)) as ndvi from \
            (select ST_Centroid(geom) as cnt from {label_name}))"

Per tradition, the SQL parser doesn’t provide much insight, merely “near "from": syntax error”.

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.

Ideas?

Best,
Jesse

Lead Computer Scientist
Science Systems and Applications, Inc.
Dr Compton Tucker Team
NASA Goddard Space Flight Center
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20250108/36214e77/attachment.htm>