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
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
