Testes_PostGIS_Raster

testes_postgis_raster.sql

-- Abordagem tradicional (vetor/vetor):
SELECT foo.value, ST_area(foo.geom::geography)/10000 AS area_ha
 FROM ( SELECT c.value,
               ST_Union(ST_Intersection(f.geom, c.geom)) as geom
        FROM fazendas f, capacidade_vect c
        WHERE  ST_Intersects(f.geom, c.geom)
        AND f.gid = 6
        GROUP BY c.value
       ) as foo
ORDER BY foo.value;

-- PostGIS Raster (raster/vetor):
SELECT tmp.pixel_value, ST_area(Geography(ST_Union(ST_Intersection(f.geom, tmp.geom))))/10000 AS area_ha
FROM fazendas f,(
		 SELECT (ST_DumpAsPolygons(ST_Clip(c.rast, ST_Buffer(f.geom, 0.01), TRUE))).val AS pixel_value,
		        (ST_DumpAsPolygons(ST_Clip(c.rast, ST_Buffer(f.geom, 0.01), TRUE))).geom AS geom
		 FROM fazendas f, capacidade_rast c
		 WHERE  ST_Intersects(ST_Buffer(f.geom, 0.01), c.rast)
		 AND f.gid = 6
		) AS tmp
WHERE  ST_Intersects(f.geom, tmp.geom)
GROUP BY tmp.pixel_value
ORDER BY tmp.pixel_value;


/*
conversão: raster2pgsql -s 4326 -I -C -M capacidade_agricola.tif -F -t 90x90 capacidade_rast > capacidade_rast.sql

campos:
value = classe de capacidade na tabela vetorial
pixel_value = classe de capacidade na tabela raster

*/




-- outros testes
CREATE TABLE vector_srtm AS
SELECT  row_number() OVER (ORDER BY (gv).val) AS gid, ST_Union((gv).geom) AS geom, (gv).val
FROM ( 
	SELECT rid, ST_Intersection(rast, geom) AS gv
	FROM srtm, fazendas
	WHERE ST_Intersects(rast, geom) AND fazendas.gid =1
     ) AS tmp
GROUP BY (gv).val
ORDER BY (gv).val;

----------------------

CREATE TABLE vector_srtm_inter AS
SELECT rid as gid, (gv).geom AS geom, (gv).val
FROM ( 
	SELECT rid, ST_Intersection(rast, geom) AS gv
	FROM srtm, fazendas
	WHERE ST_Intersects(rast, geom) AND fazendas.gid =1
     ) inter;