[gdal-dev] Seeking SQL wizardry

[Scott]

Hey Jesse,

I *think* I simplified your query a bit. Untested, but it may work.

delete from {label_name}
where gdal_get_pixel_value('{raster_fp}', {ndvi_band_num}, 'georef', 
ST_X(st_centroid(geom)), ST_Y(st_centroid(geom))) < 600

Scott

On 1/8/25 12:53, Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND 
APPLICATIONS INC] via gdal-dev wrote:
> 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
> 
> 
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev