[GRASS-user] Problems connecting a vector map of points to a postgresql attribute table

[William Hudspeth]

Hello,

I am having problems connecting a vector map of points to an attribute
table in Postgresql/Postgis. I am trying to link the vector map named
"epa_airnow_stations" using the character variable named "loc_code" with
the postgres table named "airnow_2003_2005_pm10" on a character variable
also named "loc_code". The vector map has a single record for each
station location (e.g. loc_code), while the postgres table has multiple
entries for each station location (e.g. loc_code).


Following the syntax in the v.db.connect documentation, I used the
following command:


********************************


v.db.connect map=epa_airnow_stations table=epa_airnow_stations layer=2
key=loc_code ddriver=pg database="host=phairs-
devel.unm.edu,dbname=reason,user=apache" table=airnow_2003_2005_pm10
key=loc_code


I get the following warnings:


WARNING: Table <epa_airnow_stations,airnow_2003_2005_pm10> does not
exist in database <host=phairs-devel.unm.edu,dbname=reason>

WARNING: The table <epa_airnow_stations,airnow_2003_2005_pm10> is now
part of vector map <epa_airnow_stations> and may be delected or
overwritten by GRASS modules.

DBMI-Postgres driver error:

Cannot create index:

create unique index
epa_airnow_stations,airnow_2003_2005_pm10_loc_code,loc_code on
epa_airnow_stations,airnow_2003_2005_pm10(loc_code,loc_code)

ERROR: syntax error at or near "," at character 40


WARNING: Cannot create index

WARNING: Select privileges were granted on the table.


*********************************


I am a little unclear what these warnings imply. Nonetheless, the
connection does seem to be established, but with more warnings:


GRASS 6.0.1 (western_us):~ > v.db.connect -c map=epa_airnow_stations
layer=2 WARNING: column 'n_loc_code' : type character varying is stored
as

varchar(250) some data may be lost

WARNING: column 'n_name' : type character varying is stored as varchar
(250)

some data may be lost

WARNING: column 'n_areacode' : type character varying is stored as

varchar(250) some data may be lost

WARNING: pg driver: column 'the_geom', type 22334 is not supported

INTEGER|gid

DOUBLE PRECISION|lat

DOUBLE PRECISION|n_lon

CHARACTER|n_loc_code

CHARACTER|n_name

CHARACTER|n_areacode

CHARACTER|loc_code

CHARACTER|loc_code

DOUBLE PRECISION|lat

DOUBLE PRECISION|lon

CHARACTER|datetime

INTEGER|pm10

TIMESTAMP |time_stamp

GRASS 6.0.1 (western_us):~ >


*******************************************************

Finally, when I try to display the vector map using a select statement
on the time_stamp field in the 'where=' argument, I get the following:


GRASS 6.0.1 (western_us):~ > d.vect map=epa_airnow_stations layer=2
type=centroid color=blue size=5 fcolor=blue
where="time_stamp='2003-12-15 05:00:00'"

DBMI-Postgres driver error:

Cannot select:

SELECT loc_code,loc_code FROM epa_airnow_stations,airnow_2003_2005_pm10
WHERE time_stamp='2003-12-15 05:00:00'

ERROR: column reference "loc_code" is ambiguous



****************************************************************


I would very much appreciate any insight on using the db.connect command
to properly create a postgres database connection, and then to select
and display points in the vector map based on values in the postgres
attribute table. 

Thanks, Bill