GDAL PostGIS Raster driver documentation

1 - Introduction

PostGIS Raster (previously known as WKT Raster) is the project that provides raster support on PostGIS. Since September 26st, 2010, is an official part of PostGIS project. Further information ​here.

This driver was developed during the ​Google Summer of Code 2009, and improved during October 2010. The driver only supports reading of regularly blocked in-db rasters.

The driver is under development, at same time that PostGIS Raster. The next version of the driver will include these features:

• Out-db raster support.
• Reading of non-regular blocked rasters.
• Create and modify raster on PostGIS database.

1.1 - Credits

Development

• Jorge Arévalo (jorge.arevalo at deimos-space.com) develops the driver

1.2 - More information

• PostGIS Raster documentation page ​here
• Introduction to PostGIS Raster format and description of the work done during the Google Summer of Code 2009 here
• Search for "WKT Raster" in the ​PostGIS-users and ​PostGIS-devel forums archives or in ​Nabble or write to these forums.

2 - Installation

2.1 - Installing prerequisites

These requisites are for setting up an enviroment with PostgreSQL/PostGIS with raster support.

• ​PostgreSQL version 8.3 or higher
• ​Proj4 library version 4.5.0 or higher
• ​GEOS library version 3.1.0 or higher
• ​Python version 2.5 or higher

Then you'll need to fetch the last PostGIS SVN development snapshot. The PostGIS Raster extension is part of the PostGIS library since September 26th 2010, and there's no stable PostGIS version that includes it yet. The first stable version with PostGIS Raster extension will be PostGIS 2.0, expected for April 2011.

The last PostGIS development station can be found ​here. Instructions for compiling and installing PostGIS with raster extension are ​here.

2.2 - Installing GDAL

2.2.1 - From binaries

Currently, there's no stable GDAL release that includes the PostGIS Raster driver. The first one will be GDAL 1.8.0. You can get daily build binaries for Windows mantained by Tamas Szekeres ​here. In Linux and Mac Os X, you'll need to compile the source code.

Anyway, there is updated information about general GDAL binaries here

2.2.2 - From sources

You can download daily GDAL snapshots from ​here, or checkout the code directly from SVN with any subversion client. The examples of this documentation are using Linux console client, but they're easily reproducible with any other client, simply using the same URLs

>$ svn checkout https://svn.osgeo.org/gdal/trunk/gdal gdal

After that, you have to build GDAL library with Python support:

>$ ./configure --with-python & make & make install

Further instructions here.

3 - Using GDAL PostGIS Raster driver

3.1 - Loading raster data

The GDAL PostGIS Raster driver isn't able to load raster data yet. For this proposal, you'll have to use gdal2raster script. This loader script is included with the PostGIS installation, under the raster/scripts/python subdirectory. To check the syntax of the script, execute this command from that subdirectory:

>$ python gdal2raster.py --help

Important: The out-db raster support for PostGIS Raster is still under development. Please, don't use this option.

Example 1:

Load ​this one-band grayscale image (utm.tif) on PostGIS using loader script:

>$ python gdal2raster.py -r /path/to/utm.tif -t [schema.]utm -l 1 -k 64x64 -o utm.sql -s 26711 -I -M

In this example, the script generates a SQL output (redirected to the file utm.sql). This SQL code adds the image utm.tif to PostGIS, using a regular blocking arrangement with blocks of 64x64px. A new raster table will be created and each image block will be a row of the raster table. To execute the code, you must run this command from a console:

>$ psql -d <raster_database> -f utm.sql -U <user> -W

The raster_database must be a PostgreSQL database with PostGIS Raster extension enabled. The user user must have privileges to create tables and insert values in the database.

Example 2:

Load ​this RGB image (katrina.tif) on PostGIS using loader script:

>$ python gdal2raster.py -r /path/to/katrina.tif -t katrina -l 1 -k 64x64 -o katrina.sql -s 4326 -I -M

