Strange result with raster ST_Intersects

[stev00]

I have odd behaviour when trying to use the ST_Intersects function with rasters, which I am using to filter raster tiles using an index for an ST_Value call.

On both Linux Red Hat (RDS) and Windows using Postgres 10.1 and Postgis 2.4 I get a null result for the filter call for a very small number of point geometries (in fact only one so far from around 1000 points). The only significant feature is that the x value of this point is a whole integer.

I am attaching a full sql file to reproduce a minimal version of the problem. If you look at the rasters in QGIS you should be able to see it covers the point, and adding a very small increment or decrement to the X value means a value is returned (but no difference with y value).

This combined with the whole number issue above means I wonder if it is something to do with the integer value.

I will continue looking into this, and it could obviously be a dependent library issue, but would be interested to see if others can reproduce it.

[stev00]

minimally reproduce potential bug

[stev00]

I have done some more tests with the above data. The SQL commands run and results are shown below. I guess this is related to precision - it seems that ST_Worldtorastercoord gives different results to ST_Rastertoworldcoord. Will try and do a bit more digging.

with r as(
SELECT * from test_raster WHERE rast && ST_SetSRID(ST_GeomFromText('POINT(529004 182915.87)'),27700) AND fn_st_intersects_log(rast, ST_SetSRID(ST_GeomFromText('POINT(529004 182915.87)'),27700), 1) 
)

-- result columnx=51, rowy=26
select (ST_worldtorastercoord(rast, 529004, 182915.87)).* from r 

--result st_rastertoworldcoordx=529003.000000106 st_width=50 st_height=56 st_pixelwidth=1.00000000000698
select ST_RasterToWorldCoordX(rast, 50),st_width(rast), st_height(rast), ST_PixelWidth(rast) from r 

[robe]

As much as I'd like to fix this for 2.4.4, I don't have enough time to investigate the issue before release or even if there is one.

I did do enough to determine that yes I got no ST_Value answer back for the ones you say don't work , but haven't confirmed if that's an issue or not or just there is no value for that part.

If it's an issue it's an issue in 2.2 as well so pushing to 2.2

[stev00]

A similar issue has been reported on GIS Stack Exchange:

​https://gis.stackexchange.com/questions/279949/error-with-st-value-st-nearestvalue-out-of-range-raster-coordinates/292213#292213

[Algunenano]

Moving to a non EOL release.

[stev00]

I tested this today with Postgres 14, Postgis 3.3 and GDAL 3.6dev. I could not reproduce this so I think this bug can be closed out - probably something to do with GDAL I expect