[raster] ST_SameAlignment() gives differing results based on which argument comes first

[evanderiel]

I have a sample here of several raster rows illustrating a problem that, in some cases, ST_SameAlignment() returns different results if it is called like ST_SameAlignment(left, right) and ST_SameAlignment(right, left). This is unexpected behavior, and I believe unintentional, but it's possible the raster data set is misconfigured in some way. This was first observed on a Google Cloud PostgreSQL 9 instance with PostGIS_full_version of:

POSTGIS="2.3.0 r15146" GEOS="3.5.0-CAPI-1.9.0 4392" PROJ="Rel. 6.0.0, March 1st, 2019" GDAL="GDAL 2.4.0dev, released 2018/99/99" LIBXML="2.9.1" LIBJSON="0.12.1" RASTER

and, because this is an old version of PostGIS, subsequently confirmed on PostgreSQL 11 on macOS 10.14.6 with PostGIS_full_version:

POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.3.3, released 2018/12/14" LIBXML="2.9.9" LIBJSON="0.13.1" LIBPROTOBUF="1.3.1" TOPOLOGY RASTER

(PostGIS 2.5.2 is the version available from Homebrew.)

The behavior can be seen by running the following SQL command once the sample data is included:

WITH reference AS (SELECT rid, rast from my_schema.usgs_dem_sample LIMIT 1)
SELECT 
	sample.rid, reference.rid, 
       ST_NotSameAlignmentReason(sample.rast, reference.rast), 
	ST_NotSameAlignmentReason(reference.rast, sample.rast)
FROM my_schema.usgs_dem_sample sample, reference
WHERE ST_SameAlignment(sample.rast, reference.rast) 
       <> ST_SameAlignment(reference.rast, sample.rast);

I have also seen this behavior continue even through ST_SnapToGrid() being called on both rasters, but until now I haven't had a chance to create a data sample to illustrate that behavior, and it may have something to do with the recently-fixed #4495 Incorrect geometry bbox returned from ST_SnapToGrid.

[Algunenano]

Also happening with current trunk:

WITH reference AS (SELECT rid, rast from my_schema.usgs_dem_sample LIMIT 1)
SELECT 
sample.rid, reference.rid, 
       ST_NotSameAlignmentReason(sample.rast, reference.rast), 
ST_NotSameAlignmentReason(reference.rast, sample.rast)
FROM my_schema.usgs_dem_sample sample, reference
WHERE ST_SameAlignment(sample.rast, reference.rast) 
       <> ST_SameAlignment(reference.rast, sample.rast);
NOTICE:  The rasters (pixel corner coordinates) are not aligned
NOTICE:  The rasters (pixel corner coordinates) are not aligned
NOTICE:  The rasters (pixel corner coordinates) are not aligned
NOTICE:  The rasters (pixel corner coordinates) are not aligned
NOTICE:  The rasters (pixel corner coordinates) are not aligned
NOTICE:  The rasters (pixel corner coordinates) are not aligned
NOTICE:  The rasters (pixel corner coordinates) are not aligned
NOTICE:  The rasters (pixel corner coordinates) are not aligned
NOTICE:  The rasters (pixel corner coordinates) are not aligned
NOTICE:  The rasters (pixel corner coordinates) are not aligned
   rid   |   rid   |               st_notsamealignmentreason                | st_notsamealignmentreason 
---------+---------+--------------------------------------------------------+---------------------------
 4503034 | 4493565 | The rasters (pixel corner coordinates) are not aligned | The rasters are aligned
 4503035 | 4493565 | The rasters (pixel corner coordinates) are not aligned | The rasters are aligned
 4503036 | 4493565 | The rasters (pixel corner coordinates) are not aligned | The rasters are aligned
 4503037 | 4493565 | The rasters (pixel corner coordinates) are not aligned | The rasters are aligned
 4503038 | 4493565 | The rasters (pixel corner coordinates) are not aligned | The rasters are aligned
 4503039 | 4493565 | The rasters (pixel corner coordinates) are not aligned | The rasters are aligned
 4503040 | 4493565 | The rasters (pixel corner coordinates) are not aligned | The rasters are aligned
 4503041 | 4493565 | The rasters (pixel corner coordinates) are not aligned | The rasters are aligned
 4503042 | 4493565 | The rasters (pixel corner coordinates) are not aligned | The rasters are aligned
 4503043 | 4493565 | The rasters (pixel corner coordinates) are not aligned | The rasters are aligned
(10 rows)

[evanderiel]

creates sample test case

[Algunenano]

This looks like a floating point issue. For example, one of the failures has these values:

NOTICE:  xw: -96.76916666545137957200
NOTICE:  rast1->ipX: -96.76907407407122718723
NOTICE:  yw: 41.99129629584308531776
NOTICE:  yw, rast1->ipY: 41.99129629584308531776
NOTICE:  The rasters (pixel corner coordinates) are not aligned

Considering there are multiple calls to ROUND, which calls floor, pow or ceil, I'd say that might be pretty delicate.

[robe]

I'm debating if we should just mark this one as won't fix. It's been around for so long and sounds dangerous to try to fix. Going to move to a Fund Me for now to get it out of the way.