PolarDB:Serve dynamic raster tiles directly from the database with

Load the images

Create a table to store your raster data, then import each image from OSS using ST_ImportFrom.

CREATE TABLE landsat8 (
  id integer,
  rast raster
);

INSERT INTO landsat8 VALUES (1, ST_ImportFrom('chunk_table', 'OSS://<access_id>:<secret_key>@<endpoint>/<bucket>/path_to/file1.TIF'));
INSERT INTO landsat8 VALUES (2, ST_ImportFrom('chunk_table', 'OSS://<access_id>:<secret_key>@<endpoint>/<bucket>/path_to/file2.TIF'));
INSERT INTO landsat8 VALUES (3, ST_ImportFrom('chunk_table', 'OSS://<access_id>:<secret_key>@<endpoint>/<bucket>/path_to/file3.TIF'));
INSERT INTO landsat8 VALUES (4, ST_ImportFrom('chunk_table', 'OSS://<access_id>:<secret_key>@<endpoint>/<bucket>/path_to/file4.TIF'));

Replace <access_id>, <secret_key>, <endpoint>, and <bucket> with your actual OSS credentials and path. Do not include angle brackets in the final connection string.

To verify the import, query the raster metadata:

SELECT ST_Name(rast), ST_Width(rast), ST_Height(rast), ST_SRID(rast), ST_NumBands(rast)
FROM landsat8;

Mosaic and build pyramids

With four separate images loaded, mosaic them into a single virtual raster object (id 101) and enable color balancing for a seamless visual result:

-- Mosaic all four images with color balancing
INSERT INTO landsat8 VALUES (101, ST_MosaicFrom(
  Array(SELECT rast FROM landsat8 WHERE id < 5),
  'rbt_mosaic',
  '',
  '{"srid":4326, "cell_size":[0.0002,0.0002], "nodata":true, "nodatavalue":0, "color_balance":true}'
));

Then build pyramids on the mosaicked raster. Pyramids are downsampled overviews that accelerate rendering at different zoom levels:

-- Build pyramids for the mosaicked raster (id=101)
UPDATE landsat8 SET rast = ST_BuildPyramid(rast) WHERE id = 101;

After color balancing, the exported raster looks like this:

Example 1: Standard 3-band image

For a standard RGB image with no special stretching:

SELECT ST_AsTile(
  rast,
  ST_TileEnvelope(tile_zoom, tile_column, tile_row),
  '{"strength":"none", "format":"PNG", "alpha":true}'
)
FROM landsat8 WHERE id = 101;

Example 2: Multi-band image with contrast stretch

For multi-band imagery like Landsat, select specific bands and apply a percentage stretch to improve the visual output:

SELECT ST_AsTile(
  rast,
  ST_TileEnvelope(tile_zoom, tile_column, tile_row),
  '{"strength":"ratio", "format":"PNG", "bands":"0,1,2", "alpha":true}'
)
FROM landsat8 WHERE id = 101;

Here, bands 0, 1, and 2 map to R, G, and B channels, and the ratio method applies a percentage-based contrast stretch. The tile_zoom, tile_column, and tile_row values correspond directly to the {z}, {x}, and {y} path parameters in the HTTP tile URL served in Step 3.

Install dependencies

pip install asyncpg quart

Create app.py

The service exposes two routes:

• / — serves the HTML map page and calculates the initial map bounds from the raster extent.

• /raster/{z}/{x}/{y} — accepts a tile request, runs ST_AsTile for the given Z/X/Y coordinates, and returns the PNG tile.

from quart import Quart, send_file, render_template
import asyncpg
import io
import re

# Database connection parameters
CONNECTION = {
    "host": "<database-endpoint>",
    "port": "<port>",
    "user": "<username>",
    "password": "<password>",
    "database": "<database-name>"
}

TABLE_NAME = "landsat8"    # Target table
RASTER_COLUMN = "rast"     # Raster column name
RASTER_ID = "101"          # ID of the mosaicked raster

app = Quart(__name__, template_folder='./')


@app.before_serving
async def create_db_pool():
    app.db_pool = await asyncpg.create_pool(**CONNECTION)


@app.after_serving
async def close_db_pool():
    await app.db_pool.close()


@app.route("/")
async def home():
    sql = f'''
    SELECT ST_Extent(
      ST_Transform(
        ST_Envelope({RASTER_COLUMN}), 4326))
    FROM {TABLE_NAME}
    WHERE ID = {RASTER_ID};
    '''
    async with app.db_pool.acquire() as connection:
        box = await connection.fetchval(sql)
        box = re.findall(r'BOX\((.*?) (.*?),(.*?) (.*?)\)', box)[0]
        min_x, min_y, max_x, max_y = list(map(float, box))
        bounds = [[min_x, min_y], [max_x, max_y]]
        center = [(min_x + max_x) / 2, (min_y + max_y) / 2]
        return await render_template('./index.jinja2', center=str(center), bounds=str(bounds))


@app.route("/raster/<int:z>/<int:x>/<int:y>")
async def raster(z, x, y):
    # Select bands 0,1,2 as RGB with percentage-based contrast stretch
    config = '{"strength":"ratio","bands":"0,1,2","alpha":true}'
    sql = f'''
    SELECT (
      ST_AsTile({RASTER_COLUMN},
        ST_Transform(
          ST_TileEnvelope($1, $2, $3),
          ST_Srid({RASTER_COLUMN})),
        \'{config}\')
      ).data AS tile
    FROM {TABLE_NAME}
    WHERE ID = {RASTER_ID};'''
    async with app.db_pool.acquire() as connection:
        tile = await connection.fetchval(sql, z, x, y)
        return await send_file(io.BytesIO(tile), mimetype='image/png')


if __name__ == "__main__":
    app.run(port=5500)  # Change the port if needed

Replace the CONNECTION values with your PolarDB endpoint, port, credentials, and database name.