[raster]: ST_SetGeoReference screws up out db

[robe]

Not sure what causes this issue whether a doc bug or something not right in out db support. I thought we had this probelm before and it was fixed, but I just updated to the latest.

PostgreSQL 9.2.2, compiled by Visual C++ build 1600, 64-bit POSTGIS="2.1.0SVN r11197" GEOS="3.4.0dev-CAPI-1.8.0 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

I imported this image twice — one as in db and one as outdb ​http://download.osgeo.org/postgis/logo_suite/adbadge_tall/adbadge_tall.png

SET PGPORT=5442
SET PGHOST=localhost
SET PGUSER=postgres
SET PGDATABASE=testpostgis21
raster2pgsql C:/pics/adbadge_tall.png test_pele | psql
raster2pgsql -e -R -a C:/pics/adbadge_tall.png test_pele | psql

-- After georeferencing

SELECT rid, (h).*
FROM (SELECT rid, ST_Histogram(ST_SetGeoReference(rast, '1 0 0 -1 445139 5415000'),1,4) As h
from test_pele) As a;

 rid | min | max | count  |      percent
-----+-----+-----+--------+--------------------
   1 |  15 |  75 |  13456 |  0.025542900531511
   1 |  75 | 135 |  23572 | 0.0447456340167046
   1 | 135 | 195 |  50711 | 0.0962623386484434
   1 | 195 | 255 | 439061 |  0.833449126803341
   2 |   0 |   0 | 526800 |                 -1
(5 rows)

But before that they return same answer

--before georeferencing --
SELECT rid, (h).*
FROM (SELECT rid, ST_Histogram(rast,1,4) As h
from test_pele) As a;

 rid | min | max | count  |      percent
-----+-----+-----+--------+--------------------
   1 |  15 |  75 |  13456 |  0.025542900531511
   1 |  75 | 135 |  23572 | 0.0447456340167046
   1 | 135 | 195 |  50711 | 0.0962623386484434
   1 | 195 | 255 | 439061 |  0.833449126803341
   2 |  15 |  75 |  13456 |  0.025542900531511
   2 |  75 | 135 |  23572 | 0.0447456340167046
   2 | 135 | 195 |  50711 | 0.0962623386484434
   2 | 195 | 255 | 439061 |  0.833449126803341
(8 rows)

haven't tested on 2.0 to see if similar issue.

[dustymugs]

That is expected behavior. There is an expectation that the metadata stored in database and the actual raster file's metadata match (specifically the geotransform matrix). If the metadata doesn't match, the output from a function (such as ST_Histogram) is unknown. Non-georeferenced rasters are always set the default geotransform (scaleX: 1 scaleY: -1 skewX/Y: 0 upperleftX/Y: 0 srid: 0).

[robe]

I think we discussed we should just throw an error for outdb then instead of causing mass confusion and having people have to refer to docs when weirdness ensues.

[dustymugs]

Maybe a warning instead? A user may want to be crazy…

[robe]

yes warning is fine and then also document note.

[dustymugs]

Fixed in r11298. Feel free to change/expand the note I left in the docs.