# How to extract pixel values from raster?

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
SELECT
g.gid,
g.geom,
g.row,
g.col,
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
FROM
public.grid_for_test_1km_vector g
left jOIN
public.t32ulc_entire_tile rd
on
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
{"ft_lang_label":"__label__en","ft_lang_prob":0.77765924,"math_prob":0.93876714,"size":1067,"snap":"2022-27-2022-33","text_gpt3_token_len":326,"char_repetition_ratio":0.18626529,"word_repetition_ratio":0.0,"special_character_ratio":0.28209934,"punctuation_ratio":0.2603306,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9703274,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2022-08-19T23:37:01Z\",\"WARC-Record-ID\":\"<urn:uuid:dc8d9150-b1ba-4854-bc9e-245f60c7db01>\",\"Content-Length\":\"216620\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:e4558ed6-4179-401e-9566-8d97f6c07fa3>\",\"WARC-Concurrent-To\":\"<urn:uuid:c23881d0-95be-4e5f-95f7-4db029becdfc>\",\"WARC-IP-Address\":\"151.101.1.69\",\"WARC-Target-URI\":\"https://gis.stackexchange.com/questions/435500/how-to-extract-pixel-values-from-raster\",\"WARC-Payload-Digest\":\"sha1:4BBZV2M3ML2UI3NM2UGM6C46SJW6SNL5\",\"WARC-Block-Digest\":\"sha1:7HET7QFU6OOEYGYYSAVB3S5KYT2CR3SU\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2022/CC-MAIN-2022-33/CC-MAIN-2022-33_segments_1659882573849.97_warc_CC-MAIN-20220819222115-20220820012115-00621.warc.gz\"}"}