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

Mon Dec 12 06:47:31 PST 2022

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<mailto: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<mailto: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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221212/bee1a688/attachment-0001.htm>