Table of Contents

Raster Data in PostGIS: Storing, Querying, and Processing Raster Files with PostGIS Rasters

1. Introduction: Moving Beyond Flat-File Raster Storage

Raster data is fundamental to geospatial analysis. Digital elevation models (DEMs), satellite imagery, land-cover classifications, rainfall surfaces, and temperature grids are typically stored as raster files such as GeoTIFFs (.tif).

In a traditional GIS workflow, analysts load these files into desktop applications, perform spatial operations, and save the results as separate outputs. This works well for individual projects, but managing large raster collections becomes more challenging when several users and applications must access the same datasets.

Consider an enterprise mapping platform that serves elevation data, vegetation indices, and satellite imagery to thousands of users. Maintaining separate copies of these files across applications can complicate access control, data updates, version management, and spatial queries.

PostGIS Raster extends PostgreSQL with raster data types and functions, allowing supported raster datasets to be stored, queried, and processed alongside vector geometries within the database.

Instead of exporting data between multiple applications for every operation, analysts can use SQL to extract elevation values, clip raster regions to administrative boundaries, calculate statistics, and derive terrain surfaces.

Why use PostGIS Raster?

Centralized data management: Organize raster and vector datasets within the same database.
Spatial SQL: Combine raster values with vector boundaries, locations, and attributes.
Controlled access: Use PostgreSQL permissions and database roles to manage access to data.
Application integration: Supply raster-derived information to web GIS applications and analytical services.
Reproducible workflows: Record analytical operations as SQL scripts that can be reviewed and repeated.

PostGIS Raster is not automatically faster than every desktop GIS or raster-processing library. Its main advantage is that it brings raster management and supported analytical operations into a database environment, where they can be integrated with enterprise spatial workflows.

This tutorial demonstrates how to import a GeoTIFF into PostGIS and perform four practical raster operations using SQL.

2. Step 1: Loading Raster Files into PostgreSQL

The most common way to import existing raster files is with raster2pgsql, a command-line utility distributed with PostGIS installations.

The utility reads raster formats supported by its GDAL dependencies and generates SQL statements that create a raster table and load its contents.

2.1 Prepare the Database

First, connect to your PostgreSQL database and enable PostGIS.

-- Enable PostGIS vector and geometry functionality.
CREATE EXTENSION IF NOT EXISTS postgis;

-- Enable raster functionality where provided separately
-- by your PostGIS installation.
CREATE EXTENSION IF NOT EXISTS postgis_raster;

Some installations package raster support differently. Confirm that postgis_raster is available and enabled on your server before proceeding.

You also need the PostGIS command-line utilities installed on the machine running the import.

2.2 Understand the raster2pgsql Command

Suppose you have a digital elevation model named dem.tif.

A common loading workflow is:

# Import a GeoTIFF as tiled PostGIS raster data.
# Replace the example SRID with the actual CRS of the DEM.

raster2pgsql \
  -s 32637 \
  -I \
  -C \
  -M \
  -F \
  -t 256x256 \
  /data/dem.tif \
  public.dem_tiles \
  | psql -d gisdb

This example assumes that the DEM’s coordinates genuinely use EPSG:32637. Replace it with the correct SRID for your own dataset.

The options have the following meanings:

Option	Purpose
`-s 32637`	Assign the specified spatial reference identifier to the input raster
`-I`	Create a GiST spatial index on the raster column
`-C`	Apply standard raster constraints for metadata and coverage registration
`-M`	Run `VACUUM ANALYZE` after loading
`-F`	Add a column identifying the source filename
`-t 256x256`	Divide the raster into 256-by-256-pixel tiles

Important: The -s option is not a reprojection operation. It assigns an SRID. If the raster’s coordinates need to be transformed into another CRS, reproject the raster first using an appropriate tool such as GDAL.

The -t option creates smaller raster tiles rather than storing an entire large image as one row. Tile size affects query performance and storage overhead, so test an appropriate size for your workload.

The example pipes the generated SQL directly to psql. Alternatively, you can create a SQL file and execute it separately.

The official PostGIS raster data management guide documents raster2pgsql options, loading modes, constraints, and tile management.

2.3 Verify the Imported Raster Table

