[postgis-users] Efficient ways to query PostGIS Raster pixel values using Intersects

[Bborie Park]

On 12/12/2011 02:15 PM, Andrew Hill wrote:
> Hi Bborie,
>
> As far as I can tell, ST_MapAlgebra requires the same grid alignment and
> every other aspect? If so, this wouldn't fit use-cases where resampling is
> needed. Your code does not work for me. I get the following error,
> > ST_MapAlgebra: Provided raster do not have the same alignment. Aborting
>
> I worked to figure out what was going on and went through some iterations.
> As far as I can figure out, ST_MapAlgebra(rast,rast) requires two tiles of
> the exact same dimensions and proportions? So the following,
>
> SELECT ST_MapAlgebra(rast, ST_AsRaster(ST_Intersection(ST_Envelope(rast),
> tg),rast), 'rast1') FROM marso CROSS JOIN
> GEOMETRYFROMTEXT('MULTIPOLYGON(((10 10, 10 0, 0 0, 0 10, 10 10)))',4326) tg
> WHERE ST_Intersects(rast,GEOMETRYFROMTEXT('MULTIPOLYGON(((10 10, 10 0, 0 0,
> 0 10, 10 10)))',4326))
>
> Takes my bounding box, crops it to only portion that is within a tile, and
> then tries to ST_MapAlgebra, but will fail with the same error as above.
> However, this one works fine,
>
> SELECT ST_MapAlgebra(rast, ST_AsRaster(ST_Envelope(rast), rast), 'rast1')
> FROM marso LIMIT 1
>
> I assume because the tiles are the exact same.

Hey Andrew,

Whatever two rasters you plan on passing to ST_MapAlgebra can be tested 
using ST_SameAlignment.  If ST_SameAlignment returns true, ST_MapAlgebra 
won't have any issues.

The two rasters need to have the same scale, skew and SRID.  Their upper 
left corners must be on the same grid.

I wonder if you could just simplify your ST_MapAlgebra to...

ST_MapAlgebra(
	rast,
	ST_AsRaster(tg, rast),
	'rast1'
)

as the default ST_MapAlgebra is done on the intersection of the two raster.

Also, if you could send me the metadata (ST_Metadata) of the raster 
causing the alignment error, I can do some testing.  I've had one case 
of floating point weirdness occur with the alignment test but I thought 
I had resolved that.

-bborie

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu