[GRASS-user] RE: Problem querying layers other than '1' in gi s.m

[Moritz Lennert]

Dylan Beaudette wrote:
> On Wednesday 20 September 2006 06:44, Patton, Eric wrote:
>> Moritz:
>>> You do not have to duplicate the points, but IIUC you have to assign a
>>> category value for each point in each layer. (See
>>> http://mpa.itc.it/markus/grass63progman/Vector_Library.html#vlib_categor
>>> ies_layers)
>> I'm probably misunderstanding the use of layers in general, but I thought
>> it was analogous to 'joined tables' in Arc (one geometry, many tables).
>>
>> Thinking about how I linked the tables to the vector, I just realized how I
>> may have made a mistake. The vector in question is a series of points
>> representing sample locations. Layer one contains a table with cat,
>> Timestamp, Easting and Northing. I wanted to create a second layer linking
>> to a table containing biological analyses of the sample data. The only
>> column in common between layer one and layer two is the timestamp field. Do
>> I also need a field in layer 2 telling what cat in layer 1 it belongs to?
>> Maybe that is why I can't query anything from layer 2. If I am right here,
>> how can I construct an SQL statement to populate layer 2 with the
>> corresponding cat from layer 1 where the timestamps in each table match?

It depends: if you timestamps are integers, they could be used as 
category values for layer 2. Otherwise you have to go Dylan's way.

> Eric, the way that i usually approach this type of problem in GRASS is as 
> follows:
> 
> 1. store my attribute data in a RDBMS such as mysql, postgresql, sqlite, etc.
> 2. create some vector data with at least two columns: 'cat' and 'id'
> 'cat' is used by v.db.connect to associate a row with a vector feature
> 'id' is used to associate other attribute data with a row
> 
> 3. create a new, composite  table (from two existing tables 'vector' 
> and 'data') with something like:
> create table new_table as 
> select vector.cat, vector.id, data.time, data.temp
> from vector, data
> where vector.id = data.id ;


As I mentioned earlier, you can also just create a view:

create view new_view as
select vector.cat, vector.id, data.time, data.temp
from vector, data
where vector.id = data.id ;

That way you won't clutter your disk with a multitude of tables you 
sometimes only use once. The only disadvantage of views is that they are 
recalculated at every use, so if the tables are large and the 
calculation of the view takes a lot of time, it might be better to 
create a table as Dylan suggests.

An advantage of a view is if you erase the map, only the view get's 
erased, not the tables...

> 
> 4. then hookup new_table to the original vector file with v.db.connect

When you link the map to the view you will get an warning message about 
"this table does not exist", or something like this, but you can ignore 
this (should probably be made more clear).

Moritz