WARNING: DB connection <pg/willamette-river-hg> already exists and will be
overwritten
If the mapset recognizes that geometry attributes are stored in the
postgres database, and user me has access to these attribute tables, what
might be the cause of this error (seen with multiple maps):
db.columns table=fishes
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "/home/rshepard/data/grassdata/willamette_basin/willamette-river" does not exist
WARNING: Unable to open database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>
by driver <pg>
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "/home/rshepard/data/grassdata/willamette_basin/willamette-river" does not exist
WARNING: DB connection <pg/willamette-river-hg> already exists and will be
overwritten
If the mapset recognizes that geometry attributes are stored in the
postgres database, and user me has access to these attribute tables, what
might be the cause of this error (seen with multiple maps):
db.columns table=fishes
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "/home/rshepard/data/grassdata/willamette_basin/willamette-river" does not exist
WARNING: Unable to open database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>
by driver <pg>
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "/home/rshepard/data/grassdata/willamette_basin/willamette-river" does not exist
I think you need to slow down a bit and try to review your basic understanding of things (maybe [1] could do with a bit more substance).
You are trying to connect to a database that is called "/home/rshepard/data/grassdata/willamette_basin/willamette-river-hg", not (as it should be) "willamette-river-hg".
Also, as MarkusM has already mentioned:
- db.connect does not test for the existence of the database. It just sets the content of the file definining the default mapset connection and outputs the content of this file. Use db.test to test your connection.
- The same goes for db.login: it just writes out the login info into a file. AFAIR, it is not absolutely necessary to use db.login to be able to connect to a database, especially if you do not need a password for connection.
- If you want to check the database connection of a specific vector map, please use v.db.connect -p.
I think you need to slow down a bit and try to review your basic understanding of things (maybe [1] could do with a bit more substance).
Moritz,
I do need to step back a bit.
You are trying to connect to a database that is called "/home/rshepard/data/grassdata/willamette_basin/willamette-river-hg", not (as it should be) "willamette-river-hg".
Yes, I see where I kept confusing path names for the sqlite and postgres
tables.
Almost all issues are now resolved, but I need to add a 'cat' column to
the postgres stations table (it has more columns than does the sqlite one),
so I'll so this with psql and insert sequential numbers in that column that
follow the site_nbr columns. Looking at all tables, I think this is the last
hurdle to displaying the stations table.
I think you need to slow down a bit and try to review your basic
understanding of things (maybe [1] could do with a bit more substance).
Moritz,
I do need to step back a bit.
You are trying to connect to a database that is called
“/home/rshepard/data/grassdata/willamette_basin/willamette-river-hg”, not (as
it should be) “willamette-river-hg”.
Yes, I see where I kept confusing path names for the sqlite and postgres
tables.
Almost all issues are now resolved, but I need to add a ‘cat’ column to
the postgres stations table (it has more columns than does the sqlite one),
so I’ll so this with psql and insert sequential numbers in that column that
follow the site_nbr columns. Looking at all tables, I think this is the last
hurdle to displaying the stations table.
I am afraid that manually adding a cat column and populating it with sequential numbers will not work: how do you assure that the cat numbers and corresponding attribute values indeed correspond to the cat values of the vector features? Chances are high that vector features get linked to wrong attributes.
Considering all the modifications that you have done in the meantime I suggest to start from scratch:
decide on the default database connection you want to use
set up the default database connection if not sqlite
I am afraid that manually adding a cat column and populating it with
sequential numbers will not work: how do you assure that the cat numbers
and corresponding attribute values indeed correspond to the cat values of
the vector features? Chances are high that vector features get linked to
wrong attributes.
Markus M,
Grass told me the stations table has no categories, which it does not
because the map's attribute table was derived from the pre-existing postgres
database table and the geometry created by the easting/northing pairs in
that table.
The other maps and tables were created from agency-sourced GIS
repositories and grass created cat columns for them, so it's only the
stations table needing that column to be added.
db.select sql="select * from stations limit 3"
site_nbr|site_name|lon|lat|easting|northing|start_date|end_date|howmany|bin_col|cat
10332|Willamette R. at SP&S RR Bridge (Portland)|-122.7475|45.577944|||1979-11-06|2003-06-19|25|231:00:231|
10917|Pudding R. at Hwy 99E (Aurora)|-122.748972|45.233806|||1979-11-06|2008-03-26|22|231:00:231|
10376|McKenzie R. at Coburg Road|-123.046194|44.112722|||1980-05-07|2011-01-04|40|231:173:00|
No cat values there now. But, updating the table and setting a distinct cat
number for each site_nbr will fill that column.
The issues with specifying database paths and needing a filled cat column
in the stations table have been resolved. v.db.connect shows a valid
connection to the database table:
v.db.connect map=stations -p
Vector map <stations> is connected by:
layer <1/stations> table <stations> in database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg> through
driver <pg> with key <cat>
but, trying to display that map using v.colors in a script still fails to
find the database:
d.vect complete.
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "/home/rshepard/data/grassdata/willamette_basin/willamette-river" does not exist
WARNING: Unable to open database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>
by driver <pg>
ERROR: Unable to open database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>
by driver <pg>
Hi Rich,
if you use a postgres database (driver=pg) you have to specify the full connection string to the database. You can see this in the manual for db.connect (look for PostgreSQL):
If you use a Sqlite database you can omit driver specification, because that is the default.
regards.
Stefan
Rich Shepard <rshepard@appl-ecosys.com> hat am 7. November 2018 um 23:44 geschrieben:
On Wed, 7 Nov 2018, Rich Shepard wrote:
> Almost all issues are now resolved,
The issues with specifying database paths and needing a filled cat column
in the stations table have been resolved. v.db.connect shows a valid
connection to the database table:
> v.db.connect map=stations -p
Vector map <stations> is connected by:
layer <1/stations> table <stations> in database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg> through
driver <pg> with key <cat>
but, trying to display that map using v.colors in a script still fails to
find the database:
> $HOME/documents/white-papers/geochemistry/willamette-river-mercury/scripts/grass-display-sample-sites.sh
d.vect complete.
d.vect complete.
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "/home/rshepard/data/grassdata/willamette_basin/willamette-river" does not exist
WARNING: Unable to open database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>
by driver <pg>
ERROR: Unable to open database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>
by driver <pg>
The issues with specifying database paths and needing a filled cat column
in the stations table have been resolved. v.db.connect shows a valid
connection to the database table:
It’s not resolved, and it is not a valid connection:
v.db.connect map=stations -p
Vector map is connected by:
layer <1/stations> table in database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg> through
driver with key
for driver=pg, the database must not be a path, it must be database=willamette-river-hg
Also, be aware of the differences between db.connect and v.db.connect. The pg database willamette-river-hg must have been established first with db.connect + db.login, only after that you can use v.db.connect which is not needed for newly created vectors.
Markus M
but, trying to display that map using v.colors in a script still fails to
find the database:
$HOME/documents/white-papers/geochemistry/willamette-river-mercury/scripts/grass-display-sample-sites.sh
d.vect complete.
d.vect complete.
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database “/home/rshepard/data/grassdata/willamette_basin/willamette-river” does not exist
WARNING: Unable to open database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>
by driver
ERROR: Unable to open database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>
by driver
It's not resolved, and it is not a valid connection:
v.db.connect map=stations -p
Vector map <stations> is connected by:
layer <1/stations> table <stations> in database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>
through
driver <pg> with key <cat>
for driver=pg, the database must not be a path, it must be
database=willamette-river-hg
Markus M.
I now understand the differences between db.connect and v.db.connnect and
did not recognize the full path returend by v.db.connect.
Also, be aware of the differences between db.connect and v.db.connect. The
pg database willamette-river-hg must have been established first with
db.connect + db.login, only after that you can use v.db.connect which is
not needed for newly created vectors.
Each time I run db.connect -p it shows the postgres database and the pg
driver. When I saw the results of v.db.connect I added this line to the top
of the script
db.connect driver=pg database=willamette-river-hg
(I did not re-login because that always returned the message that I had been
logged in so the command overwrote the existing login with a new one.)
Re-running the script produced the same error.
If db.connect returns the proper database name (without the full path),
while v.db.connect has the full path, how do I correct this? I've looked at
db.* and v.db.* and not seen what I should do to have the v.db.connect see
the database without a path.
if you use a postgres database (driver=pg) you have to specify the full
connection string to the database. You can see this in the manual for
db.connect (look for PostgreSQL):
Stefan,
Each time I think that I have the path requirement differences between
db.connect and v.db.connect understood there's aninstance when I mix them up
unintentionally. I've changed the db.connect line in the script to use
localhost rather than the absolute path.
As I responded to Markus M, I don't know how to fix the absolute path
returned by v.db.connect
Vector map <stations> is connected by:
layer <1/stations> table <stations> in database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg> through
driver <pg> with key <cat>
Vector map <stations> is connected by:
layer <1/stations> table <stations> in database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg> through
driver <pg> with key <cat>
What am I still doing incorrectly with db.connect?
Consider that the <NameOfTheTable> have to be different from the name of the mapset, i.e. must not be called "stations"!
Regards.
Stefan
Rich Shepard <rshepard@appl-ecosys.com> hat am 8. November 2018 um 15:07 geschrieben:
On Thu, 8 Nov 2018, st_kiefer@web.de wrote:
> if you use a postgres database (driver=pg) you have to specify the full
> connection string to the database. You can see this in the manual for
> db.connect (look for PostgreSQL):
Stefan,
Each time I think that I have the path requirement differences between
db.connect and v.db.connect understood there's aninstance when I mix them up
unintentionally. I've changed the db.connect line in the script to use
localhost rather than the absolute path.
As I responded to Markus M, I don't know how to fix the absolute path
returned by v.db.connect