PolarDB:GanosBase: Obtain the spatial extent of raster pixels

Import data

Import temperature 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. Set spatial reference to 4326
INSERT INTO temperature
VALUES (1, ST_SetSrid(ST_ImportFrom('chunk_table', 'OSS://<id>:<key>@<endpoint>/bucket/path/file.nc'), 4326));

After the import completes, verify the metadata:

-- Query the metadata information
SELECT st_metadata(rast) FROM temperature WHERE id =1;

Import device data

-- Create the ganos_fdw extension
CREATE EXTENSION ganos_fdw;

-- Create the fdw table for Shapefile
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);

This example uses a foreign data wrapper (FDW) to import device data from OSS. For other import methods, see .

Extract the temperature range

The following query converts all pixels with temperatures between 27°C and 28°C into polygons, then merges them into a single geometry using ST_Union:

-- Merge the pixels with temperatures greater than 27°C and less than 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 < 279;

Performance optimization

For high-resolution raster data, ST_PixelAsPolygons processes every pixel — the more pixels, the slower the query. Use ST_Reclassify to mark out-of-range pixels as nodata before calling ST_PixelAsPolygons. This reduces the volume of data to be processed and significantly speeds up the query:

-- Perform reclassification
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 ),

-- Return spatial range
tmp2 AS (
  SELECT (ST_PixelAsPolygons(rast)).*
FROM tmp)
SELECT ST_Union(geom)
FROM tmp2;

Run spatial overlay analysis

Use ST_Contains to find devices that fall within the merged temperature polygon:

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(*) -- You can select any column. The count column is used as an example
FROM devices, tmp2
WHERE ST_Contains(tmp2.geom, devices.geom);

Sample result:

 count
-------
  1879