This topic describes how to use the raster engine of GanosBase (known as GanosBase Raster) in water conservancy and meteorology analysis. GanosBase integrates storage, retrieval, and analysis of image and grid data within the database, enabling the analysis and processing of massive raster data in areas such as meteorology, water conservancy, resource management, emergency response, and media.
About GanosBase Raster
Raster data
Raster data divides real-world space into grids. Each grid is called a cell or pixel and contains properties representing the characteristics of an entity.
Raster data is typically classified into two categories:
Thematic data: The value of each raster pixel is a measurement or classification to describe the information such as digital elevation model (DEM) data, pollutant concentration, signal strength, precipitation, land ownership, and vegetation types.
Image data (also known as the remote sensing image): Images are captured by ground, aerial, or satellite remote sensing platforms. These images reflect the electromagnetic characteristics of various objects and include both aerial and satellite images.
A raster is also called a spatio-temporal raster because raster data contains both spatial and temporal attributes. With temporal attributes, raster data can be used to manage time series.
What is GanosBase Raster
GanosBase Raster is a spatio-temporal engine extension provided by PolarDB for PostgreSQL (Compatible with Oracle). This extension allows the database to efficiently store, manage, and process raster data types. It supports the integration and analysis of raster data from various sources, including remote sensing, photogrammetry, and thematic mapping. It also offers a GeoServer extension for publishing raster objects as a standard Open Geospatial Consortium (OGC) service, such as WMS or WMTS. GanosBase Raster can be used in a wide range of sectors, including meteorology, environmental monitoring, geological exploration, natural resource management, national defense, emergency response, telecommunications, media, transportation, urban planning, and homeland security.
Model overview
The GanosBase raster data model consists of the following elements:
Raster: a raster dataset, such as a remote sensing image or a TIFF file.
Tile: a block of pixels. A tile is the basic storage unit of a raster object. Each tile has 256 x 256 pixels by default.
Band: a single 2D layer in a raster dataset. Each band comprises multiple tiles, with each tile having its own coordinate numbers.
Cell: a pixel in a tile. Cells can be of different data types, such as byte, short, int, or double.
Pyramid: a series of reduced-resolution versions of a raster, used to speed up the display of raster data. A pyramid has different levels, with each level corresponding to a layer. Level 0 refers to the raw data.
Metadata: raster metadata, including the spatial extent, projection type, and pixel type.
As shown in the preceding figure, GanosBase Raster uses a simple but efficient raster model to manage thematic data and remote sensing imagery data. In the database system, a raster image is stored as a raster object. A raster object is logically divided into multiple bands, each holding the raw image data retrieved during the import process. A raster object is stored and managed by tiles. The size of a tile defaults to 256 x 256 pixels, but it can be changed as required. Each tile contains one or more bands. A tile consists of cells that represent pixels. Each raster object has its own metadata, such as the spatial extent, data type, projection information, and coordinate numbers. If a raster is organized in a pyramid structure, each band will have its own pyramid.
Advantages
GanosBase Raster outperforms PostGIS Raster in the following aspects: business adaptation, storage savings, and calculation capabilities.
Better business adaptation
While PostGIS Raster fully rasterizes data, GanosBase Raster offers an object-oriented storage structure. Each raster, whether an image or a DEM file, is stored as a single database record, forming a one-to-one mapping. Each of these records can contain a raster up to 1 TB. In addition, GanosBase does not allow for direct operations on tiles, ensuring the integrity of metadata. In addition, it is highly correlated with time series data. In a word, GanosBase Raster enables you to better integrate raster data into your business processes.
Greater storage savings
GanosBase Raster adopts an architecture that preserves raster metadata in the database and raster attribute data on low-cost OSS (Object Storage Service). This is beneficial for use cases which require large storage capacity, such as the analysis of massive numbers of images. This way, you can still perform spatial analysis of raster with significantly lower storage costs.
More spatial-temporal operators
In addition to assessing spatial relationships, processing images, and performing standard operations on pyramids, pixel values, and attributes, GanosBase Raster provides a variety of unique statistical and algebraic operations, as well as specialized image color balancing algorithms and accelerated rendering for overview maps, even when handling large-scale raster datasets.
Areal precipitation analysis
Scenario Description
Areal precipitation, a key indicator in flood forecasting, reflects the average precipitation per unit area within a region. It provides critical data for flood control, reservoir regulation, disaster prevention, and economic development. In most cases, relevant departments collect monitoring data from isolated observation sites, making it necessary to establish a computing link to perform areal precipitation analysis of an entire region for decision making purposes.
Requirements
In this scenario, spatial interpolation of discrete monitoring points is required to construct grid data. Based on the grid data, contour and surface tracing is conducted. In the meantime, relevant statistical information within the extent covered by vector surfaces is calculated to determine the overall areal precipitation.
Data Import: imports the vector data of observation points into the database using the FDW extension.
Data Interpolation: uses the ST_InterpolateRaster function to convert vector observation points into a grid of a specified size, which is used to store GanosBase Raster data.
Contour and surface: uses the ST_Contour function to trace contours or surfaces based on the grid data for analyzing overall precipitation distribution.
Spatial statistics: uses the ST_Statistics function to categorize precipitation distribution across the grid and determine the maximum, minimum, median, and total precipitation of each category.
Areal precipitation calculation: calculates the grid within any vector boundary and constructs statistical rules for penetrated grids to obtain the overall precipitation data for the vector area.
Implementation
Install the GanosBase extension
Install the ganos_raster and ganos_fdw extensions in the target database.
CREATE EXTENSION ganos_raster CASCADE;
CREATE EXTENSION ganos_fdw CASCADE;Import data
Prepare a point layer file named point.shp and a polygon layer file named polygon.shp, both in shapefile format. Each point contains an ID attribute (string) and a precipitation attribute (floating-point). Display the layers overlaid in QGIS as shown in the following example. Each number indicates the precipitation value of each point:
Map the shapefiles as external tables from OSS to the database using the GanosBase FDW. Then, create tables and insert data by executing the CREATE statements. For more information, see FDW. See the following sample SQL statements:
-- ak and ak_secret are the AccessKeyId and AccessKeySecret of the OSS service. CREATE SERVER myserver FOREIGN DATA WRAPPER ganos_fdw OPTIONS ( datasource 'OSS://<access_id>:<secrect_key>@<Endpoint>/<bucket>/path_to/file', format 'ESRI Shapefile' ); CREATE USER MAPPING FOR CURRENT_USER SERVER myserver OPTIONS (user '<ak>', password '<ak_secret>'); -- Map shapefiles as external tables CREATE FOREIGN TABLE foreign_point_table ( fid integer, id varchar, geom geometry, pre double precision -- precipitation) SERVER myserver OPTIONS ( layer 'point' ); CREATE FOREIGN TABLE foreign_polygon_table ( fid integer, id varchar, geom geometry) SERVER myserver OPTIONS ( layer 'polygon' ); -- Create tables and insert data CREATE TABLE point_table AS SELECT fid, geom, pre FROM foreign_point_table; CREATE TABLE polygon_table AS SELECT fid, geom FROM foreign_polygon_table;After the data is imported via external tables, you can perform various SQL queries, such as querying all records from point_table:
SELECT fid, ST_AsText(geom),pre FROM point_table;Sample result:
fid | st_astext | pre -----+-----------------------------+------ 0 | POINT(119.1084 28.50302) | 5 1 | POINT(118.768925 28.475747) | 3.5 2 | POINT(119.30954 28.773729) | 2.5 3 | POINT(119.039694 28.363413) | 6 4 | POINT(119.035561 28.614094) | 4 5 | POINT(119.9517 28.77) | 0.5 6 | POINT(120.35833 28.62694) | 1 7 | POINT(119.908078 28.439481) | 1.5 8 | POINT(119.472 28.5933) | 4.5 9 | POINT(119.400282 28.398895) | 4 10 | POINT(119.783954 28.271403) | 0 11 | POINT(119.663102 28.514025) | 3 12 | POINT(120.343889 28.9031) | 0 13 | POINT(119.425841 27.768324) | 8.5 14 | POINT(119.426237 28.015453) | 6.5 15 | POINT(119.677214 27.944789) | 4.5 16 | POINT(119.078999 28.045044) | 7 17 | POINT(120.328711 28.411951) | 1.5 18 | POINT(120.071226 28.412596) | 1 19 | POINT(120.336292 27.986628) | 2 20 | POINT(119.174307 27.635898) | 17 21 | POINT(119.106197 27.776089) | 13.5 22 | POINT(119.316833 28.191166) | 4 23 | POINT(119.554612 28.149524) | 3.5 24 | POINT(119.573099 28.295702) | 2.5 25 | POINT(119.154161 28.237417) | 5 26 | POINT(120.391447 28.211369) | 4 27 | POINT(119.979144 28.568005) | 1 28 | POINT(119.468737 27.606996) | 10.5 29 | POINT(119.881658 28.03036) | 1.5 30 | POINT(120.068508 28.165111) | 1.5 31 | POINT(119.765358 27.821914) | 2 32 | POINT(118.892389 27.733222) | 14 33 | POINT(118.75833 28.00694) | 4.5 34 | POINT(118.91 27.51222) | 11.5 35 | POINT(119.248086 27.435982) | 14.5 (36 rows)
Perform spatial interpolation
GanosBase provides the spatial interpolation function ST_InterpolateRaster, which enables the conversion of points into Raster objects through interpolation. This function supports parallel processing to improve execution performance by setting the degree of parallelism.
Create a table with a Raster field to store interpolated raster data.
CREATE TABLE IF NOT EXISTS raster_table ( id integer, rast raster -- raster data type for storing interpolated raster objects );Use the ST_InterpolateRaster function to interpolate spatial data from point_table using the Inverse Distance Weighting (IDW) method, and then insert the results into raster_table.
NoteIn the example, ST_MakePoint is used to generate a point object that contains attributes such as precipitation as the input parameter for interpolation.
INSERT INTO raster_table(id, rast) VALUES( 1, (SELECT ST_InterpolateRaster( ST_Collect(ST_MakePoint(ST_X(geom),ST_Y(geom),pre)), 512, 512, '{"method":"IDW","radius":2.0,"max_points":"30","min_points":"1"}', '{"chunktable":"rbt","celltype":"32bf"}') FROM point_table));When processing a large amount of points, enable parallel processing before executing the interpolation function to improve performance.
-- Set the degree of parallelism to 4 SET ganos.parallel.degree = 4;Use the ST_ExportTo function to export the generated raster object into a file for viewing purposes. In this case, the file is exported in COG format to OSS.
SELECT ST_ExportTo(rast, 'COG', 'OSS://<access_id>:<secrect_key>@<Endpoint>/<bucket>/path_to/file.tif') FROM raster_table WHERE id=1;Download the file and open it in QGIS to view the result:
Use the ST_ClipToRast function to clip the interpolated raster object with the polygon object (representing the administrative division) from polygon_table. This process creates a new raster object and inserts the object into raster_table.
INSERT INTO raster_table SELECT 2,ST_ClipToRast(r.rast, p.geom, 0, '', NULL, '', '{"chunktable":"rbt"}') FROM raster_table AS R, polygon_table AS p;Execute the following statements to export the clipped raster data to a file named idw_clip.tif:
SELECT ST_ExportTo(rast, 'COG', 'OSS://<access_id>:<secrect_key>@<Endpoint>/<bucket>/path_to/file.tif') FROM raster_table WHERE id=2;
Contour and surface tracking
After the raster object is generated through interpolation, you can use the raster processing functions provided by GanosBase for data analysis and processing. The following example shows how to perform contour and surface tracking.
Generate contours
GanosBase provides the ST_Contour function to create contours and surfaces. Use the following SQL statement to produce contours at a regular interval of 1.0 and overlay them with the administrative division polygon. The outcome is stored in the rs_contours table.
CREATE TABLE rs_contours AS
SELECT id, max_value, min_value, ST_Intersection(a.geom,p.geom) FROM
(SELECT (ST_Contour(rast,1,'{"interval":"1.0"}')).*
FROM raster_table WHERE id=1) a, polygon_table AS p;Import rs_contours, the created contour table, into QGIS for an overlay display. In the following resulting image, green numbers are the measurements of observation sites, and red numbers are the contour values.
Generate surfaces
Add the polygonize attribute to the ST_Contour function and set it to true to return surfaces:
CREATE TABLE rs_contours_polygon AS
SELECT id, max_value, min_value, ST_Intersection(a.geom,p.geom) FROM
(SELECT (ST_Contour(rast,1,'{"interval":"1.0","polygonize":"true"}')).*
FROM raster_table WHERE id=1) a, polygon_table AS p;The ST_Contour function outputs surfaces of the polygon type. See the following sample image of the surfaces overlaid and colored in QGIS:
Precipitation analysis
This section illustrates the process of calculating the areal precipitation for a given polygon using the interpolated raster object. Areal precipitation refers to the ratio of the total amount of precipitation within a specific area to the area of that region, usually expressed in millimeters per square meter (mm/m²). Therefore, the area of the polygon covered by each pixel is first calculated. Then, the pixel value is multiplied by the corresponding area and summed up to obtain the total precipitation. Finally, the total precipitation is divided by the area of the polygon to calculate the areal precipitation.
Collect statistics of the precipitation in an area. GanosBase provides the ST_Statistics function for collecting raster data statistics. This function supports raster pixel values within any area (points, lines, and areas). It allows the output of any polygonal area and specifies a pixel value statistical range to perform interval-based statistics on the interpolated raster data. In this example, the statistical range is
(0,5,10,15,20]:SELECT (ST_Statistics(rast, ST_GeomFromText('POLYGON((119.0969 28.0519, 118.9058 27.8942, 119.0502 27.5649, 119.3347 27.6292, 119.4262 27.8775, 119.4927 28.1823, 119.3812 28.1186, 119.0969 28.0519))'), 0, '(0,5,10,15,20]',false)).* FROM raster_table WHERE id=1;Sample result:
name | band | min | max | mean | sum | count | std | median | mode ---------+------+--------------------+--------------------+--------------------+--------------------+-------+--------------------+--------------------+-------------------- full | 0 | 2.5009584426879883 | 16.841625213623047 | 6.618387373747887 | 243748.58858776093 | 36829 | 2.6857099819905033 | 6.045845985412598 | 3.892089605331421 (0-5] | 0 | 2.5009584426879883 | 4.999907970428467 | 4.144016098134749 | 50963.109974861145 | 12298 | 0.5534420165252167 | 4.231814861297607 | 3.892089605331421 (5-10] | 0 | 5.000039577484131 | 9.999935150146484 | 6.852788502446667 | 136110.0852355957 | 19862 | 1.2932534814382863 | 6.623260498046875 | 5.028009414672852 (10-15] | 0 | 10.00007438659668 | 14.993864059448242 | 11.947531793007881 | 52999.25103378296 | 4436 | 1.2279701295194279 | 11.900737762451172 | 12.430845260620117 (15-20] | 0 | 15.00446891784668 | 16.841625213623047 | 15.777434950734413 | 3676.142343521118 | 233 | 0.5092360295014834 | 15.7352294921875 | 15.00446891784668 (5 rows)From the output statistics, detailed statistical data within the specified polygonal area can be obtained. For example, the maximum precipitation in this area is 16.84 mm, the minimum is 2.5 mm, and there are 4436 pixels with precipitation amounts greater than 10 mm but less than or equal to 15 mm.
Use the ST_ClipToRast function to clip the raster object, generate a new raster object, and insert the object into the raster_table table.
INSERT INTO raster_table SELECT 3, ST_ClipToRast(rast, ST_GeomFromText('POLYGON((119.0969 28.0519, 118.9058 27.8942, 119.0502 27.5649, 119.3347 27.6292, 119.4262 27.8775, 119.4927 28.1823, 119.3812 28.1186, 119.0969 28.0519))'), 0, '', NULL, '', '{"chunktable":"rbt"}') FROM raster_table WHERE id=1;To obtain the most accurate areal precipitation statistics, traverse each pixel of the raster data to determine the rectangular feature of the area covered by the pixel and its corresponding pixel value. The ST_Width and ST_Height functions are used to obtain the dimensions of the clipped raster object. See the following SQL statement:
SELECT ST_Width(rast), ST_Height(rast) FROM raster_table WHERE id=3;Sample result:
st_width | st_height ----------+----------- 185 | 217Get the area and precipitation for each pixel. Use the ST_PixelAsPolygon and ST_Value functions to obtain the polygon area covered by each pixel and its corresponding pixel value, and store the results to a new table. See the following SQL statement:
-- Create a new table CREATE TABLE pixel_pre ( row integer, col integer, geom geometry, -- pixel area pre double precision -- pixel value ); UPDATE raster_table SET rast=ST_SetSrid(rast, 4326); -- Traverse each pixel of the clipped raster data to obtain its covered rectangular area and corresponding pixel value DO $do$ BEGIN FOR i IN 0..184 LOOP FOR j IN 0..216 LOOP INSERT INTO pixel_pre SELECT i,j, ST_PixelAsPolygon(rast,j,i),ST_Value(rast,0,i,j) as value FROM raster_table where id=3; END LOOP; END LOOP; END $do$;Query the new table. The coordinates, covered rectangular area, and value of each pixel are returned.
SELECT ROW,col,ST_AsText(geom),pre FROM pixel_pre LIMIT 10;Sample result:
row | col | st_astext | pre -----+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------- 107 | 33 | POLYGON((119.244752593338 28.0907410103828,119.244752593338 28.0878755431622,119.2479422912 28.0878755431622,119.2479422912 28.0907410103828,119.244752593338 28.0907410103828)) | 5.66606330871582 107 | 34 | POLYGON((119.244752593338 28.0878755431622,119.244752593338 28.0850100759417,119.2479422912 28.0850100759417,119.2479422912 28.0878755431622,119.244752593338 28.0878755431622)) | 5.698885917663574 107 | 35 | POLYGON((119.244752593338 28.0850100759417,119.244752593338 28.0821446087211,119.2479422912 28.0821446087211,119.2479422912 28.0850100759417,119.244752593338 28.0850100759417)) | 5.7316670417785645 107 | 36 | POLYGON((119.244752593338 28.0821446087211,119.244752593338 28.0792791415006,119.2479422912 28.0792791415006,119.2479422912 28.0821446087211,119.244752593338 28.0821446087211)) | 5.764394283294678 107 | 37 | POLYGON((119.244752593338 28.0792791415006,119.244752593338 28.0764136742801,119.2479422912 28.0764136742801,119.2479422912 28.0792791415006,119.244752593338 28.0792791415006)) | 5.797055244445801 107 | 38 | POLYGON((119.244752593338 28.0764136742801,119.244752593338 28.0735482070595,119.2479422912 28.0735482070595,119.2479422912 28.0764136742801,119.244752593338 28.0764136742801)) | 5.829642295837402 107 | 39 | POLYGON((119.244752593338 28.0735482070595,119.244752593338 28.070682739839,119.2479422912 28.070682739839,119.2479422912 28.0735482070595,119.244752593338 28.0735482070595)) | 5.862148761749268 107 | 40 | POLYGON((119.244752593338 28.070682739839,119.244752593338 28.0678172726184,119.2479422912 28.0678172726184,119.2479422912 28.070682739839,119.244752593338 28.070682739839)) | 5.894571304321289 107 | 41 | POLYGON((119.244752593338 28.0678172726184,119.244752593338 28.0649518053979,119.2479422912 28.0649518053979,119.2479422912 28.0678172726184,119.244752593338 28.0678172726184)) | 5.92690896987915 107 | 42 | POLYGON((119.244752593338 28.0649518053979,119.244752593338 28.0620863381773,119.2479422912 28.0620863381773,119.2479422912 28.0649518053979,119.244752593338 28.0649518053979)) | 5.959161758422852 (10 rows)Calculate the areal precipitation. In this example, the areal precipitation is calculated per square meter. Therefore, before calculating the area of each pixel polygon, use the ST_Transform method to convert the coordinate system from EPSG:4326 to EPSG:3857 in order to obtain the area value in square meters. Then, multiply the pixel value by the area, sum the results for all pixels, and divide the sum by the total pixel area to calculate the areal precipitation. See the SQL statement:
SELECT sum(ST_Area(ST_Transform(geom,3857)) * pre) / sum(ST_Area(ST_Transform(geom,3857))) as "precipitation(mm/m^2)" FROM pixel_pre WHERE pre>0;Sample result:
precipitation(mm/m^2) ----------------------- 9.434021577002174 (1 row)
Conclusion
GanosBase raster engine provides a set of tools for importing, storing, analyzing, and visualizing grid data. It meets the requirements of remote sensing image management, DEM data analysis, and grid data analysis, and offers advanced capabilities like GPU computation to support various applications. The use of GanosBase raster engine in areal precipitation calculation has effectively supported the operation of the meteorological precipitation consultation system of the Ministry of Water Resources and has improved the analysis and calculation efficiency of the system by more than 10 times. With comprehensive capabilities and application cases in water resources, natural resources, meteorology, environmental protection, and emergency response, GanosBase raster engine provides robust spatio-temporal foundational support for aerospace big data management applications to multiple customers.