Once the import finishes, inspect the table:

-- Inspect the columns generated by raster2pgsql.
SELECT
    column_name,
    data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'dem_tiles';

A typical table contains an integer tile identifier, a raster column named rast, and optionally a filename column.

Check the record count:

-- Count the tiles loaded into the database.
SELECT COUNT(*) AS tile_count
FROM public.dem_tiles;

A count greater than one is expected when the original raster has been subdivided into tiles.

You now have a raster dataset available through PostgreSQL.

3. Step 2: Exploring PostGIS Raster Metadata

Before performing raster analysis, inspect the data’s dimensions, coordinate reference system, band structure, and pixel types.

PostGIS provides several functions for this purpose.

3.1 Inspect Raster Dimensions and SRID

The following functions report basic raster properties:

ST_Width(rast) — raster width in pixels.
ST_Height(rast) — raster height in pixels.
ST_SRID(rast) — raster spatial reference identifier.
ST_NumBands(rast) — number of bands.
ST_Band(rast, n) — returns a raster containing the selected band.

Execute the following query:

-- Inspect the dimensions and coordinate system of each tile.
SELECT
    rid,
    ST_Width(rast) AS width_pixels,
    ST_Height(rast) AS height_pixels,
    ST_SRID(rast) AS srid,
    ST_NumBands(rast) AS band_count
FROM public.dem_tiles
ORDER BY rid
LIMIT 10;

If you imported a single-band DEM using 256-by-256 tiles, most complete tiles will be 256 pixels wide and 256 pixels high. Tiles along the outer boundary may be smaller, depending on how the loader divided the raster.

ST_SRID should return the expected SRID. An unexpected value, including zero, may indicate that the raster’s spatial reference metadata needs investigation.

3.2 Inspect Band Properties

Raster bands may have different pixel types and NoData values. Check them before performing calculations.

-- Inspect the first band's metadata.
SELECT
    rid,
    (ST_BandMetaData(rast, 1)).*
FROM public.dem_tiles
ORDER BY rid
LIMIT 10;

Band metadata can include the pixel type, NoData value, and whether the band references an external raster file.

You can also inspect the pixel type directly:

-- Check the storage type of band 1.
SELECT
    rid,
    ST_BandPixelType(rast, 1) AS pixel_type,
    ST_BandNoDataValue(rast, 1) AS nodata_value
FROM public.dem_tiles
LIMIT 10;

A DEM might contain 32-bit floating-point values, while an optical satellite band might contain 16-bit integer values.

This distinction matters when choosing output data types and NoData sentinels during raster processing.

3.3 Select a Specific Raster Band

ST_Band extracts one or more bands from a raster. Band numbering starts at 1.

-- Return only the first raster band.
SELECT
    rid,
    ST_Band(rast, 1) AS elevation_band
FROM public.dem_tiles
LIMIT 5;

This returns raster values, not a textual metadata description. Use ST_BandMetaData to inspect band properties and ST_Band when an operation needs a selected band or a new raster containing only certain bands.

For a multiband satellite raster, this function can be useful when an operation requires a specific spectral band.

4. Step 3: Four Practical Raster Operations Using Spatial SQL

The following examples assume a tiled elevation raster stored in public.dem_tiles, with rid identifying the tile and rast containing the raster.

The examples use a vector point table called public.sample_points and a polygon table called public.admin_areas. These tables must already exist, and their geometry columns must use a CRS compatible with the raster operations.

Query 1: Extract Pixel Values at Specific Point Coordinates

The PostGIS Raster function ST_Value retrieves the value of a raster pixel at a specified location.

Suppose surveyors have collected several sample points and want to extract the elevation associated with each location.

-- Extract DEM values for all sample points.
-- Point and raster coordinates must use compatible CRSs.

SELECT
    p.sample_id,
    ST_Value(
        r.rast,
        1,
        p.geom
    ) AS elevation_value
FROM public.sample_points AS p
JOIN public.dem_tiles AS r
    ON ST_Intersects(r.rast, p.geom);

Here:

p.sample_id identifies the survey observation.
r.rast is the DEM tile.
1 selects raster band 1.
p.geom supplies the sampling location.
ST_Intersects restricts the join to raster tiles intersecting the point.

