[GRASS-dev] Re: Proof of concept for more complete database access in GRASS module [was: Re: [GRASS-user] RE: Problem querying layers other than '1' in gi s.m]

[Trevor Wiens]

On Tue, 10 Oct 2006 14:45:59 +0200
Moritz Lennert wrote:

> Trevor Wiens wrote:
> > On Tue, 26 Sep 2006 23:35:25 -0700
> > Michael Barton wrote:
> > 
> > -snip-
> >> There seems to be some confusion in your statement about not liking GRASS as
> >> a database and so switching to PostgreSQL. Perhaps what you really mean is
> >> that you find dbf limiting for attribute management and prefer PostgreSQL. 
> > 
> > That is not what I said. What I said was that because I find attribute
> > management awkward in GRASS I use PostGIS (the PostgreSQL equivalent
> > to Oracle Spatial) for most vector work unless I need features in
> > GRASS not found in PostGIS. In comparisons for operations such as
> > overlays or point in polygon searches, GRASS is slightly faster than
> > PostGIS, but in most cases the performance hit hasn't been significant
> > and in the end, it is much faster to use PostGIS instead of GRASS
> > because I don't have to import and export and reconnect attribute data.
> > I get the results I want in a single query. I would like to have that
> > same kind of control within GRASS.
> 
> 
> Trying to take this a bit further, I have implemented a version of the 
> just posted d.vect.chart2 
> (http://grass.itc.it/pipermail/grass5/2006-October/026503.html) in a way 
> that it allows an arbitrary SQL query as source of the displayed data 
> (attached). This module (called d.vect.chart.sql for the purpose of this 
> proof of concept) contains an sql option which allows the user to use 
> any arbitrary sql query to define the data to be charted. This query has 
> to return the data in a certain order. A -s flag indicates that the 
> query also returns a sizecol and the user has to tell the module how 
> many data columns there are (this could probably be handled using the 
> SQL parser, but I didn't think it important at this stage).
> 
> So, you can run it as such
> 
> d.vect.chart.sql -s map=communes ncols=1 scale=0.0001 sql="select 
> t1.pop_2001, t2.pop_2001, t1.comcod from bel_espon143 as t1, commcen as 
> t2 where t1.comcod=t2.comcod order by t1.pop_2001 desc "
> 
> which shows that you can combine any table you want as long as the query 
> results correspond to the required format.
> 
> This module is only proof of concept and certainly needs improvements 
> and checks, but it works for me in its current state.
> 
> Trevor is this more in line with what you are looking for ? Maybe we 
> should think about reworking more modules to allow such an arbitrary sql 
> query at least as an option ?
> 

Moritz,

Thanks for doing this. Hopefully I'll have time to download it and
play with tonight. I was really wanting to pickup the gauntlet Radim had
thrown down regarding implementing v.overlay according to my scheme,
but I've got deadlines looming and not had any time to do any hacking
at all.

Yes, this is part of what I envision. I see two functions for
arbitrary queries. First, as a vector object filter system to
replace the 'layers feature' and second, as a means to provide
more control over the creation of attribute tables for functions
creating new vector files such as v.overlay, or v.buffer, etc.

I wonder now how many steps it takes to recreate your arbitrary query
graph with 'layers' and I wonder about performance differences between
the two.

T

> Moritz
> 
> **************************
>  > d.vect.chart.sql -h
> 
> Description:
>   Displays charts of GRASS vector data in the active frame on the 
> graphics monitor based on an arbitrary sql query
> 
> Keywords:
>   display
> 
> Usage:
>   d.vect.chart.sql [-csr] map=name [type=string[,string,...]]
>     [layer=value] sql=string ncols=value [ctype=string] [size=value]
>     [scale=value] [ocolor=string] [colors=string[,string,...]]
>     [max_ref=value[,value,...]]
> 
> Flags:
>    -c   Center the bar chart around a data point
>    -s   SQL query contains size column
>    -r   use square root of sizecol (to make area of pie, not diameter, 
> proportional to sizecol)
> 
> Parameters:
>        map   Name of input vector map
>       type   Type
>              options: point,line,boundary,centroid,area
>              default: point,line,boundary,centroid
>      layer   Layer number
>              default: 1
>        sql   SQL statement retrieving the data in the following order: 
> data columns, [size column], key column
>      ncols   Number of data columns (without size column)
>      ctype   Chart type
>              options: pie,bar
>              default: pie
>       size   Size of chart (diameter for pie, total width for bar)
>              default: 40
>      scale   Scale for size (to get size in pixels)
>              default: 1
>     ocolor   Outline color
>              default: black
>     colors   Colors used to fill charts
>    max_ref   Maximum value used for bar plot reference
> 


-- 
Trevor Wiens 
twiens at interbaun.com

The significant problems that we face cannot be solved at the same 
level of thinking we were at when we created them. 
(Albert Einstein)