extracting pixel value from a table in postgresql

Question

I have a table in postgresql that contains ID, row number, column number, pixel value and centroid of the pixel in WGS84 lat-lon system(4326). How could I extract the pixel value by giving a point in 4326 reference system?

Answer 1

SELECT pixel_value FROM raster_table
WHERE ST_Intersects(ST_SetSRID(ST_Point(xcoord,ycoord), 4326),rast)=TRUE;

Then according to your new comments the SQL-command could be

SELECT pixel_value FROM your_table_with_pixels
WHERE ST_Intersects(ST_SetSRID(ST_Point(xcoord,ycoord), 4326),ST_Buffer(geom, resolution_of_raster_cell))=TRUE;