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 SELECT * -- 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 SELECT g.gid, g.geom, g.row, g.col, 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 FROM public.grid_for_test_1km_vector g left jOIN public.raster rd on 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?