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;

• f.ashouri: Thank you Vladimir, but maybe my question was not clear enough. I'm not working on postgis raster. This is just a set of pixels of a raster file with row number, column number, pixel values and pixel centroid in 4326.
• Vladimir: The pixels have to have coordinates, otherwise it is impossible to extract values. Where are the coordinates of pixels stored?
• f.ashouri: the coordinates of centroid of pixels are available.
• Vladimir: What is their representation? As geometry PostGIS data type? WKT? WKB?
• f.ashouri: yes, geometry type WKT