[postgis-tickets] r14781 - ST_Reclass tip

[Regina Obe]

Author: robe
Date: 2016-03-13 12:14:25 -0700 (Sun, 13 Mar 2016)
New Revision: 14781

Added:
   web/_content/posts/2016/03-13_tip_pixels_of_range_value.html
Log:
ST_Reclass tip

Added: web/_content/posts/2016/03-13_tip_pixels_of_range_value.html
===================================================================
--- web/_content/posts/2016/03-13_tip_pixels_of_range_value.html	                        (rev 0)
+++ web/_content/posts/2016/03-13_tip_pixels_of_range_value.html	2016-03-13 19:14:25 UTC (rev 14781)
@@ -0,0 +1,56 @@
+---
+title: Selecting only pixels of particular range of values with ST_Reclass
+layout: post
+category: tips
+tags: [newbie, raster]
+author: Regina Obe
+thumbnail: postgis.png
+---
+<--markdown-->
+
+This raster question comes up quite a bit on PostGIS mailing lists and stack overflow and the best answer often involves
+the often forgotten <a href="/docs/RT_ST_Reclass.html" target="_blank">```ST_Reclass```</a> 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.
+
+<!-- pagebreak -->
+
+The question goes something like this
+
+<em>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.</em>
+
+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```.
+
+{% geshi 'sql' %}
+SELECT ST_Reclass(rast, 1, 
+	'[0-90]:0,(90-100):1,[100-1000):2', '4BUI', 0) As rast
+    FROM  sometable
+    WHERE filename = '123.tif';
+{% endgeshi %}
+
+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
+
+{% geshi 'sql' %}
+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.
+{% endgeshi %}
+
+