[postgis-users] Rasters

[Paul Ramsey]

Rob,

On Sep 29, 2005, at 9:59 AM, rb wrote:

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

I mean what is a row in a table in the database?  So for vector, the  
unit is a "feature": a point, line or polygon.  What is that unit for  
raster?

Your example is interesting, and begins to get into an area where  
PgSQL is potentially "useful" in the raster domain (depending on ones  
definition of "useful") and that is as an integration environment.

As a slight digression, a local company in Victoria made a fair bit  
of money with a "datablade" for Informix (just like a PgSQL  
extension, really) that allowed people to "use multidimensional  
rasters in the database".  Here is the thing though, each raster file  
was 1-2 Gb in size, and the atomic unit in the database was one row  
per raster file.  And then there were a whole bunch of functions for  
doing neato things like generating profiles based on paths through  
the raster, or slices, or so and so.  But really, there was nothing  
being done that could not have been done in the same way with little  
commandline utilities that just operated on the raw files  
themselves.  There was a perceived benefit to the customers, who paid  
good money for this stuff, presumably in that the SQL environment  
operated as an integration space.  You could do this, that, and the  
other with the datablade, and then do that, them, and whoever with  
other SQL functions from other database modules on the resultants of  
the previous work.  And the more stuff you plugged into the SQL  
environment, the more powerful all the related stuff became.  Network  
effects, in much the same way that Perl is a trivial little language,  
but extremely powerful due to the network effects of the CPAN archive  
of utility modules.

PgSQL is much like that, in that once can take the results of a  
spatial data summary (a tuple set) and feed it into a PL/R summary  
routine, and feed the results of that out into some general PgSQL  
functions.  So one could, with the right model, allow raster  
operations to be bound and exposed via SQL and be able to start  
chaining, from PostGIS inputs to rasters, to raster math, to  
summaries, to PL/R, to final output tuples.

There are lots of nasty cracks though, and "raster in a database"  
means radically different things to different people.  It could mean  
the above, which is sort of GRASS-in-SQL.  Or it could mean what most  
geospatial people think, which is "big image archive in a  
database" (which I think is a painful waste of effort), or it could  
mean what the Informix people mean for their datablade, which is a  
combination of the two rationales ("databases are 'better'" + "SQL is  
a common language everyone can use to do this otherwise complex stuff").

I am afraid I am not clarifying the problem at all, but I think that  
is because the problem means different things to different people.   
Take Gerry for example... :) no really, take him, please :)

P.

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>