[gdal-dev] [EXTERNAL] Re: SQL / gdal_get_pixel_value tips requested

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

Even,

That appears to have “just work”!

<sounds of bowing>

Jesse

From: Even Rouault <even.rouault at spatialys.com>
Date: Wednesday, May 8, 2024 at 1:15 PM
To: Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] <jesse.r.meyer at nasa.gov>, Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] via gdal-dev <gdal-dev at lists.osgeo.org>
Subject: [EXTERNAL] Re: [gdal-dev] SQL / gdal_get_pixel_value tips requested
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.



Jesse,

the answer lies in exploring available Spatialite SQL functions: https://www.gaia-gis.it/gaia-sins/spatialite-sql-5.1.0.html

ST_Centroid() returns a Point geometry. You may get its coordinates with ST_X() and ST_Y(). I suppose you'd need to use a sub-select construct to get things working, like

SELECT gdal_get_pixel_value(path, band_num, 'georef', ST_X(centroid), ST_Y(centroid)) FROM (SELECT ST_Centroid(geometry) AS centroid FROM ds) x

untested obviously!
Le 08/05/2024 à 19:09, Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] via gdal-dev a écrit :
Hi,

I’m wanting to execute a SQL query on a vector database like:
vector_mem_ds.ExecuteSQL(f"select ST_Centroid(GEOMETRY) as cnt, gdal_get_pixel_value('{raster_fp}', {ndvi_band_num}, 'georef', cnt.STX, cnt.STY) from ds", dialect="SQLITE")

In particular, I want to use the geometry’s centroid for gdal_get_pixel_value, but the latter function takes two parameters, and ST_Centroid provides 1 return value (a compound type Point), so I am hoping for some way to unpack this into the form necessary expected from get_pixel_value.  Apparently some GIS query engines do expose a STX and STY field but that’s if you trust random google results.  But as is this fails.

Is this possible largely as-is?

Thanks,
Jesse



_______________________________________________

gdal-dev mailing list

gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>

https://lists.osgeo.org/mailman/listinfo/gdal-dev

--

http://www.spatialys.com<http://www.spatialys.com/>

My software is free, but my time generally not.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20240508/20857360/attachment-0001.htm>