[postgis-users] WITH ...

juli g. pausas juli.g.pausas at uv.es
Fri Jul 31 03:43:22 PDT 2015

I'm slowly learning postgis ...
I have these 3 SELECTS that works well when run independently, but I'm now
trying to use them together in a WITH query, and I cannot make it run. What
I'm doing wrong?

positiu AS (
    SELECT rid, ST_Reclass(ST_Band(rast, 1), 1, '[1-10000]:1', '16BSI', 0)
    FROM rastertmp.prova
isok AS (SELECT t1.rid, ST_Union(ST_Intersection(t1.rast, 1, t2.rast, 1,
    FROM rastertmp.prova AS t1, positiu AS t2
    WHERE ST_Intersects(t1.rast, t2.rast)
        GROUP BY t1.rid
clips AS (
    SELECT p.ecoregion_cod, ST_Union(ST_Clip(r.rast,1, p.geom, true)) AS
    FROM gis_wd.wd_ecoregiones AS p, isok AS r
    WHERE ST_Intersects(r.rast, p.geom)
    GROUP BY ecoregion_cod
SELECT ecoregion_cod, (ST_SummaryStats(rast, 1)).* FROM clips

-- ERROR: column t2.rast does not exist

In fact, I'm assuming that running these SELECTS in a single WITH query is
more efficient (faster?) than running each one separately, generating
intermediate tables (less disk access?). But perhaps I'm wrong, any idea?


*CIDE, CSIC*  |  www.uv.es/jgpausas  |
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150731/32b91ee2/attachment.html>

More information about the postgis-users mailing list