[postgis-users] WITH ...

[juli g. pausas]

Hi
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?

WITH
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,
'BAND1'))
    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
rast
    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?

Thanks

Juli
--
*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>