[postgis-users] Rasters

[rb]

OK,
I am presenting two brief cases that I would use in creating a
hydrologic model, one featuring landuse, and the other featuring a soil
properties matrix, and employing a couple of different aggregation
functions. 

I am not sure of the terminology "atomic unit" but I will assume it
means the smallest unit, which in my cases I will assume to be 10 meter
by 10 meter cells. 

Also, I am not entirely sure of the data structure, so if this comes out
a litle disjointed as to how the SQL aggregations would work. For
example, the whole aggregate/grouping procedure would be dicey with this
sort of contraption because the raster values essentially become an
array within the table, even if they are stored in some file external to
the database, so if I have gotten the aggregate function theory wrong,
consider this a rough draft.

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





On Thu, 2005-09-29 at 11:39, Paul Ramsey wrote:
> OK, raster people.  My problem is that I cannot envision the use  
> cases for raster in the database, so, make it easy for me! :)
> 
> - What is the atomic unit of storage for rasters in the database?  Is  
> it complete scenes?  Something else?
> - What is some SQL I would apply against my raster tables?  Make up  
> some descriptive function names and show me some example SQL and tell  
> me what it does.
> - How do vector data and raster data interact?  Note the atomicity  
> question above.
> 
> P.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-- 
Non-point Source Data Analyst
University of Maryland, College Park
Chesapeake Bay Program Office
410 Severn Avenue, Suite 305B
Annapolis, MD, 21403
Phone: (410) 267-5779

rburghol at chesapeakebay.net