How to determine the intersection of a Raster and a point Shapefile? [closed]

Question

I am new to PostGIS and I can't find a way to intersect my raster map with my points shapefile.

The raster is the country and the shapefile is associated with the cities. I would like to determine the pixels positions of the cities in the raster map.

I read that ST_Intersects helps only with the intersection of two rasters.

Do you have any suggestions?

Answer 1

You can have ST_Intersects with raster and vector like this:

SELECT ST_AsGDALRaster(rast, 'GTiff') 
FROM your_raster_schema.your_raster_table 
WHERE ST_Intersects(ST_GeomFromGeoJSON({
      "type": "Feature",
      "geometry": {
        "type": "Point",
        "coordinates": [10, 10]
      },
      "properties": {
        "name": "your_location_name"
      }
    }),rast);

Change the coordinates based on your point. You might use ST_GeomFromWKB or ST_GeomFromText instead of ST_GeomFromGeoJSON