PostGIS

Chapter 13. PostGIS Raster Frequently Asked Questions
Prev		Next

Chapter 13. PostGIS Raster Frequently Asked Questions

13.14. I’m getting error ERROR: RASTER_fromGDALRaster: Could not open bytea with GDAL. Check that the bytea is of a GDAL supported format. when using ST_FromGDALRaster or ERROR: rt_raster_to_gdal: Could not load the output GDAL driver when trying to use ST_AsPNG or other raster input functions.

13.1.

Where can I find out more about the PostGIS Raster Project?

Refer to the PostGIS Raster home page.

13.2.

Are there any books or tutorials to get me started with this wonderful invention?

There is a full length beginner tutorial Intersecting vector buffers with large raster coverage using PostGIS Raster. Jorge has a series of blog articles on PostGIS Raster that demonstrate how to load raster data as well as cross compare to same tasks in Oracle GeoRaster. Check out: Jorge’s PostGIS Raster / Oracle GeoRaster Series. There is a whole chapter (more than 35 pages of content) dedicated to PostGIS Raster with free code and data downloads at PostGIS in Action - Raster chapter. Also covered in second edition.. You can buy PostGIS in Action now from Manning in hard-copy (significant discounts for bulk purchases) or just the E-book format. You can also buy from Amazon and various other book distributors. All hard-copy books come with a free coupon to download the E-book version.

Here is a review from a PostGIS Raster user PostGIS raster applied to land classification urban forestry

13.3.

How do I install Raster support in my PostGIS database?

PostGIS Raster is part of the PostGIS codebase and generally available with most PostGIS binary distributions. Starting with PostGIS 3.0, PostGIS raster is now a separate extension and requires: CREATE EXTENSION postgis_raster; to enable it in your database. If you are compiling your own PostGIS, you will need to compile with GDAL otherwise postgis_raster extension will not be built.

Refer to Download PostGIS binaries for popular distributions of PostGIS that include raster support.

13.4.

How do I load Raster data into PostGIS?

The latest version of PostGIS comes packaged with a raster2pgsql raster loader executable capable of loading many kinds of rasters and also generating lower resolution overviews without any additional software. Please refer to Section 11.1.1, “Using raster2pgsql to load rasters” for more details.

13.5.

What kind of raster file formats can I load into my database?

Any that your GDAL library supports. GDAL supported formats are documented GDAL File Formats.

Your particular GDAL install may not support all formats. To verify the ones supported by your particular GDAL install, you can use

raster2pgsql -G

13.6.

Can I export my PostGIS raster data to other raster formats?

Yes

PostGIS raster has a function ST_AsGDALRaster that will allow you to use SQL to export to any raster format supported by your GDAL. You can get a list of these using the ST_GDALDrivers SQL function.

You can also use GDAL commandline tools to export PostGIS raster to other formats. GDAL has a PostGIS raster driver, but is only compiled in if you choose to compile with PostgreSQL support.

The driver currently doesn’t support irregularly blocked rasters, although you can store irregularly blocked rasters in PostGIS raster data type.

If you are compiling from source, you need to include in your configure

--with-pg=path/to/pg_config

to enable the driver. Refer to GDAL Build Hints for tips on building GDAL against in various OS platforms.

If your version of GDAL is compiled with the PostGIS Raster driver you should see PostGIS Raster in list when you do

gdalinfo --formats

To get a summary about your raster via GDAL use gdalinfo:

gdalinfo  "PG:host=localhost port=5432 dbname='mygisdb' user='postgres' password='whatever' schema='someschema' table=sometable"

To export data to other raster formats, use gdal_translate the below will export all data from a table to a PNG file at 10% size.

Depending on your pixel band types, some translations may not work if the export format does not support that Pixel type. For example floating point band types and 32 bit unsigned ints will not translate easily to JPG or some others.

Here is an example simple translation

gdal_translate -of PNG -outsize 10% 10% "PG:host=localhost port=5432 dbname='mygisdb' user='postgres' password='whatever' schema='someschema' table=sometable" C:\somefile.png

You can also use SQL where clauses in your export using the where=… in your driver connection string. Below are some using a where clause

gdal_translate -of PNG -outsize 10% 10% "PG:host=localhost port=5432 dbname='mygisdb' user='postgres' password='whatever' schema='someschema' table=sometable where='filename=\'abcd.sid\''" " C:\somefile.png

gdal_translate -of PNG -outsize 10% 10% "PG:host=localhost port=5432 dbname='mygisdb' user='postgres' password='whatever' schema='someschema' table=sometable where='ST_Intersects(rast, ST_SetSRID(ST_Point(-71.032,42.3793),4326) )' " C:\intersectregion.png

To see more examples and syntax refer to Reading Raster Data of PostGIS Raster section

13.7.

Are their binaries of GDAL available already compiled with PostGIS Raster suppport?

Yes. Check out the page GDAL Binaries page. Any compiled with PostgreSQL support should have PostGIS Raster in them. GDAL tools is also generally included as part of QGIS.

If you want to get the latest nightly build for Windows — then check out the Tamas Szekeres nightly builds built with Visual Studio which contain GDAL trunk, Python Bindings and MapServer executables and PostGIS Raster driver built-in. Just click the SDK bat and run your commands from there. http://www.gisinternals.com. Also available are VS project files.

13.8.

What tools can I use to view PostGIS raster data?

You can use MapServer compiled with GDAL to view Raster data. QGIS supports viewing of PostGIS Raster if you have PostGIS raster driver installed.

In theory any tool that renders data using GDAL can support PostGIS raster data or support it with fairly minimal effort. Again for Windows, Tamas' binaries (includes Mapserver) http://www.gisinternals.com are a good choice for windows users if you don’t want the hassle of having to setup to compile your own.

