AnalyticDB:Raster model

Step 1: Create extensions

Load the three required extensions in this order:

CREATE EXTENSION ganos_spatialref;
CREATE EXTENSION ganos_geometry;
CREATE EXTENSION Ganos_Raster;

Step 2: Create a raster table

Specify raster_obj as both the raster column and the distribution column:

-- raster_obj is the distribution column, which controls data placement across nodes
CREATE TABLE raster_table(id integer, raster_obj raster)
DISTRIBUTED BY (raster_obj);

Step 3: Import raster data from OSS

Insert two raster objects using different storage modes:

INSERT INTO raster_table
VALUES
  -- In-DB: raster byte stream is stored in chunk_table inside the database
  (1, ST_ImportFrom('chunk_table', 'OSS://<id>:<key>@oss-cn.aliyuncs.com/mybucket/data/my_image.tif')),
  -- Out-DB: only a reference is stored; data remains in OSS
  (2, ST_CreateRast('OSS://<id>:<key>@oss-cn.aliyuncs.com/mybucket/data/my_image.tif'));

Step 4: Query raster object properties

Retrieve the height and width (in pixels) of a raster object:

SELECT ST_Height(raster_obj), ST_Width(raster_obj)
FROM raster_table
WHERE id = 1;

Expected output:

 st_height
-----------
      1241
(1 row)

Step 5: Build pyramids

Build a pyramid for each raster object. Use default parameters for the in-DB raster, and specify downsampling options for the out-DB raster:

-- Build pyramid with default parameters (in-DB raster, id=1)
DO $$
DECLARE
    rast raster;
BEGIN
    SELECT raster_obj INTO rast FROM raster_table WHERE id = 1;
    rast = ST_BuildPyramid(rast);
    UPDATE raster_table SET raster_obj = rast WHERE id = 1;
END;
$$ LANGUAGE 'plpgsql';

-- Build pyramid with explicit parameters (out-DB raster, id=2)
DO $$
DECLARE
    rast raster;
BEGIN
    SELECT raster_obj INTO rast FROM raster_table WHERE id = 2;
    rast = ST_BuildPyramid(rast, -1, 'Near', 'chunk_table');
    UPDATE raster_table SET raster_obj = rast WHERE id = 2;
END;
$$ LANGUAGE 'plpgsql';

Step 6: Select the optimal pyramid level

Given a viewport of 800×600 pixels and a geographic bounding box, ST_BestPyramidLevel returns the pyramid level that best matches the display resolution:

-- Returns the pyramid level that best fits an 800x600 viewport
-- over the geographic bounding box ((128.0, 30.0), (128.5, 30.5))
SELECT ST_BestPyramidLevel(rast, '((128.0, 30.0),(128.5, 30.5))', 800, 600)
FROM raster_table
WHERE id = 1;

Expected output:

 st_bestpyramidlevel
---------------------
                   3

Step 7: Clip a raster region

Retrieve the pixel matrix for a geographic region:

DO $$
DECLARE
    rast raster;
BEGIN
    SELECT raster_obj INTO rast FROM raster_table WHERE id = 1;
    rast = ST_BuildPyramid(rast);
    SELECT ST_Clip(rast, 0, '((128.980,30.0),(129.0,30.2))', 'World');
END;
$$ LANGUAGE 'plpgsql';

To get the pixel coordinate range of the clipped area rather than the pixel data itself, use ST_ClipDimension:

-- Returns the pixel bounding box of the clip region at pyramid level 2
SELECT ST_ClipDimension(raster_obj, 2, '((128.0, 30.0),(128.5, 30.5))')
FROM raster_table
WHERE id = 1;

Expected output:

 st_clipdimension
----------------------------------
 '((600, 720),(200, 300))'

Step 8: Drop extensions

When you no longer need the raster extensions, drop them in reverse order:

DROP EXTENSION Ganos_Raster;
DROP EXTENSION ganos_geometry;
DROP EXTENSION ganos_spatialref;