[postgis-users] selected aligned tiles in pgraster between several large raster tables.

[dustymugs]

On 06/12/2013 11:06 AM, Graeme B. Bell wrote:
>> Have you considered storing your rasters outside the database with
>> raster2pgsql's -R flag? I usually recommend this if the rasters are
>> readonly. This also minimizes the amount of information stored within
>> the database to just the metadata of the out-db rasters.
>>
>> -bborie
> 
> Hi bborie,
> 
> Thanks very much for your reply, but unfortunately I feel in-DB vs out-DB is orthogonal to the issue I'm talking about. 
> 
> It makes no real difference here whether the rasters are stored in-DB or out-of-DB from the point of view of the SQL interface. 
> 
> The question I'm asking is about how you can best use an SQL and PostGIS interface to perform comparisons or calculations using *equivalent tiles* of *logically distinct rasters* through PostGIS - without resorting to lumping them together as bands in a single raster. 
> 
> Graeme. 

in-db and out-db does matter. Your SQL asks the database to fetch some
data and the size of said data (and where that data is) will affect the
performance of the SQL.

A.rast::geometry = B.rast::geometry will check for spatially equal
tiles. This check will be faster on out-db vs in-db as there's less data
for postgres to load regardless of your SQL.

If you're asking for a canned SQL query...

SELECT
	SOMEWORK
FROM A
JOIN B
	ON A.rast::geometry = B.rast::geometry

-bborie