PolarDB:GanosBase: Obtain the spatial extent of raster pixels

Import data

-- Create the GANOS_RASTER extension
CREATE EXTENSION GANOS_RASTER CASCADE;

-- Create the temperature table
CREATE TABLE temperature(id integer, rast raster);

-- Import the NetCDF temperature data and set the spatial reference to SRID 4326
INSERT INTO temperature
VALUES (1, ST_SetSrid(ST_ImportFrom('chunk_table', 'OSS://<id>:<key>@<endpoint>/bucket/path/file.nc'), 4326));

SELECT st_metadata(rast) FROM temperature WHERE id = 1;

-- Create the ganos_fdw extension
CREATE EXTENSION ganos_fdw;

-- Register the shapefile as a foreign table
SELECT ST_RegForeignTables('OSS://<id>:<key>@<endpoint>/bucket/path/file.shp');

-- Import data into the database and create a spatial index
CREATE TABLE devices AS SELECT * FROM <foreign_table_name>;
CREATE INDEX idx_devices_geom ON devices USING Gist(geom);

Extract temperature range

The following statement converts temperature pixels into polygons and merges those in the 27°C–28°C range (raw values 270–280) into a single geometry:

-- Merge pixels with temperatures between 27°C and 28°C
WITH tmp AS (
    SELECT (ST_PixelAsPolygons(rast)).*
    FROM temperature
    WHERE id = 1
)
SELECT ST_Union(geom)
FROM tmp
WHERE value >= 270 AND value < 280;

For high-resolution raster data, this statement may be slow because it processes every pixel. Use ST_Reclassify to mark out-of-range pixels as nodata before calling ST_PixelAsPolygons, which reduces the number of rows returned:

-- Step 1: Reclassify — pixels outside 270–280 become nodata (value 0)
WITH tmp AS (
    SELECT ST_Reclassify(
               rast,
               '[{"band":0,"remap":{"(-100,270,280, 1000]":"0,1,0"}, "nodata":false, "nodataValue":0}]',
               '{"chunktable":"reclass_chunk_table"}'
           ) AS rast
    FROM temperature
    WHERE id = 1
),

-- Step 2: Extract polygons only for the retained pixels
tmp2 AS (
    SELECT (ST_PixelAsPolygons(rast)).*
    FROM tmp
)
SELECT ST_Union(geom)
FROM tmp2;

For the full ST_Reclassify syntax, see ST_Reclassify.

Spatial overlay analysis

Run a spatial containment analysis to count devices located within the extracted temperature range:

WITH tmp AS (
    SELECT (ST_PixelAsPolygons(rast)).*
    FROM temperature WHERE id = 1
),
tmp2 AS (
    SELECT ST_Union(geom) AS geom
    FROM tmp
    WHERE value >= 270 AND value < 279
)
SELECT count(*)
FROM devices, tmp2
WHERE ST_Contains(tmp2.geom, devices.geom);

Expected result:

 count
-------
  1879