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.
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
Gives error: Warning: pg_query(): Query failed: ERROR: rt_raster_to_gdal: Could not load the output GDAL driver.
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?
Every one or two months, someone asks on the PostGIS users list usually somewhat panicked, My geometry is missing when I look in pgAdmin III
This is documented in the PostGIS Frequently Asked questions section of the manual I tried to use PgAdmin to view my geometry column and it is blank, what gives?
Since this question gets asked so much, I thought it best to highlight it again.
Spatial objects can be big things and if your geometry, geography, or raster row field is big enough, pgAdmin is not going to load it
and will just show an empty field. This is by design. There are a couple of ways to verify you really have data and even how big your geometry, geography or raster column is