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 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?
Get a distinct pixel value list for band 1 of all raster tiles
Specifying the band number is optional and defaults to 1 if not specified. I like to specify it just cause I work a lot with multi-band rasters. In this case I just have digital elevation data table called dem.
SELECT DISTINCT (pvc).VALUE FROM (SELECT ST_ValueCount(dem.rast,1) AS pvc FROM dem) AS f ORDER BY (pvc).VALUE;
Now if you are using PostgreSQL 9.3+, you can make this even shorter by using the LATERAL clause (since LATERAL keyword is optional in most cases, you can skip saying LATERAL and write it like this)
SELECT DISTINCT (pvc).VALUE FROM dem, ST_ValueCount(dem.rast,1) AS pvc ORDER BY (pvc).VALUE;
Get a pixel value list for band 1 and total pixels for an area of interest
Now if you have a huge coverage, chances are you only care about a particular area, not the 3 million tiles you have.
You might want to also know
how many times the value appears just for your area of interest
So you really want to combine your arsenal with
Here I am using the 9.3 LATERAL short-hand
SELECT (pvc).VALUE, SUM((pvc).COUNT) AS tot_pix FROM dem INNER JOIN ST_GeomFromText('LINESTRING(-87.627 41.8819, -87.629 41.8830)' , 4326) AS geom ON ST_Intersects(dem.rast, geom), ST_ValueCount(ST_Clip(dem.rast,geom),1) AS pvc GROUP BY (pvc).VALUE ORDER BY (pvc).VALUE;
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.
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/Postgres.app/Contents/Versions/9.5/share/postgresql/extension/postgis_sfcgal.control
as in this question: SFCGAL in PostGIS problem.
There are two main causes for this:
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.