[draft] Một số truy vấn Raster trong PostGIS

Lưu nháp

Một số truy vấn raster trong PostGIS

–intersects with polygon
CREATE TABLE public.intersects AS
SELECT a.rast, b.*
FROM public.t1 AS a, public.polygon AS b
WHERE ST_Intersects(a.rast, b.geom);

–intersects with point
CREATE TABLE public.intersects_point AS
SELECT a.rast, b.*
FROM public.t1 AS a, public.point AS b
WHERE ST_Intersects(a.rast, b.geom);

–get value of pixcels
SELECT b.*,st_value(a.rast,(ST_Dump(b.geom)).geom)
FROM
t1 as a, point AS b
WHERE ST_Intersects(a.rast,b.geom)
ORDER BY b.id;

–stats
SELECT st_summarystats(a.rast) AS stats
FROM t1 AS a, point AS b
WHERE ST_Intersects(a.rast,b.geom)


SELECT st_summarystats(ST_Union(a.rast))
FROM t1 AS a, point AS b
WHERE ST_Intersects(a.rast,b.geom)


WITH t AS (
SELECT st_summarystats(ST_Union(a.rast)) AS stats
FROM t1 AS a, point AS b
WHERE ST_Intersects(a.rast,b.geom)
)
SELECT (stats).min,(stats).max,(stats).mean FROM t;

-soiqualang_chentreu-