Hi Patrick,
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.
Looks like:
delete from layer_name where gdal_get_pixel_value(…) < 600
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.
Best,
Jesse
Lead Computer Scientist
Science Systems and Applications, Inc.
Dr Compton Tucker Team
NASA Goddard Space Flight Center
From: P O'Toole <p.oto...@uwyo.edu>
Date: Wednesday, January 8, 2025 at 4:49 PM
To: gdal-dev@lists.osgeo.org <gdal-dev@lists.osgeo.org>, Meyer, Jesse R.
(GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] <jesse.r.me...@nasa.gov>
Subject: [EXTERNAL] Re: Seeking SQL wizardry
CAUTION: 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.
Hi, Jesse,
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:
DELETE FROM some_table WHERE some_table.row_id = ( SELECT rowid FROM
some_table WHERE gdal_get_pixel_value('raster.tif', ndvi_band_num, 'georef',
ST_X(ST_Centroid(geom)), ST_Y(ST_Centroid(geom)) < 600);
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.
Hope that helps.
- Patrick O'Toole
Full-Stack Developer
Wyoming Natural Diversity Database
University of Wyoming
________________________________
From: gdal-dev <gdal-dev-boun...@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>
Sent: Wednesday, January 8, 2025 1:53 PM
To: gdal-dev@lists.osgeo.org <gdal-dev@lists.osgeo.org>
Subject: [gdal-dev] Seeking SQL wizardry
◆ This message was sent from a non-UWYO address. Please exercise caution when
clicking links or opening attachments from external sources.
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
