[postgis-users] Raster Support...

[Robert Burgholzer]

>To repeat:  once you get your rasters in the database, what are you  
> going to do with them?

I keep finding new reasons to have some sort of raster support in
PostGIS.
In my most recent case, I have need to turn PostGIS shapes into rasters,
in order to submit some data that is tabular as input to a model that is
raster based. I think that being able to do it on the fly in PostGIS,
and then maybe serving it up to the client via WMS or some such thing
would be good. I am definitely interested in lending a hand on this,
although in my previous efforts, I have not really been able to get my
head around the available libraries and such that could facilitate this,
other than thinking that GRASS libraries could for a basis. Also, I
think that the problems that I have would not require the raster data to
be stored inside the db, but could be accomplished by an interaction
between the vector db entity and a file based raster.

In addition to my current desires, I will quote myself from previous
postings in response to a similar question:

March 2004:
My idea is to store raster in files and allow postgis SQL commands to
perform "zonal summaries" and the like, for example, I would like to
take a raster containing slope information, and compute the average
slope for a set of overlapping vectors in a PostGIS database.

September 2005:
VECTOR RASTER SQL HYPOTHETICAL SCENARIO:
I have 3 tables I am working with, a vector shape table watershed
(the_geom, watershedid), and two raster tables:

TABLES:
watershed
   the_geom
   watershedid - the unique name or code of each watershed

rasterlanduse
   the_geom,
   landuseclass - an integer code value for land use for each cell

rastersoils
   the_geom,
   infiltrationrate - a floating point value for each cell

VECTOR/RASTER FUNCTIONS:
RVSummarize([vector_geom], [raster_geom], [column to summarize],
[summary function: MIN, MAX, AVG, STDEV])
   - this function returns what is known in raster as a zonal summary.
In desktop gis programs this can be done raster to raster and vector to
raster. Basically, it performs a summary of the selected operation type
on the cell values of a given raster area (zone)

RVOverlapArea2d(a.the_geom, b.the_geom) - returns the overlapping area
from the intersection of a vector and raster. This too could be an
aggregate function and subject to grouping.



CASE #1: I want to determine the area of all land uses in my set of
vector shapes, which represent small watersheds:

select a.watershedid, b.landuseclass, RVOverlapArea2d(a.the_geom,
b.the_geom) as luarea
from watershed table as a, rasterlanduse as b
where a.the_geom && b.the_geom and RVOverlapArea2d(a.the_geom,
b.the_geom) > 0.0
group by a.watershedid, b.landuseclass;

this returns a list like this:
watershedid | landuseclass | luarea
-----------------------------------
1              | Urban Perv.   | 10.0
1              | Pasture        | 24.6
1              | Forest          | 123.67
1              | Urb. Imperv.  | 6.7
2              | Urban Perv.   | 74.1
2              | Pasture        | 2.8
2              | Forest          | 73.9
2              | Urb. Imperv.  | 16.1


CASE 2: I want to compute the soil infiltration value for each watershed
from a raster soil property matrix:

SQL:
select a.watershedid, RVSummarize(a.the_geom, b.the_geom,
'infiltrationrate', 'average') as infilt
from watershed table as a, rastersoils as b
where a.the_geom && b.the_geom and RVOverlapArea2d(a.the_geom,
b.the_geom) > 0.0
group by a.watershedid;

this returns a list like this:
watershedid | infilt
--------------------
1              | 0.23
2              | 0.17