As in Example 1, this code generates a SQL file, called katrina.sql with the SQL code to add the image katrina.tif to PostGIS using a regular blocking arrangement with blocks of 64x64px. Again, a new raster table will created and each image block will be a row of the raster table. To execute the code, again run:

>$ psql -d <raster_database> -f katrina.sql -U <user> -W

Example 3:

Using the second image (katrina.tif), let's create an overview of the image of half size (meaning image dimensions, not file size) in PostGIS.

>$ python gdal2raster.py -r /path/to/katrina.tif -t katrina -l 2 -V -k 64x64 -o katrina_ov2.sql -s 4326 -I -M

The key concepts here are:

• The -V flag. This flag allows loader script to create a new table to store the overviews' metadata. If you want to load several images with overviews, you'll have to use this flag only the first time the loader script is called, to create the overviews' metadata table only once.
• The -l 2 flag. This flag forces the loader script to create a table with a GDAL-provided overview of the original file, with an overview factor of 2. This is, half size.

As usual, to execute the generated SQL code, run this command:

>$ psql -d <raster_database> -f katrina_ov2.sql -U <user> -W

3.2 - Reading data

If you want to use the GDAL WKT Raster driver, you must provide a connection string as Dataset's name. The syntax of this connection string is (the quotes may be ommitted):

 PG":host='<host>' port:'<port>' dbname='<dbname>' user='<user>' password='<password>' [schema='<schema>'] [table='<raster_table>'] [where='<sql_where>'] [mode='<working_mode>']"

Note that the string, until the part that starts with "table='" is a libpq-style ​connection string. That means that you can leave out unnecessary fields (like password, in some cases).

The "table" option requires the name of a WKT Raster table. This table only can be created by using the script gdal2tiles, by now.

The "where" option is used to filter the results of the raster table. Any SQL-WHERE expression is valid. The "mode" option is used to know the expected arrangement of the raster table. There are 2 possible values:

• mode = 1. Also called ONE_RASTER_PER_ROW mode. In this case, a raster table is considered as a bunch of different raster files. This mode is intended for raster tables storing different raster files. It's the default mode if you don't provide this field in connection string.
• mode = 2. Also called ONE_RASTER_PER_TABLE mode. In this case, a raster table is considered as a unique raster file, even if the table has more than one row. This mode is intended for reading tiled rasters from database, just like the rasters created in section 3.1.

Three important notes here:

• If a table stores a tiled raster (like ones created in section 3.1) and you execute the driver with mode = 1, each image tile will be considered as a different image, and will be reported as a subdataset.
• There are use cases the driver can't still work with. For example: non-regular blocked rasters. That cases are detected and an error is raised. Anyway, as I've said, the driver is under development, and will work with more raster arrangements ASAP.
• There's an additional working mode. If you don't provide a table name, the driver will look for existing raster tables in all allowed database' schemas, and will report each table as a subdataset.

You must use this connection string's format in all the gdal tools, like gdalinfo, gdal_translate, gdalwarp, etc.

Example 1:

To get info about utm.tif file stored in database (see section 3.1) run this command:

>$ gdalinfo -mm -stats -checksum "PG:host='localhost' dbname='<raster_database>' user='<user>' password='<password>' table='utm' mode='2'"

As user / password, in a typical installation, you can use postgres / postgres

Example 2:

To get an overview of the katrina.tif image with half size run this command:

>$ gdal_translate -outsize 50% 50% "PG:host='localhost' dbname='<raster_database>' user='<user>' password='<password>' table='katrina'" katrina_half_size.tif 

This will create a file called katrina_half_size.tif like the original katrina.tif but half size.

3.3 - Creating and modifying data

The GDAL PostGIS Raster driver is not able to create and modify rasters yet. You'll have to use gdal2raster loader to create new ones.

4 - Reporting problems

To report errors and suggestions, you can use the ​gdal-dev list or write me an e-mail: jorge.arevalo at deimos-space.com