[GRASS-user] v.db.connect, categories, and postgresql database queries

[Michael Barton]

Bill,

Give this a try. The "cat" field is simply an integer key field to link
vector objects and an attribute table. As such, it needs to meet the
following criteria:

1) It MUST be integer. Nothing else will work
2) The key field is always called "cat" for the vector object, but can be
called any acceptable field name in the attribute table
3) The values in the "cat" field for each vector object must match the
values in the key field in PostgreSQL for the associated attributes. That is
vector object with cat=5 needs to have a corresponding record in the
attribute table with a key field that has a value of 5. More than one vector
object can have the same cat value. That is, you can have 10 points with
cat=1, 10 points with cat=2, and 10 points with cat=3. In the attribute
table then, you need 3 records: keyfield=1, keyfield=2, and keyfield=3. As
you can see, this is a many to one relationship. GRASS also supports 1:1 and
(I've read but have not tried) many:1 relationships.
4) use v.db.connect to specify the database, driver, table, and key field
that joins the attribute table to the vector objects.
5) You can have a COMPLETELY DISTINCT set of key field values for the SAME
vector object by using a different layer. That is, using the example in #3,
you can have a different set of key fields in layer=2 such that for the
first 5 points, cat=1, the next 20 points cat=2, and the last 5 points
cat=3; AND for layer=3 you can have cat=1-30 (unique value for each object).
Each layer, then, can join to a DIFFERENT attribute table that has records
with key fields that match the cat values for that layer. That is, you can
have an attribute table that matches the cat values of layer=1 (i.e, 3
records), a different table that matches the cat values of layer=2 (i.e., 3
DIFFERENT records), and yet a third attribute table that matches cat values
of layer=3 (30 records).

I hope this helps.
__________________________________________
Michael Barton, Professor of Anthropology
School of Human Evolution & Social Change
Center for Social Dynamics & Complexity
Arizona State University

phone: 480-965-6213
fax: 480-965-7671
www: http://www.public.asu.edu/~cmbarton



> From: William Hudspeth <bhudspeth at edac.unm.edu>
> Date: Mon, 15 May 2006 10:35:55 -0600
> To: <grassuser at grass.itc.it>
> Subject: [GRASS-user] v.db.connect, categories, and postgresql database
> queries
> 
> Hello all,
> 
> I am trying to make a connection between a GRASS vector map and an
> attribute table stored in Postgresql. I can see and query the attribute
> table with db.connect and db.select, but I cannot seem to do so with
> v.db.connect or v.db.select. It seems that the problem perhaps has
> something to do with the fact that the postgres attribute table is
> lacking a cat field, but I don't understand how the field is created and
> used in subsequent query operations. Could someone please provide an
> explanation and series of directions on how I link vector maps to
> attribute tables, and how the cat fields are involved. The online
> documentation is not particularly clear in this area. I would like
> ultimately to display (d.vect) vector map records based on values found
> in the postgres attribute table.
> 
> Thanks for any help.
> 
> Bill
> 
> 
> 
> 
>