The default sampling method uses the nearest pixel. In current PostGIS versions, ST_Value can also accept a resampling method such as bilinear interpolation.

For example:

-- Extract a bilinearly interpolated elevation estimate.
SELECT
    p.sample_id,
    ST_Value(
        r.rast,
        1,
        p.geom,
        TRUE,
        'bilinear'
    ) AS interpolated_elevation
FROM public.sample_points AS p
JOIN public.dem_tiles AS r
    ON ST_Intersects(r.rast, p.geom);

Bilinear interpolation uses neighboring pixel values to estimate a value at the requested location. It can be useful for continuous elevation surfaces, but it should not be applied indiscriminately to categorical rasters such as land-cover classes.

Interpretation: The query returns a database result containing sample identifiers and their corresponding elevation values. This output can be joined to survey records or passed to an application without exporting the full DEM.

See the official ST_Value documentation.

Query 2: Clip a Raster to Vector Boundaries Using SQL

Raster analysis frequently requires restricting a large dataset to a smaller administrative or study area.

For instance, an analyst might want an elevation raster clipped to municipal boundaries.

The ST_Clip function returns a raster limited to the specified geometry.

-- Clip intersecting DEM tiles to administrative boundaries.
-- This example assumes a floating-point DEM and a suitable
-- NoData sentinel of -9999.

CREATE TABLE public.zone_dem_clips AS
SELECT
    z.zone_id,
    r.rid AS source_tile_id,
    ST_Clip(
        r.rast,
        1,
        z.geom,
        -9999::double precision,
        TRUE,
        TRUE
    ) AS rast
FROM public.admin_areas AS z
JOIN public.dem_tiles AS r
    ON ST_Intersects(r.rast, z.geom);

The arguments specify:

r.rast — the source raster.
1 — the band to clip.
z.geom — the clipping boundary.
-9999 — the example NoData value used outside the clipping area.
TRUE — crop the result to the intersection extent.
TRUE — include pixels touched by the clipping geometry.

The last argument is supported from PostGIS 3.5.0 onward. Earlier versions do not support that argument and require the older function signature.

Choose a NoData sentinel that is valid for the raster’s pixel type and does not collide with legitimate data values. For integer or unsigned pixel types, -9999 may not be representable. The example is intended for a suitable floating-point DEM.

The output table contains clipped raster fragments. Because the source raster is tiled, a polygon intersecting several tiles may produce several records. That is normal and often useful for tiled processing.

For further details, see the PostGIS ST_Clip documentation.

Query 3: Calculate Zonal Statistics in SQL

Zonal statistics summarize raster values within vector zones.

Examples include calculating mean elevation by district, average vegetation index by agricultural field, or temperature statistics by census area.

PostGIS provides ST_SummaryStats for one raster and ST_SummaryStatsAgg for aggregating statistics across a set of rasters, such as the tiles intersecting a polygon.

The following query clips each intersecting tile to the zone before calculating aggregate statistics.

-- Calculate elevation statistics for each administrative zone.
-- The raster and polygon geometries must use compatible CRSs.
-- The example assumes -9999 is a valid NoData value for the DEM.

WITH zone_stats AS (
    SELECT
        z.zone_id,
        ST_SummaryStatsAgg(
            ST_Clip(
                r.rast,
                1,
                z.geom,
                -9999::double precision,
                TRUE,
                TRUE
            ),
            1,
            TRUE,
            1.0
        ) AS stats
    FROM public.admin_areas AS z
    JOIN public.dem_tiles AS r
        ON ST_Intersects(r.rast, z.geom)
    GROUP BY z.zone_id
)
SELECT
    zone_id,
    (stats).count  AS pixel_count,
    (stats).mean   AS mean_elevation,
    (stats).stddev AS elevation_stddev,
    (stats).min    AS minimum_elevation,
    (stats).max    AS maximum_elevation
FROM zone_stats
ORDER BY zone_id;

The parameters to ST_SummaryStatsAgg specify:

The raster input being summarized.
Band 1.
Exclusion of NoData pixels (TRUE).
A sampling fraction of 1.0, meaning all eligible pixels are included.

The output provides several useful measurements:

