I have a grid with a size of 2.24 x 2.24 km with 224 rows and columns making 10 m for each cell. Now I want to overlap this grid with my raster data which is 109 x 109 km and extract pixel values (all band values).

Currently, I have tried:

to create a grid

DROP TABLE IF EXISTS public.grid_for_test_1km_vector;
CREATE TABLE public.grid_for_test_1km_vector AS
--                    nrow, ncol, xsize (km), ysize (km), x0, y0
FROM ST_CreateFishnet(224, 224, 2.24, 2.24, 603819, 5361821) AS cells;
ALTER TABLE public.grid_for_test_1km_vector ADD COLUMN IF NOT EXISTS gid SERIAL PRIMARY KEY;
ALTER TABLE public.grid_for_test_1km_vector ALTER COLUMN geom TYPE geometry(Polygon,32632) USING ST_SetSRID(geom,32632);
CREATE INDEX grid_for_test_1km_id_vector ON public.grid_for_test_1km_vector USING gist (geom)

to get values that intersects

DROP TABLE IF EXISTS public.band_value_1_whole_km;
CREATE TABLE public.band_value_1_whole_km AS
    st_value(rd.rast, 1, g.centroid) as band_2,
    st_value(rd.rast, 2, g.centroid) as band_3,
    st_value(rd.rast, 3, g.centroid) as band_4,
    st_value(rd.rast, 4, g.centroid) as band_8,
    st_value(rd.rast, 5, g.centroid) as band_11, 
    st_value(rd.rast, 6, g.centroid) as ndvi, 
    st_value(rd.rast, 7, g.centroid) as ndbi
    public.grid_for_test_1km_vector g
left jOIN
    public.raster rd
    st_intersects(v.geometry , g.centroid);

and this is very slow. How can I speed up the query? What is a best approach to extract all band values from raster as much as fast?

  • Your question relates to the task of optimizing SQL script, technical capabilities of the PC and geodata structure and is complex, so for me it is a bit ambiguous... wait for help from others, but do not stop your own experiments on your test data... Jul 12 at 18:16


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.