Author: Regina Obe 2016/03/13 in tips ( newbie, raster )
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.
The question goes something like this
I have this raster measuring contaminent levels of ammonia in the pixel values in band 1, and I want to rank contamination by 0 low, 1 medium, 2 high. Then I want to find the area of this contamination or do some other crazy geometric thing to it.
The basic strategy is to reduce your raster into a simpler one that contains 0s, 1s, 2s where 0 ends up being marked as nodata
. Then you mark off 0 (or whatever number you choose) as nodata
. In the end you end up with a fairly simple raster that is easy to vectorize or keep as raster and do stats on.
So for example here we use ST_Reclass to reclassify all pixel values >= 0 and <= 90 as 0, >90 < 100 as 1, and 100 to 1000 as 2.
The last argument makes value of 0 represent nodata
.
SELECT ST_Reclass(rast, 1, '[0-90]:0,(90-100):1,[100-1000):2', '4BUI', 0) AS rast FROM sometable WHERE filename = '123.tif';
Will give you a new set of rasters that have pixel values of 1,2 and nodata
.
Now the next part of the question goes, how do you do geometric operations on this new set, like for example computing the centroid in this case of each toxic level area
WITH cgeoms AS ( SELECT ST_DumpAsPolygons( ST_Reclass(rast, 1, '[0-90]:0,(90-100):1,[100-1000):2', '4BUI', 0), 1 ) AS gval FROM sometable WHERE filename = '123.tif' ) SELECT ST_Centroid( ST_Union( (gval).geom ) ) AS geom, (gval).val FROM cgeoms GROUP BY (gval).val;
There are also many stats you can do with functions such as ST_Histogram
and ST_ValueCount
so you do not
have to resort to vectorizing your raster to do basic stats on it.
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”