[GRASS-dev] Re: [GRASS-user] v.db.* commands in GRASS-7

[Moritz Lennert]

On 12/04/12 12:29, Andres Kuusk wrote:
> In the manual I read: It is possible to link the geographic objects in a
> vector map to one or more tables.
> All vector objects are kept in one geometry file, and topology is
> maintained for all vector objects together. GRASS layers only consist of
> links to different attribute tables in which vector objects can have zero,
> one or more categories.

I personally do not like the idea of calling layers "links to different 
attribute tables" as IMHO this is only half the story. Layers are a 
characteristic of the vector feature (geometries) file and they are not 
necessarily linked to attribute tables. Categories are identifiers for 
vector features allowing you to give either a unique id to each feature 
or to group similar features by giving them all the same id. Layers 
allow you to group features in different manners. For example, you might 
have sample points for which on one layer you have unique ids, and on 
another layer ids corresponding to specific types of samples.

Or, as discussed in the example at 
http://grass.osgeo.org/wiki/GRASS_Vector_Layers, you can have roads with 
one layer containing the unique id of each road and another layer with 
ids for specific routes that one might take, combining several roads 
with the same id.

Obviously, these issues can also be handled via attribute tables, so the 
advantage of doing this through layers is disputable.

However, you can also use this system to identify different types of 
features in a same map. For example, if you have a series of polygons 
representing fields and at the same time, the boundaries of these fields 
have a meaning as linear features, e.g. as paths, then you can give a 
unique id to each field as area in layer 1, and a unique id in layer 2 
to those boundary lines that are paths. This is more complicated to deal 
with through attribute tables, but if your paths will always depend on 
the field boundaries (and might change if these boundary paths changes) 
then keeping them in the same map can be helpful.

Not every feature in a map necessarily has a category value in each 
layer. So, taking the example of the fields and paths, only some 
boundaries, but not all, might have a category value in layer 2.

Optionally, each layer can be (but does not have to) be linked to an 
attribute table. The link is made by the category values of the features 
in that layer which has to have a corresponding entry in the specified 
key column of the table.

> I link two attribute tables to a vector of area polygons.
> In layer 1 I see area polygons and I can use selection with where
> conditions on the attribute table 1.
>
> In layer 2 the same polylines are boundaries and selection in the
> attribute table 2 does not work. Maybe I am doing something wrong?

What do you mean by the same polylines are boundaries ? Did you decide 
this ? How ? Are you sure that your features have category values in 
layer 2 ? What is the result of v.category option=report ?

> Another problem.
> Command v.db.join returns error.
> I am trying to join two attribute tables (era2011 linked to the vector of
> area polygons era2011 with key=cat, and erb2011) of the same size:
>
> % v.db.join map=era2011 layer=1 column=cat otable=erb2011 ocolumn=erid
>
> Column cat in table era2011 is of type integer.
> Column erid in table erb2011 is of type smallint.
>
> I got an error:
>
> Adding column <erid> to the table
> DBMI-PostgreSQL driver error:
> Unable to execute:
> ALTER TABLE era2011 ADD COLUMN erid INTEGER(2)
> ERROR: syntax error at or near "("
> LINE 1: ALTER TABLE era2011 ADD COLUMN erid INTEGER(2)
> ERROR: Error while executing: 'ALTER TABLE era2011 ADD COLUMN erid
> INTEGER(2)'
> ERROR: Unable to add column <erid INTEGER(2)>.
> ERROR: Error creating column <erid>
>
>
> The same error (syntax error at or near "(") I got in case the
> integer columns in the otable are of type integer (integer(4)).

AFAIK, PostgreSQL does not have integer types with length definition in 
parantheses. However, in the script, there is:

91	        if len(col) > 2 and driver != "sqlite":
92	            coltype = "%s(%s)" % (col[1], col[2])

I have the feeling that this is a bit too simplistic: I don't think that 
SQLite has abandoned notations such as varchar(25), so maybe excluding 
this type of notation for all data types might be a bit radical, and at 
the same time, there are other DB backends that don't support 
parentheses in notations of other data types.

So this issue probably has to be treated differently. Please file a bug.

In the meantime, you can can try to work around this issue by modifying 
the v.db.join script with something like this:

91	        if len(col) > 2 and driver != "sqlite" and col[1] != 'INTEGER':
92	            coltype = "%s(%s)" % (col[1], col[2])


>
>
> The v.db.join works if the first table (era2011) already has empty columns
> which correspond to the columns of the otable (erb2011).
>
> Does this mean that I have first to add columns to the attribute table
> of layer 1 and after that the v.db.join can be used for updateing
> attribute values in these columns?

If columns of the same name already exist in the target table, then 
values seem to be overwritten in these existing columns. I'm not sure I 
find this behaviour very reassuring concerning data integrity in the 
target table.

All in all, I think that v.db.join needs some more love ;-)

Moritz