13.9.

How can I add a PostGIS raster layer to my MapServer map?

First you need GDAL 1.7 or higher compiled with PostGIS raster support. GDAL 3 or above is preferred since many issues have been fixed in 1.8 and more PostGIS raster issues fixed in trunk version.

You can much like you can with any other raster. Refer to MapServer Raster processing options for list of various processing functions you can use with MapServer raster layers.

What makes PostGIS raster data particularly interesting, is that since each tile can have various standard database columns, you can segment it in your data source

Below is an example of how you would define a PostGIS raster layer in MapServer.

note.png[Note]

The mode=2 is required for tiled rasters and was added in PostGIS 2.0 and GDAL 1.8 drivers. This does not exist in GDAL 1.7 drivers.

-- displaying raster with standard raster options
LAYER
    NAME coolwktraster
    TYPE raster
    STATUS ON
    DATA "PG:host=localhost port=5432 dbname='somedb' user='someuser' password='whatever'
        schema='someschema' table='cooltable' mode='2'"
    PROCESSING "NODATA=0"
    PROCESSING "SCALE=AUTO"
    #... other standard raster processing functions here
    #... classes are optional but useful for 1 band data
    CLASS
        NAME "boring"
        EXPRESSION ([pixel] < 20)
        COLOR 250 250 250
    END
    CLASS
        NAME "mildly interesting"
        EXPRESSION ([pixel] > 20 AND [pixel] < 1000)
        COLOR 255 0 0
    END
    CLASS
        NAME "very interesting"
        EXPRESSION ([pixel] >= 1000)
        COLOR 0 255 0
    END
END

-- displaying raster with standard raster options and a where clause
LAYER
    NAME soil_survey2009
    TYPE raster
    STATUS ON
    DATA "PG:host=localhost port=5432 dbname='somedb' user='someuser' password='whatever'
        schema='someschema' table='cooltable' where='survey_year=2009' mode='2'"
    PROCESSING "NODATA=0"
    #... other standard raster processing functions here
    #... classes are optional but useful for 1 band data
END

13.10.

What functions can I currently use with my raster data?

Refer to the list of Chapter 12, Raster Reference. There are more, but this is still a work in progress.

Refer to the PostGIS Raster roadmap page for details of what you can expect in the future.

13.11.

I am getting error ERROR: function st_intersects(raster, unknown) is not unique or st_union(geometry,text) is not unique. How do I fix?

The function is not unique error happens if one of your arguments is a textual representation of a geometry instead of a geometry. In these cases, PostgreSQL marks the textual representation as an unknown type, which means it can fall into the st_intersects(raster, geometry) or st_intersects(raster,raster) thus resulting in a non-unique case since both functions can in theory support your request. To prevent this, you need to cast the textual representation of the geometry to a geometry.

For example if your code looks like this:

SELECT rast
 FROM my_raster
   WHERE ST_Intersects(rast, 'SRID=4326;POINT(-10 10)');

Cast the textual geometry representation to a geometry by changing your code to this:

SELECT rast
 FROM my_raster
   WHERE ST_Intersects(rast, 'SRID=4326;POINT(-10 10)'::geometry);

13.12.

How is PostGIS Raster different from Oracle GeoRaster (SDO_GEORASTER) and SDO_RASTER types?

For a more extensive discussion on this topic, check out Jorge Arévalo Oracle GeoRaster and PostGIS Raster: First impressions

The major advantage of one-georeference-by-raster over one-georeference-by-layer is to allow:

coverages to be not necessarily rectangular (which is often the case of raster coverage covering large extents. See the possible raster arrangements in the documentation)
rasters to overlaps (which is necessary to implement lossless vector to raster conversion)

These arrangements are possible in Oracle as well, but they imply the storage of multiple SDO_GEORASTER objects linked to as many SDO_RASTER tables. A complex coverage can lead to hundreds of tables in the database. With PostGIS Raster you can store a similar raster arrangement into a unique table.

It’s a bit like if PostGIS would force you to store only full rectangular vector coverage without gaps or overlaps (a perfect rectangular topological layer). This is very practical in some applications but practice has shown that it is not realistic or desirable for most geographical coverages. Vector structures needs the flexibility to store discontinuous and non-rectangular coverages. We think it is a big advantage that raster structure should benefit as well.

13.13.

raster2pgsql load of large file fails with String of N bytes is too long for encoding conversion?

raster2pgsql doesn’t make any connections to your database when generating the file to load. If your database has set an explicit client encoding different from your database encoding, then when loading large raster files (above 30 MB in size), you may run into a bytes is too long for encoding conversion.

This generally happens if for example you have your database in UTF8, but to support windows apps, you have the client encoding set to WIN1252.

To work around this make sure the client encoding is the same as your database encoding during load. You can do this by explicitly setting the encoding in your load script. Example, if you are on windows:

set PGCLIENTENCODING=UTF8

If you are on Unix/Linux

export PGCLIENTENCODING=UTF8

Gory details of this issue are detailed in http://trac.osgeo.org/postgis/ticket/2209

13.14.

I’m getting error ERROR: RASTER_fromGDALRaster: Could not open bytea with GDAL. Check that the bytea is of a GDAL supported format. when using ST_FromGDALRaster or ERROR: rt_raster_to_gdal: Could not load the output GDAL driver when trying to use ST_AsPNG or other raster input functions.

As of PostGIS 2.1.3 and 2.0.5, a security change was made to by default disable all GDAL drivers and out of db rasters. The release notes are at PostGIS 2.0.6, 2.1.3 security release. In order to reenable specific drivers or all drivers and reenable out of database support, refer to Section 2.1, “Short Version”.

ST_DFullyWithin

Home

Chapter 14. PostGIS Extras