PolarDB:Best practices for importing vector and raster data to GanosGanosBase

Import using QGIS

PolarDB for PostgreSQL is fully compatible with PostgreSQL Community Edition, so any GIS desktop tool that supports PostgreSQL connections works with GanosBase. QGIS is a common choice.

Create a PostgreSQL data source connection in QGIS pointing to your PolarDB cluster, then use the tool's built-in data import capability to load vector data.

Import using FDW (recommended)

Foreign Data Wrapper (FDW) is a PostgreSQL extension for accessing external data, including files in OSS and tables in other databases within the same cluster. GanosBase FDW automatically maps geometric spatial data types to the Geometry type, letting you query foreign tables and local cluster tables in a unified way.

The following example imports poly.shp from OSS into GanosBase.

1. Install the required extensions.

   CREATE EXTENSION ganos_spatialref;
   CREATE EXTENSION ganos_geometry;
   CREATE EXTENSION ganos_fdw;

2. Register poly.shp as a foreign table using ST_RegForeignTables.

   SELECT ST_RegForeignTables('oss://<access_id>:<secret_key>@[<Endpoint>]/<bucket>/path_to/poly.shp');

3. Verify the registration by querying the information_schema.foreign_tables view.

   SELECT foreign_table_name FROM information_schema.foreign_tables
     ORDER BY foreign_table_name ASC;

The steps above register poly.shp as a foreign table and let you query it through data mapping. No data is written to the database. To copy the data into a local table, run:

CREATE TABLE poly_db AS SELECT * FROM poly;

Import TIFF data

TIFF is a widely used raster format. The following example loads a TIFF file from OSS.

1. Create a table with a raster column to store the data.

   CREATE TABLE raster_table
   (
     id     integer,
     format text,
     rast   raster
   );

2. Import the TIFF file using ST_ImportFrom or ST_CreateRast.

   -- ST_ImportFrom: copies pixel data into the database as chunks named 'chunk_table'
   INSERT INTO raster_table
   SELECT 1, 'TIFF', ST_ImportFrom('chunk_table', 'oss://<access_id>:<secret_key>@[<Endpoint>]/<bucket>/path_to/file');
   
   -- ST_CreateRast: stores only metadata; pixel data stays in OSS
   INSERT INTO raster_table
   SELECT 2, 'TIFF', ST_CreateRast('oss://<access_id>:<secret_key>@[<Endpoint>]/<bucket>/path_to/file');

3. Query the imported raster objects using GanosBase raster UDFs (user-defined functions).

   SELECT
     id,
     ST_Georeference(rast),
     st_extent(rast),
     ST_NumBands(rast),
     ST_SRID(rast),
     st_value(rast, 0, 100, 100),
     st_value(rast, 1, 200, 200)
   FROM raster_table;

   Sample result:

   

Import HDF5 and NetCDF data

HDF5 and NetCDF are standard raster formats used in earth observation, scientific computing, and geoscience (meteorology and oceanography). Both ST_ImportFrom and ST_CreateRast support them with the same syntax as TIFF.

-- Import a NetCDF file using ST_ImportFrom
INSERT INTO raster_table
SELECT 3, 'NC', ST_ImportFrom('chunk_table', 'oss://<access_id>:<secret_key>@[<Endpoint>]/<bucket>/path_to/file');

-- Import an HDF5 file using ST_CreateRast
INSERT INTO raster_table
SELECT 4, 'NC', ST_CreateRast('oss://<access_id>:<secret_key>@[<Endpoint>]/<bucket>/path_to/file');

Files with subdatasets

HDF5 and NetCDF files often contain multiple subdatasets. Append the subdataset name to the file path when importing:

• NetCDF: 'oss://...file.nc:sub_name'

• HDF5: 'oss://...file.hdf5://path/sub_name'

-- NetCDF with a subdataset
INSERT INTO raster_table
SELECT 5, 'NC', ST_ImportFrom('chunk_table', 'oss://<access_id>:<secret_key>@[<Endpoint>]/<bucket>/path_to/file.nc:sub_name');

-- HDF5 with a subdataset
INSERT INTO raster_table
SELECT 5, 'HDF5', ST_ImportFrom('chunk_table', 'oss://<access_id>:<secret_key>@[<Endpoint>]/<bucket>/path_to/file.hdf5://path/sub_name');

Files with more than three dimensions

GanosBase stores raster data in (w, h, b) dimensions, where w is chunk width, h is chunk height, and b is the number of bands. For HDF5 and NetCDF files with more than three dimensions, calculate b as the product of all dimension sizes except the longitude and latitude axes, then pass it in the chunkdim parameter.

For example, the following NetCDF file has four dimensions: lon, lat, time, and isobaric.

Excluding lon and lat, the product of the remaining dimension sizes is 120. Pass that value as the band count in chunkdim:

INSERT INTO nc_table
VALUES (1, ST_ImportFrom('nc_rbt', '/Users/xiaofei/Data/raster/nc_demo.nc', '{"chunkdim":"(256,256,120)"}'));