[GRASS5] Re: intersect sites with polygons?

[Christoph Simon]

On Fri, 5 Jul 2002 15:18:54 +0200
Radim Blazek <blazek at itc.it> wrote:

> On Friday 05 July 2002 12:39 pm, Christoph Simon wrote:
> > The classic SQL example for an aggregate functin is:
> >
> > 	select max (val) from tab where ...;
> >
> > this will return just one row, so it's not going to be typical as an
> > attribute. 
> 
> Select object with category with max value? Not frequent, but say that
> wee want see tha last added feature. Should be possible in this way:
> where cat = (select max (cat) from tbl); 

Max is a classic example for an aggregate function. That's why I
mentioned it. Right now, I'm not sure to use the terms attribute and
category correctly. In a DEM, say we have 15 different elevation
values, so each would be one of 15 categories, but then, we might have
an association such that category 5 means 250m, which would be the
attribute. Is this correct? In case it is, I was talking about
attributes, not categories.

> > OTOH, SQL generally and Postgres in particular allows to
> > write SQL and C-language based functions, which can do more or less
> > everything: Someone might have a map associated with one or more
> > tables and one column for each set of climate data measured. This is
> > the point, where she could experiment with different algorithms to
> > predict future values, compare it to already known ones and compute an
> > index of quality of prediction which would be displayed in the map.
> 
> Example of SQL statement for this?

	SELECT myfunc (col1, col3, col7) FROM tableX WHERE ....;

(Of course, myfunc() needs to have been defined before, but this
varies from RDBMS to RDBMS and is usually persistent to different SQL
sessions.) Then, SQL would call myfunc() for each row included by the
WHERE clause, and myfunc() would return a value which represents a
computed (aggregate) column.

Here col1, col3, col7 could be the different climate values at three
different moments. Following the above example, myfunc() would use
col1 and col3 to predict what should have happend at the moment stored
in col7. After applying a prediction algorithm to these two columns,
the result is compared with the value of col7, computing a number with
reflects the degree of match in some useful scale, or maybe forming
cluster ranges. Then you can draw a map where each cluster range is
associated with a certain color. Or you take the individual values and
create a height field, showing in a 3D space where the algorithm was
specially good or bad. This way you have a visual impact over the
quality of the algorithm you applied initially in myfunc.

> > One thing I use pretty frequently, which is not an aggregate function
> > but which also requires access to the first part are more or less
> > simple arithmetic operations;
> >
> > 	select inches * 25.4 AS milims from...
> 
> Yes, but it does not return list of categories, i guess?

See above. If my definition is right, it would be an attribute, not a
category. But even so, linking different tables with or without
aggregate functions, the result could also be a category. A very
simple example could be some scaling or forming of cluster ranges
using modula or division. I think the difference depends on how the
user defines it.

The whole point is, that I don't believe that you can freeze some
parts of an SQL statement which are actually variable. If you do,
you'll lose SQL power. And setting the columns globally is essentially
a freeze, because if I would have to change it all the time, I would
prefer to do so directly in the statement. OTOH, using such a global
value as default still could be a good idea, as I agree that there
will be many cases, where you don't use that additional power.

-- 
Christoph Simon
ciccio at kiosknet.com.br
---
^X^C
q
quit
:q
^C
end
x
exit
ZZ
^D
?
help
.