[postgis-users] Best practices for PostGIS raster query and processing from web app

[Tim Meehan]

Hi All,

We are building a web app where the user (hopefully) will (1) use an
ExtJS/OpenLayers client to build a query of multiple PostGIS rasters to
locate pixels with a combination of characteristics, (2) view the
'selected' focal pixels to make sure they got what they wanted, (3) specify
a calculation on focal pixels that will be conducted within the postgis
database, then (4) view a raster map of the results in the client via
Geoserver/OpenLayers. We are new to processing rasters in PostGIS databases
and are looking for advice on how to do it efficiently.  Here are the
questions - following a few key details about our test database.

Details: The test database has four single-band rasters as tables and a
vector layer as a table.  There is a 'slope_raster' table (~ 4000 rows by
4000 cols, no tiles, values 0.0 to 100.0, SRID 3174), a 'landcover_raster'
table (same, snapped to previous layer, values 1 to 12), a 'soil_carbon_1'
raster table (same, values 0.0 to 50.0), a precomputed 'distance_to_rail'
raster table (same, values roughly 0.0 to 100.0), and a 'railroads' vector
layer. The software is PostgreSQL 1.16.1, PostGIS 2.0.3, QGIS 1.8.

### Q1. From our naive perspective, it seems that a good way to "select"
focal pixels might involve something like:

return a new raster table of 1s and 0s named 'temp_selected_raster',
where, for example, 'slope_raster' > 10, 'landcover_raster' = 2, and
'distance_to_rail' <= 30

a. How would you turn this pseudocode into a PostGIS SQL query so this
action is done quickly (we are not particularly skilled at SQL and new to
PostGIS raster processing functions)?  That is, should we use an
intersection approach or a map algebra approach?  What would the SQL look
like?

b. Should we create a new table, or a new column in an existing table (we
have figured out how to view a new table in QGIS, but not a new column)?
Maybe you will suggest new bands.  We are still not clear on when/how to
use/view tables versus, columns, versus, bands.  Any suggested reading?

c. Should we precompute a raster of distances to rail using a GIS, so that
the distance part of the query operates on a raster instead of combining
rasters and vectors (railroads) in the same query?


### Q2. Is it sensible to have a query lead to a new raster table, then use
PostGIS to turn the table into an image that is sent to the client via
Geoserver?  Any other suggestions?


### Q3. Once the user is happy that they have selected the correct focal
pixels, should we use that selection as a sort of mask to process another
raster layer within PostGIS?  For example does this seem sensible:

now, return a new table named 'soil_carbon_2'
where 'temp_selected_raster'  is 1,
the calculation is soil_carbon_1 * 0.80

a. This is a map algebra exercise, right?  What would the SQL look like?
We have experimented with simple map algebra expressions, following the
examples in chapter 9 of the PostGIS manual.  We tried

ALTER TABLE landcover_raster ADD COLUMN map_rast raster;
UPDATE landcover_raster SET map_rast =
ST_MapAlgebraExpr(rast,NULL,'[rast]+1') WHERE rid = 1;

This took 85 seconds in PostGIS.  An equivalent operation with the raster
calculator in QGIS took less than 1 second.  Is this because we need to
tune tile size?  Thus far all rasters in our database are not tiled.

b. Is there an optimal tile size that accommodates both the raster math
(here) and the spatial query (above)?

c. Should we do this stuff on arrays outside the database?


Thanks for your help!  Your insight will save us a LOT of work.

Best,
Tim
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130408/b4bd27e5/attachment.html>