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

[Pierre Racine]

> 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?

Pixel selection is possible via three options:

1) one raster ST_MapAlgebra

2) ST_Reclass

3) ST_DumpAsPolygons (with a where clause)

> 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?

Certainly not new columns. New table or new band. Performance test needed.

> 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?

It is possible to build a distance raster in the database. It is a slow operation. But if your data is kind of static you can do it outside. What do you want exactly? The nearest pixels to a rail?

> ### 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?

Why turn it into a new (temporary) table? We don't have much experience with GeoServer but if you can do any SQL query you can create a new raster and make it a JPEG. See http://www.bostongis.com/blog/index.php?/archives/175-Minimalist-Web-based-PHP-PostGIS-2.0-Spatial-GeometryRaster-Viewer.html

> ### 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?  

right. A two raster MapAlgebra.

> 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.

Try the new optimized callback map algebra: 

http://postgis.net/docs/manual-dev/RT_ST_MapAlgebra.html

You might have to upgrade as it was optimized very recently.

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

Test needed. If the pixel selected do not involve all the tiles of a tiled raster you might gain a lot by tiling.

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

I would say it depends on the number of pixel selected. Choosing between doing geoprocessing in rasters mode, in vectors mode or in array mode is always a threshold. If the number of pixel to threat is low it might be very efficient to convert them to polygons (ST_DumpAsPolygons) and work in vector mode, in array mode or just as a temporary table of numbers and x and y coordinates. I don't see think you might gain much by transferring everything out and writing your own code to do what PostgreSQL is able to do.

Pierre