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@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
