PolarDB:Use GanosBase to obtain the spatial range of raster pixels

New functions

GanosBase 4.3 introduces a set of functions for transforming raster pixels into spatial geometry objects and numeric values.

Best practice

Given the meteorology and temperature data (raster) and spatial distribution of devices (vector) in China, you can leverage the unified analysis capabilities of GanosBase for both vector and raster data to identify devices that detect temperature within a specified range. You can also identify devices that are located within a specified elevation range based on the digital elevation model (DEM) data. The following types of data are used in the example:

• The temperature data of China in a NetCDF raster file.

• The device location information of China in shapefile format.

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 data is successfully imported, you can query the metadata information by executing the following SQL statement:

-- 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);

Extract temperature range

This case mainly demonstrates how to convert the raster-based temperature data into a vector spatial range for analysis and computing. The following SQL statement is executed to merge spatial ranges of the pixels that meet the temperature conditions to determine the overall spatial range.

-- 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 < 280;

However, the performance of this SQL statement may be low when processing high-resolution raster data because all pixels are processed. To enhance performance, the ST_Reclassify function is used to process invalid data as nodata values, which reduces the volume of data to be processed. For more information about the syntax, see ST_Reclassify.

-- 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;

Spatial overlay analysis

Perform a spatial containment analysis between the returned vector spatial range and the device table to identify devices that meet the specified conditions:

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

Conclusion

GanosBase provides the storage, computing, and analysis capabilities for spatio-temporal raster data to simplify complex vector-raster multimodal analysis into a few GeoSQL statements. Spatiotemporal data can be processed in databases instead of traditional GIS software. This simplifies program logic, reduces development complexity and maintenance costs, and allows industry professionals with advanced GIS capabilities on the cloud.