Statistic	Interpretation
Pixel count	Number of valid pixels included
Mean	Average elevation within the zone
Standard deviation	Variation in pixel elevations
Minimum	Lowest valid elevation
Maximum	Highest valid elevation

The query assumes that the administrative zones and raster tiles are aligned in a compatible coordinate system. If the data use different CRSs, prepare transformed geometries before running the analysis. Avoid repeatedly transforming an indexed geometry column inside the join when you can transform the zone data once beforehand.

The meaning of the statistics also depends on the raster’s units and data quality. If you are summarizing a vegetation index, for example, the mean is the average index value rather than an elevation measurement.

For details, consult the ST_SummaryStats documentation and ST_SummaryStatsAgg documentation.

Query 4: Generate Slope and Aspect Surfaces in the Database

A DEM records elevation, but terrain analysis often requires derivative surfaces.

Two common examples are:

Slope: The steepness of terrain at each pixel.
Aspect: The compass direction toward which the terrain faces.

PostGIS provides ST_Slope and ST_Aspect for these operations.

First, understand raster tiling

A DEM imported using raster2pgsql -t 256x256 is stored as many tiles. Terrain derivatives depend on neighboring pixels, often within a 3-by-3 neighborhood.

If you calculate slope or aspect independently for each tile, tile-edge effects can occur because adjacent cells from neighboring tiles are unavailable to the calculation.

For a moderate-sized, regularly aligned DEM coverage, one straightforward SQL workflow is to mosaic the tiles first and then derive the terrain surfaces. This may require substantial memory and processing resources for very large datasets.

Generate a slope raster

-- Create a mosaicked DEM and calculate slope in degrees.
-- Assumes horizontal and vertical units are both metres.

CREATE TABLE public.dem_slope AS
WITH dem_mosaic AS (
    SELECT ST_Union(rast) AS rast
    FROM public.dem_tiles
)
SELECT
    ST_Slope(
        rast,
        1,
        '32BF',
        'DEGREES',
        1.0,
        FALSE
    ) AS rast
FROM dem_mosaic;

The arguments specify band 1, 32-bit floating-point output, degree units, a scale factor of 1, and no interpolation of NoData cells.

The scale parameter relates vertical and horizontal units. A scale of 1.0 is appropriate when both elevation and horizontal coordinates are in metres. If they use different units or the raster is in geographic coordinates, choose an appropriate scale instead.

Generate an aspect raster

-- Generate aspect in degrees from the same DEM mosaic.
-- Aspect is measured clockwise from north.

CREATE TABLE public.dem_aspect AS
WITH dem_mosaic AS (
    SELECT ST_Union(rast) AS rast
    FROM public.dem_tiles
)
SELECT
    ST_Aspect(
        rast,
        1,
        '32BF',
        'DEGREES',
        FALSE
    ) AS rast
FROM dem_mosaic;

Aspect values generally range from 0 to 360 degrees, measured clockwise from north. Flat pixels may be assigned an aspect value of -1.

The examples produce a single mosaic-derived result raster, so they are best suited to moderate datasets where a full mosaic can be processed economically.

For very large raster coverages, mosaicking the entire DEM into one raster may be impractical. Use a tile-aware processing strategy that includes appropriate neighboring-pixel context, or derive slope and aspect with a raster-processing tool designed for large tiled datasets before loading the outputs into PostGIS.

Authoritative references: ST_Slope and ST_Aspect.

5. Performance Considerations: In-Database vs. Out-of-Database Rasters

PostGIS Raster supports two broad storage approaches.

5.1 In-Database Raster Storage

With in-database storage, raster pixel data are stored in PostgreSQL tables.

Advantages:

Raster data are managed within the database.
PostgreSQL permissions and operational policies can govern access.
Data can be included in database backup and recovery processes.
SQL operations can work directly with the stored raster tiles.

Trade-offs:

Loading large raster collections can take time.
Database storage requirements can be substantial.
Backups and replication may require significant resources.
Poor tile sizing can affect query efficiency.

For many enterprise workloads, tiled in-database storage is a sensible starting point, particularly when raster data must be managed alongside vector data and served through database-backed applications.

5.2 Out-of-Database Raster Storage

