I’ve been asked this question in some shape or form at least 3 times, mostly from people puzzled why they get this error. The last iteration went something like this:
I can’t use
ST_AsPNG when doing something like
SELECT ST_AsPNG(rast) FROM sometable;
Gives error: Warning: pg_query(): Query failed: ERROR: rt_raster_to_gdal: Could not load the output GDAL driver.
So why does this happen? This has been an issue since our release of 2.0.6/2.13 PostGIS security patches that by default disabled all raster drivers and out-of-db raster storage. Raster is much less useful if you don’t have any raster drivers enabled, so you need to renable them or the selective ones you need.
Determine which drivers you have enabled
The first step is to figure out which ones you do have enabled if any. You can determine that by using the
SELECT short_name FROM ST_GDALDrivers();
Enabling drivers pre-PostGIS 2.2 If you get no records back, then you have the default configuration of all drivers being disabled. You need to set some environment variables either in your postgres startup script or system environment. The process varies a lot from platform to platform and how you installed PostgreSQL. This is detailed a bit in the docs.
In order to enable all or the ones you need, refer to: Installation Short version. For windows users, who are using the installer, the installer at the end has prompts asking you if you want to enable drivers and enables the most common ones.
Coming in PostGIS 2.2 Raster GUCS In the not yet released version of PostGIS 2.2 which we hope to ship come August or September 2015, some PostgreSQL PostGIS-specific GUCs have been introduced which allow you to do this in a cross-platform way using PostgreSQL GUC machinery. If you have PostgreSQL 9.4, you can use the new ALTER SYSTEM construct to set this system whide instead of in a specific database or session.
As of PostGIS 2.3, the postgis extension was changed to no longer allow relocation. All function calls within the extension are now schema qualified.
While this change fixed some issues with database restore, it created the issue of if you installed PostGIS in a schema other than the one you wanted to it is not intuitive how to move it to a different schema. Luckily there is a way to do this.
For this exercise, I will install PostGIS in the default schema and then demonstrate how to move it into another schema location.
You can run these steps using psql or pgAdmin or any other PostgreSQL tool you want.
The error ‘postgis.backend’ is already set comes up every so often in PostGIS mailing list. The issue arises often during or after an upgrade. I’ll go over causes for this I am aware of and how to fix.
The question goes something like this
After upgrading to Postgis 2.3 from 2.1, my server log is filled with these messages :
“WARNING ‘postgis.backend’ is already set and cannot be changed until you reconnect”
This raster question comes up quite a bit on PostGIS mailing lists and stack overflow and the best answer often involves
the often forgotten
ST_Reclass function that has existed since PostGIS 2.0.
People often resort to the much slower though more flexible
ST_MapAlgebra or dumping out
their rasters as Pixel valued polygons they then filter
with WHERE val > 90,
ST_Reclass does the same thing but orders of magnitude faster.