[PostGIS] #5649: Since introduction of resampling on ST_Value (v3.2) behaviour on inexisting band has changed

[PostGIS]

#5649: Since introduction of resampling on ST_Value (v3.2) behaviour on inexisting
band has changed
---------------------+---------------------------
  Reporter:  lieven  |      Owner:  robe
      Type:  defect  |     Status:  new
  Priority:  medium  |  Milestone:  PostGIS 3.2.8
 Component:  raster  |    Version:  3.2.x
Resolution:          |   Keywords:
---------------------+---------------------------
Comment (by robe):

 Replying to [comment:4 lieven]:
 > Hi,
 > Have you any alternative solution for this scenario ? I wish to upgrade
 my postgis version but I'm stuck with this problem. If you have any clue
 to help me, it will be amazing
 >
 > PS: it seems to be also a problem with postgresql version. Working on 14
 not on 15 version
 >
 > Thanks a lot


 It's postgresql version specific?  T Are you testing against the same
 PostGIS version?  If you are seeing a difference between PostgreSQL 14 and
 15 using the same version of postgis, then it's most likely a GDAL version
 difference.
 Anyrate I'm seeing an issue with my latest dev instance running:


 {{{
 POSTGIS="3.5.0dev 3.4.0rc1-886-g03d6a1d77" [EXTENSION] PGSQL="160"
 GEOS="3.13.0dev-CAPI-1.18.0" PROJ="9.3.1 NETWORK_ENABLED=OFF
 URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj
 DATABASE_PATH=/usr/share/proj/proj.db" GDAL="GDAL 3.8.1, released
 2023/11/28" LIBXML="2.9.14" LIBJSON="0.17" LIBPROTOBUF="1.4.1"
 WAGYU="0.5.0 (Internal)" RASTER
 }}}

 Please post the


 {{{
 SELECT postgis_full_version();
 }}}

 Of the versions working as you would expect and the versions not working
 as you would expect.

 At anyrate if this is a postgis change, it was unintentional, so I'll see
 if we can resolve on our end..

 Regarding your query, an alternative solution that will work in both cases
 (this version does rely on postgresql returning null if array element item
 does not exist.

 You could also alternatively use a CASE WHEN ST_NumBands(rast) >= 7 THEN
 ST_Value(rast, 7) ELSE NULL END
 but from experience I think the CASE statement might slow it down more
 than below


 {{{
 WITH poi AS (
     SELECT ST_SetSRID(ST_Point(1.75, 46.37), 4326) AS coord
 )
 SELECT
     rast.time,
     v.elements[1] AS u_wind,
     v.elements[2] AS v_wind,
     v.elements[3] AS temperature,
     v.elements[4] AS humidity,
     v.elements[5] AS pressure,
     v.elements[6] AS cloud_cover,
     v.elements[7] AS precipitations,
     v.elements[8] AS ww,
     r.model
 FROM poi
 LEFT JOIN weather.inspire_2023_12_raster_4326 r ON (ST_Intersects(r.rast,
 poi.coord))
     LEFT JOIN LATERAL
       (SELECT array_agg(ST_Value(r.rast, i, poi.coord) ORDER BY i) AS
 elements
           FROM generate_series(1, ST_NumBands(r.rast)) AS i ) AS v ON true
 WHERE time = TIMESTAMP WITHOUT TIME ZONE '2023-12-20 00:00:00';
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5649#comment:5>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.