[GRASS-dev] 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]

[Moritz Lennert]

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

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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: d.vect.chart.sql.tgz
Type: application/x-gtar
Size: 6489 bytes
Desc: not available
Url : http://lists.osgeo.org/pipermail/grass-dev/attachments/20061010/74fdb5d4/d.vect.chart.sql.gtar