[gdal-dev] [EXTERNAL] Re: SQL query to set column to GPKG raster pixel value of point intersection result

Mon Dec 12 06:54:32 PST 2022

You don't necessarily need to pass a literal value. That can be combined 
with any other SQLite function that return the appropriate data type, 
like Spatialite functions: 
https://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.1.html

Le 12/12/2022 à 15:47, Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND 
APPLICATIONS INC] a écrit :
>
> Sorry for the spam – I meant the last message to be a question.  The 
> tests so far only account for explicitly provided values in the query 
> string.
>
> *From: *gdal-dev <gdal-dev-bounces at lists.osgeo.org> on behalf of 
> "Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] 
> via gdal-dev" <gdal-dev at lists.osgeo.org>
> *Reply-To: *"Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND 
> APPLICATIONS INC]" <jesse.r.meyer at nasa.gov>
> *Date: *Monday, December 12, 2022 at 9:44 AM
> *To: *Even Rouault <even.rouault at spatialys.com>, Rahkonen Jukka 
> <jukka.rahkonen at maanmittauslaitos.fi>, "gdal-dev at lists.osgeo.org" 
> <gdal-dev at lists.osgeo.org>
> *Subject: *Re: [gdal-dev] [EXTERNAL] Re: SQL query to set column to 
> GPKG raster pixel value of point intersection result
>
> Thanks Even, this looks good!
>
> In my case, then the SQL query should resemble something like `select 
> gdal_get_layer_pixel_value('byte', 1, 'georef', ST_X(geom), ST_Y(geom))`
>
> Where `geom` is a centroid.
>
> *From: *Even Rouault <even.rouault at spatialys.com>
> *Date: *Thursday, December 8, 2022 at 5:04 PM
> *To: *"Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS 
> INC]" <jesse.r.meyer at nasa.gov>, Rahkonen Jukka 
> <jukka.rahkonen at maanmittauslaitos.fi>, "gdal-dev at lists.osgeo.org" 
> <gdal-dev at lists.osgeo.org>
> *Subject: *Re: [gdal-dev] [EXTERNAL] Re: SQL query to set column to 
> GPKG raster pixel value of point intersection result
>
> Hi,
>
> That sounds like a cool idea. I've had a crack at it in 
> https://github.com/OSGeo/gdal/pull/6877 
> <https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FOSGeo%2Fgdal%2Fpull%2F6877&data=05%7C01%7Cjesse.r.meyer%40nasa.gov%7C863e159aea4442e6870708dadc4f678f%7C7005d45845be48ae8140d43da96dd17b%7C0%7C0%7C638064530856204431%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=V8CA38M%2BnhrQIFDwbSnOuFxA7w0HdWG56%2Bo%2F3IhOWOk%3D&reserved=0>
>
> Even
>
> Le 08/12/2022 à 22:10, Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS 
> AND APPLICATIONS INC] via gdal-dev a écrit :
>
>     Hi,
>
>     I want to perform this on tens of thousands of GPKG databases,
>     each possessing upwards of millions of points, so automation (and
>     performance) is a high value.  The hope was that given both data
>     sources leverage the same driver, there was some hope of this
>     working with the proper incantation, especially considering the
>     library routines available by Spatialite in SQL queries.  But
>     without something like burn available in a SQL query then this
>     can’t work at the query level.  This is easy to write in C/C++ but
>     wanted to see if the database engine could handle it directly.
>
>     Jesse
>
>     *From: *Rahkonen Jukka <jukka.rahkonen at maanmittauslaitos.fi>
>     <mailto:jukka.rahkonen at maanmittauslaitos.fi>
>     *Date: *Thursday, December 8, 2022 at 3:45 PM
>     *To: *"Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND
>     APPLICATIONS INC]" <jesse.r.meyer at nasa.gov>
>     <mailto:jesse.r.meyer at nasa.gov>, "gdal-dev at lists.osgeo.org"
>     <mailto:gdal-dev at lists.osgeo.org> <gdal-dev at lists.osgeo.org>
>     <mailto:gdal-dev at lists.osgeo.org>
>     *Subject: *[EXTERNAL] Re: SQL query to set column to GPKG raster
>     pixel value of point intersection result
>
>     Hi,
>
>     It feels like quite special tool. Have you considered to use the
>     Sample Raster Values tool in QGIS? The description of the tool is:
>
>     Sample raster values
>
>     This algorithm creates a new vector layer with the same attributes
>     of the input layer and the raster values corresponding on the
>     point location.
>
>     If the raster layer has more than one band, all the band values
>     are sampled.
>
>     -Jukka Rahkonen-
>
>     *Lähettäjä:*gdal-dev <gdal-dev-bounces at lists.osgeo.org>
>     <mailto:gdal-dev-bounces at lists.osgeo.org> *Puolesta *Meyer, Jesse
>     R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] via gdal-dev
>     *Lähetetty:* torstai 8. joulukuuta 2022 20.07
>     *Vastaanottaja:* gdal-dev at lists.osgeo.org
>     *Aihe:* [gdal-dev] SQL query to set column to GPKG raster pixel
>     value of point intersection result
>
>     Hi,
>
>     I have GPKG database with features represented by point
>     geometries.  I’d like to update these features, in a single SQL
>     statement, to add a new column whose value is the pixel value of
>     an external GPKG raster for which the point geometry intersects
>     with.  I was hoping to find an OGR SQL function similar to burn
>     for this, but I’ve come up empty handed.
>
>     Is this functionality available? Ideally, without resorting to
>     manually updating each feature individually in a user
>     application.  Otherwise, perhaps a feature request is in order.
>
>     Thanks,
>
>     Jesse
>
>     _______________________________________________
>
>     gdal-dev mailing list
>
>     gdal-dev at lists.osgeo.org
>
>     https://lists.osgeo.org/mailman/listinfo/gdal-dev  <https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fgdal-dev&data=05%7C01%7Cjesse.r.meyer%40nasa.gov%7C863e159aea4442e6870708dadc4f678f%7C7005d45845be48ae8140d43da96dd17b%7C0%7C0%7C638064530856204431%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=KsjwkCtnoLmRw6cw8LS427cMe8isV1WzLW6vPS3Yeqc%3D&reserved=0>
>
> -- 
> http://www.spatialys.com  <https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.spatialys.com%2F&data=05%7C01%7Cjesse.r.meyer%40nasa.gov%7C863e159aea4442e6870708dadc4f678f%7C7005d45845be48ae8140d43da96dd17b%7C0%7C0%7C638064530856204431%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=gTxLbTlpgy0bBkF%2FtXI6tSGdOuZw2JbNGijxgxWHQoY%3D&reserved=0>
> My software is free, but my time generally not.

-- 
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/20221212/234da4a4/attachment.htm>