Out-of-database, or out-db, raster storage keeps the raster pixels in external files and stores references to those files in the database.

The loader’s -R option supports this approach.

For example:

# Store references to the GeoTIFF instead of embedding its pixels.
# The source file must be accessible to the database server.
# Replace the SRID and path for your own raster.

raster2pgsql \
  -s 32637 \
  -R \
  -I \
  -C \
  -F \
  -t 256x256 \
  /data/dem.tif \
  public.dem_outdb \
  | psql -d gisdb

This can reduce the amount of raster pixel data stored in PostgreSQL and may be attractive when the files are already managed in shared storage or object storage.

However, the files remain external dependencies. They must stay available at their referenced locations, and the database server must be configured to access the necessary formats and paths.

Administrators must also consider file permissions, access security, backups, replication, and the availability of the external storage.

PostGIS commonly disables out-db raster access by default. Enabling it requires suitable configuration, including the relevant PostGIS settings and allowed GDAL drivers. Only enable the necessary access for trusted files and users.

See the official PostGIS raster management guide for the out-db configuration requirements.

5.3 Choosing the Right Storage Strategy

Requirement	In-database storage	Out-db storage
Centralized database management	Strong fit	Metadata and references are centralized, but pixels are external
Large raster collections	May require substantial database storage	Can reduce database storage requirements
Backup simplicity	Raster pixels can be included in database backups	External files need a separate backup strategy
External file dependency	No external pixel-file dependency after import	Source files must remain accessible
Enterprise access management	Database permissions govern access to stored data	Database and external file permissions both matter
Operational complexity	Database storage and maintenance	Database plus external storage management

Neither method is universally better. Choose according to data volume, access patterns, backup requirements, and how the raster service will be deployed.

6. Best Practices for Production Raster Databases

Before deploying a raster workflow, consider the following practices:

Tile large rasters: Use a suitable tile size and measure query performance rather than assuming one size is always best.
Create a spatial index: Use raster2pgsql -I or an appropriate GiST index so spatially selective queries can identify candidate tiles.
Maintain metadata: Confirm the SRID, pixel resolution, band count, pixel type, and NoData values.
Keep statistics current: Use ANALYZE or suitable routine maintenance after substantial data changes.
Align the raster and vector CRS: Avoid unnecessary transformations during repeated spatial joins.
Account for tile boundaries: Operations requiring neighboring pixel values, such as slope calculations, need sufficient spatial context.
Validate outputs: Compare database-derived results with trusted raster software or reference data, especially before using terrain or environmental products operationally.
Choose storage intentionally: Keep external file dependencies, permissions, and backup procedures explicit if you use out-db raster references.

Also consider whether the raster data actually need to be stored in PostgreSQL. For a workflow primarily focused on large-scale raster processing, tools such as GDAL may be more suitable for some operations, while PostGIS can manage the resulting products and serve integrated spatial queries.

7. Conclusion

PostGIS Raster enables analysts to bring raster data management and supported processing operations into a PostgreSQL environment.

Using raster2pgsql, GeoTIFF imagery can be imported as tiled database raster data or registered through out-db references. Functions such as ST_Width, ST_Height, ST_SRID, ST_NumBands, and ST_BandMetaData help inspect raster properties.

The practical SQL workflow then extends to point sampling with ST_Value, clipping with ST_Clip, zonal statistics with ST_SummaryStatsAgg, and terrain analysis with ST_Slope and ST_Aspect.

The main lesson is to choose a storage strategy that matches the application, design tiles and indexes around the spatial queries, and ensure that raster calculations respect coordinate systems, NoData values, and neighboring-pixel requirements.

PostGIS Raster is most valuable when raster data are part of a larger spatial information system—not merely isolated files, but datasets that need to interact reliably with vector features, SQL queries, and enterprise applications.

References

PostGIS Raster Data Management — raster2pgsql, tiling, and out-db storage.
PostGIS Raster Reference — raster accessors, processing functions, and map algebra.
ST_Value — extracting raster values at point locations.
ST_Clip — clipping rasters to geometry boundaries.
ST_SummaryStats and ST_SummaryStatsAgg — descriptive raster statistics.
ST_Slope and ST_Aspect — terrain derivative calculations.