PostgreSQL: ST_Value Function Usage Examples to Optimize Raster Data in a Database

By digoal

Background

Raster data is image data composed of lattices, which have the spatial property (boundary). A lattice is composed of pixels. Therefore, each pixel also has a spatial property. Moreover, each pixel is filled with values representing factors such as temperature, grayscale, hue, saturation, and brightness.

Each raster can have multiple bands. Each band can represent a layer, and various bands can be combined and superimposed for calculation.

Raster data allows analyzing and visualizing data with a spatial or business property — for example, heatmap data, greening rate maps, road distribution, and temperature distribution.

The structure of ArcGIS raster data is as follows:

Any valid grid format
Pixel array
One or more wave bands
Stored as a file on a disk or in a geographical database
More than 70 formats supported
As an image service
As a data source in geographic processing and analysis tools
Available in “image analysis”

When a pixel’s value is extracted, some performance issues may occur because raster data can be large or small.

Use ST_Value Function to Extract the Value of a Raster’s Specified Pixel

When the raster file is very large, it may take a long time to execute the ST_Value Function, although only one pixel’s value is extracted.

The performance of this step is related to the data structure and retrieval method of the raster file.

Use ST_Value Function to Set the Value of a Raster’s Specified Pixel

http://postgis.net/docs/RT_ST_SetValue.html

Knowledge About Raster

Learn about raster objects from the ArcGIS or PostGIS documents:

Optimization Method

Optimization at the Business Layer

1) Split a large raster object into several smaller objects.

2) Convert the boundary into a geometry point and create an expression index on the boundary.

3) Check whether the input geometry point intersects the raster being queried (ST_Intersects). If yes, calculate ST_Value. If no, do not calculate ST_Value.

Optimization at the Database Kernel Layer

Another optimization method is establishing a better data model for raster files that can facilitate fast searches, which is transparent to business systems.

Summary

The optimization methods are as follows:

1) Split a large raster object into several small objects to reduce the computing resource consumption.

2) Use expression indexes to reduce the computing resource consumption or I/O amplification.

3) Reduce accuracy and stratify raster objects.

SELECT   
  ST_AsText(ST_Union(pixpolyg)) As shadow  
FROM   
(  
SELECT         ST_Translate(  
                  ST_MakeEnvelope(  
            ST_UpperLeftX(rast),   
ST_UpperLeftY(rast),  
ST_UpperLeftX(rast) + ST_ScaleX(rast)*2,  
ST_UpperLeftY(rast) + ST_ScaleY(rast)*2,   
    0  
      ),   
      ST_ScaleX(rast)*x,   
      ST_ScaleY(rast)*y  
           ) As pixpolyg,   
           ST_Value(rast, 2, x, y) As b2val  
FROM   
  dummy_rast   
CROSS JOIN  
  generate_series(1,1000,2) As x   
CROSS JOIN   
  generate_series(1,1000,2) As y  
WHERE rid =  2  
  AND x <= ST_Width(rast)    
  AND y <= ST_Height(rast)    
) As foo  
WHERE  
ST_Intersects(   
  pixpolyg,  
  ST_GeomFromText('POLYGON((3427928 5793244,3427927.75 5793243.75,3427928 5793243.75,3427928 5793244))',0)  
)   
AND b2val != 254;

Community

PostgreSQL: ST_Value Function Usage Examples to Optimize Raster Data in a Database

Background

Use ST_Value Function to Extract the Value of a Raster’s Specified Pixel

Use ST_Value Function to Set the Value of a Raster’s Specified Pixel

Knowledge About Raster

Optimization Method

Optimization at the Business Layer

Optimization at the Database Kernel Layer

Summary

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database for FinTech Solution

Oracle Database Migration Solution

Database Migration Solution