I have created a grid using fishnet which has cells that are equal to 10 m (with and height) and the grid size is 2.24 x 2.24 km. Using this grid I would like to extract all band values from the raster. How can I speed up the extraction?

Currently, I tried:

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

grid_for_test_1km_vector is grid table that contains polygons, while t32ulc_entire_tile has raster data that has the area which intersects with the grid polygons. This query executes very slowly...

  • The query is probably long because the join is made without an index. Check that there is a spatial index on the geom and rast columns.
    – Atm
    Jul 12 at 10:26
  • @Atm how can I check? I have created an index with gist for the grid_for_test_1km_vector table, while raster data has rid column by default. Jul 12 at 10:32
  • @Atm I have checked, and they both have spatial indexes: Jul 12 at 10:42


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.