Posts in category 'tips'

Causes for 'postgis.backend' is already set

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”

Read More…

Selecting only pixels of particular range of values with ST_Reclass

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, where ST_Reclass does the same thing but orders of magnitude faster.

Read More…

Why can't I do CREATE EXTENSION postgis_sfcgal

PostGIS 2.2.0 came out this month, and the SFCGAL extension that offers advanced 3D and volumetric support, in addition to some extended 2D functions like ST_ApproximateMedialAxis became a standard PostgreSQL extension and seems to be a fairly popular extension.

I’ve seen several reports on GIS Stack Exchange of people trying to install PostGIS SFCGAL and getting things such as

ERROR: could not open extension control file /Applications/

as in this question: SFCGAL in PostGIS problem.

There are two main causes for this:

Read More…

Enabling PostGIS raster drivers and out-of-db raster support

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

    FROM  sometable;

Gives error: Warning: pg_query(): Query failed: ERROR: rt_raster_to_gdal: Could not load the output GDAL driver.

Read More…

Getting distinct pixel values and pixel value counts of a raster

PostGIS raster has so so many functions and probably at least 10 ways of doing something some much much slower than others. Suppose you have a raster, or you have a raster area of interest — say elevation raster for example, and you want to know the distinct pixel values in the area. The temptation is to reach for ST_Value function in raster, but there is a much much more efficient function to use, and that is the ST_ValueCount function.

ST_ValueCount function is one of many statistical raster functions available with PostGIS 2.0+. It is a set returning function that returns 2 values for each row: a pixel value (value), and a count of pixels (count) in the raster that have that value. It also has variants that allow you to filter for certain pixel values.

This tip was prompted by the question on stackexchange How can I extract all distinct values from a PostGIS Raster?

Read More…