Loading raster data into PostGIS from the Command Line¶

How It Works¶

raster2pgsql converts a raster file into a series of SQL commands that can be loaded into a database–it does not perform the actual loading. The output of this command may be captured into a SQL file, or piped to the psql command, which will execute the commands against a target database.

Preparation¶

1. Select the raster file(s) you wish to load.
2. Identify the SRID (“projection”) of your data. If available, this information is accessed via the layer metadata in GeoServer.
3. Either identify the target database where you would like to load the data, or ref:create a new database <dataadmin.pgGettingStarted.createdb>.

Loading data¶

1. Open a terminal or command line window.

   Note

   If the path to the raster2pgsql and psql commands haven’t been included in your PATH system variable, you may wish to add them now. Please consult your operating system help for information on how to change the PATH variable.

2. Confirm PostGIS is responding to requests. The quickest way to test this is to run a psql query.

   psql -c "SELECT PostGIS_Version()"
   

               postgis_version
   ---------------------------------------
    2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
   

   Note

   These examples use the default port (5432), but substitute your own PostGIS port if different with the -p option.

3. To see a list of the supported raster formats, use raster2pgsql with the -G option.

   raster2pgsql -G
   

   Available GDAL raster formats:
     Virtual Raster
     GeoTIFF
     National Imagery Transmission Format
     Raster Product Format TOC format
     ECRG TOC format
     Erdas Imagine Images (.img)
     CEOS SAR Image
     CEOS Image
     JAXA PALSAR Product Reader (Level 1.1/1.5)
     Ground-based SAR Applications Testbed File Format (.gff)
     ELAS
     Arc/Info Binary Grid
     Arc/Info ASCII Grid
     GRASS ASCII Grid
     SDTS Raster
     DTED Elevation Raster
     Portable Network Graphics
     JPEG JFIF
     .............
   

4. To convert and load a raster file into the target database in one step, run the raster2pgsql command and pipe the output into the psql command. The recommended syntax is:

   raster2pgsql -I -C -s <SRID> <PATH/TO/RASTER FILE> <SCHEMA>.<DBTABLE> | psql -d <DATABASE>
   

   The command parameters are:

   • <SRID>—Spatial reference identifier
   • <PATH/TO/RASTER FILE>—Full path to the raster file (such as C:\MyData\land\landuse.tif)
   • <SCHEMA>—Target schema where the new raster table will be created
   • <DBTABLE>—New database table to be created (usually the same name as the source raster file)
   • <DATABASE>—Target database where the table will be created

   The following example uses raster2pgsql to create an input file and upload it into 100x100 tiles. The -I option will create a spatial GiST index on the raster column after the table is created. This is strongly recommended for improved performance. The -C option will apply the raster constraints (SRID, pixel size and so on) to ensure the new raster table is correctly registered in the raster_columns view.

   raster2pgsql -s 4236 -I -C -M *.tif -F -t 100x100 public.demelevation | psql -d gisdb
   

   Note

   If you omit the name of the schema and use demelevation instead of public.demelevation, the raster table will be created in the default schema of the database or user.

   Note

   For more information about raster2pgsql command options, please refer to the Loading and Creating Rasters section of the PostGIS Documentation.

   To capture the SQL commands, pipe the output to a file:

   raster2pgsql -s 4236 -I -C -M *.tif -F -t 100x100 public.demelevation > elev.sql
   
   psql -U postgres -d gisdb -f elev.sql
   

Batch Loading¶

Although it is feasible to run the raster2pgsql command as many times as required, it may be more efficient to create a batch file to load a number of raster files.

for %%f in (*.tif) do raster2pgsql -I -s <SRID> %%f %%~nf > %%~nf.sql
for %%f in (*.sql) do psql -d <DATABASE> -f %%f

#!/bin/bash

for f in *.tif
do
    raster2pgsql -I -s <SRID> $f `basename $f .tif` > `basename $f .tif`.sql
done

for f in *.sql
do
    psql -d <DATABASE> -f $f
done

Creating Raster Tables in the Database¶

You can also add rasters and raster tables directly to the database. A typical workflow is as follows:

1. Create a table with a raster column.

   CREATE TABLE myRaster(rid serial primary key, rast raster);
   

2. Populate the table with some raster data by either creating empty rasters or creating rasters from other geometries.

   • To create an empty raster, use ST_MakeEmptyRaster().

     INSERT INTO myRasterTable(rid,rast)
     VALUES(3, ST_MakeEmptyRaster( 100, 100, 0.0005, 0.0005, 1, 1, 0, 0, 4326) );
     

   • To use an existing raster as a template for a new raster, execute the following:

     INSERT INTO myRasterTable(rid,rast)
     SELECT 4, ST_MakeEmptyRaster(rast)
       FROM myRasterTable WHERE rid = 3;
     

     Confirm the successful insertion of the two rasters and display the raster metadata with ST_MetaData():

     SELECT rid, (md).*
       FROM (SELECT rid, ST_MetaData(rast) As md
                   FROM myRasterTable
                   WHERE rid IN(3,4)) As foo;
     

     rid|upperleftx|upperlefty|width|height|scalex|scaley|skewx|skewy|srid|numbands
     ---+----------+-----------+----+-------+-----+------+-----+-----+----+----------
     3  | 0.0005   | 0.0005   | 100 | 100  | 1    | 1    | 0   | 0   |4326| 0
     4  | 0.0005   | 0.0005   | 100 | 100  | 1    | 1    | 0   | 0   |4326| 0
     

   • To create a raster from an existing geometry, use ST_AsRaster().

     CREATE TABLE myNewRaster AS
       SELECT 1 AS rid, ST_AsRaster((
            SELECT
               ST_Collect(geom)
            FROM myGeomTable
            ), 1000.0, 1000.0 )
       AS rast;
     

   • To create a new raster table based on an existing raster table but with a different projection, use ST_Transform(). If no projection algorithm is specified, NearestNeighbor is used by default. The following example will use the Bilinear algorithm.

     Note

     Algorithm options are: NearestNeighbor, Bilinear, Cubic, CubicSpline, and Lanczos.

     SELECT ST_Width(myNewRaster) As w_before, ST_Width(wgs_84) As w_after,
       ST_Height(myNewRaster) As h_before, ST_Height(wgs_84) As h_after
          FROM
                ( SELECT rast As myNewRaster, ST_Transform(rast,4326) As wgs_84,
              ST_Transform(rast,4326, 'Bilinear') AS wgs_84_bilin
                    FROM aerials.o_2_boston
                              WHERE ST_Intersects(rast,
                                     ST_Transform(ST_MakeEnvelope(-71.128, 42.2392,-71.1277,
                                          42.2397, 4326),26986) )
                        LIMIT 1) As foo;
     

     w_before | w_after | h_before | h_after
     ------ --+-------- +----------+---------
     200      |  228    | 200      | 170
     

3. To optimize query performance for the raster table, create a spatial index on the raster column.

   CREATE INDEX myRasterTable_rast_st_convexhull_idx ON myRasterTable USING gist(ST_ConvexHull(rast));
   

   Note

   Pre-2.0 versions of PostGIS raster were based on the envelope rather than the convex hull. To ensure spatial indexes work correctly in PostGIS 2.0, drop any existing envelope indexes and replace them with convex hull based indexes.