[GRASS-user] v.colors usage

[rshepard]

On Tue, 6 Nov 2018, Markus Metz wrote:

Please check v.db.connect map=stations -p
and
db.connect -p
Is the table attached to the map stations really in the default database?

Markus M,

db.connect -p

driver: pg
database: willamette-river-hg
schema: group:

   Well, well, well. Here's the problem:

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/mercury/sqlite/sqlite.db>
through driver <sqlite> with key <cat>

   How can stations be seen in the default sqlite database when db.connect
shows the table in the postgres database? What else is db.connect doing if
it allows grass commands to ignore what it reports and defaults to the
sqlite one?

   I thought db.connect used the database to which it reported being
connected.

   How do I remove the sqlite stations table so grass sees it in the
connected postgres database?

Thanks!

Rich

[Markus_Metz]

On Tue, Nov 6, 2018 at 10:34 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Tue, 6 Nov 2018, Markus Metz wrote:

Please check v.db.connect map=stations -p
and
db.connect -p
Is the table attached to the map stations really in the default database?

Markus M,

db.connect -p
driver: pg
database: willamette-river-hg
schema: group:

Well, well, well. Here’s the problem:

v.db.connect map=stations -p
Vector map is connected by:
layer <1/stations> table in database
</home/rshepard/data/grassdata/willamette_basin/mercury/sqlite/sqlite.db>
through driver with key

How can stations be seen in the default sqlite database when db.connect
shows the table in the postgres database? What else is db.connect doing if
it allows grass commands to ignore what it reports and defaults to the
sqlite one?

Apparently the table (not the vector map) stations exists twice: once in the sqlite database, once in the pg willamette-river-hg database. This is the reason for the confusion.

A vector map can have a table that is not in the default database as reported by db.connect -p.

I thought db.connect used the database to which it reported being
connected.

db.connect as well as other db.* commands use the default database connection reported by db.connect -p unless specified, e.g.

db.describe tab=stations driver=sqlite database=/home/rshepard/data/grassdata/willamette_basin/mercury/sqlite/sqlite.db

How do I remove the sqlite stations table so grass sees it in the

connected postgres database?

You have to change the database connection for a given vector map with v.db.connect. To be safe (avoiding confusion) you might use v.db.droptable first. Make sure you transfer attribute values first, e.g. with db.copy.

Markus M

[rshepard]

On Tue, 6 Nov 2018, Markus Metz wrote:

Apparently the table (not the vector map) stations exists twice: once in
the sqlite database, once in the pg willamette-river-hg database. This is
the reason for the confusion. A vector map can have a table that is not in
the default database as reported by db.connect -p.

Markus M,

   I did not realize this. I thought that using db.connect with the pg driver
would use that table exclusively. This is good to know.

db.connect as well as other db.* commands use the default database
connection reported by db.connect -p unless specified, e.g.

   Well, it didn't here when I tried using the v.colors command.

You have to change the database connection for a given vector map with
v.db.connect. To be safe (avoiding confusion) you might use v.db.droptable
first. Make sure you transfer attribute values first, e.g. with db.copy.

   I had used db.copy to copy all the stations map tables to postgres except
for the stations table because that existed first in the postgres database,
and attributes there were expoorted and used with v.in.ascii to create the
grass map.

   Changing the database connection to the sqlite default I thought the
stations table was deleted:

db.droptable -f driver=sqlite table=stations

Forcing ...
WARNING: Deleting table <stations> which is attached to following map(s):
WARNING: stations@mercury

but,

db.connect driver=pg database="willamette-river-hg"

GRASS 7.7.svn (willamette_basin):~/data/grassdata/willamette_basin > db.connect -p
driver: pg
database: willamette-river-hg

v.colors -c map=stations use=attr column=howmany rules=rules.txt rgb_col=bin_col --o

DBMI-SQLite driver error:
Error in sqlite3_prepare(): select * from stations where oid < 0
no such table: stations

DBMI-SQLite driver error:
Error in sqlite3_prepare(): select * from stations where oid < 0
no such table: stations

ERROR: Column <howmany> not found in table <stations>

db.connect -p driver: pg database: willamette-river-hg schema: group:

GRASS 7.7.svn (willamette_basin):~/data/grassdata/willamette_basin

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/mercury/sqlite/sqlite.db>
through driver <sqlite> with key <cat>

   So, grass tells me it dropped the sqlite database table, but it still
looks for it when I run v.colors. I suppose I need to manually change the
database type.

Regards,

Rich

[rshepard]

On Tue, 6 Nov 2018, Rich Shepard wrote:

db.droptable -f driver=sqlite table=stations
Forcing ...
WARNING: Deleting table <stations> which is attached to following map(s):
WARNING: stations@mercury

but,

db.connect driver=pg database="willamette-river-hg"
GRASS 7.7.svn (willamette_basin):~/data/grassdata/willamette_basin > db.connect -p
driver: pg
database: willamette-river-hg

   I think I see the problem and need clarification so I can fix it.

   All prior grass projects used the default databases. This project is where
I learn how to use postgres tables instead.

   When I used db.copy I gave the postgres tables new names; e.g.,
db.copy from_driver=sqlite
from_database="$HOME/data/grassdata/willamette_basin/mercury/sqlite/sqlite.db"
from_table=wrb to_driver=pg to_database=willamette-river-hg to_table=wrb_pg

   Now, the grass location has this directory strucure:

$ ls
PERMANENT/ db.test mercury/ willamette-river-hg

and the mercury subdirectory has the map and attribute subdirectories:

$ ls mercury/
VAR WIND sqlite/ vector/ windows/

while the willamette-river-hg file is strictly attributes.

   My question is whether I can delete the ../mercury/sqlite/ subdirectory
since the pg driver is the default when I invoke grass. Then I could rename
the postgres tables to remove the _pg appended to the table names.

Regards,

Rich

[Markus_Metz]

On Tue, Nov 6, 2018 at 11:57 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Tue, 6 Nov 2018, Markus Metz wrote:

Apparently the table (not the vector map) stations exists twice: once in
the sqlite database, once in the pg willamette-river-hg database. This is
the reason for the confusion. A vector map can have a table that is not in
the default database as reported by db.connect -p.

Markus M,

I did not realize this. I thought that using db.connect with the pg driver
would use that table exclusively. This is good to know.

db.connect as well as other db.* commands use the default database
connection reported by db.connect -p unless specified, e.g.

Well, it didn’t here when I tried using the v.colors command.

You have to change the database connection for a given vector map with
v.db.connect. To be safe (avoiding confusion) you might use v.db.droptable
first. Make sure you transfer attribute values first, e.g. with db.copy.

I had used db.copy to copy all the stations map tables to postgres except
for the stations table because that existed first in the postgres database,
and attributes there were expoorted and used with v.in.ascii to create the
grass map.

Changing the database connection to the sqlite default I thought the
stations table was deleted:

db.droptable -f driver=sqlite table=stations

I suggested v.db.droptable, not db.droptable. db.* commands work on databases whereas v.* commands work on vectors. Thus you need to use v.db.connect, not db.connect, to connect a table to a vector, e.g.:

v.db.connect map=stations driver=pg database=“willamette-river-hg” table=stations layer=1

HTH,

Markus M

Forcing …
WARNING: Deleting table which is attached to following map(s):
WARNING: stations@mercury

but,

db.connect driver=pg database=“willamette-river-hg”
GRASS 7.7.svn (willamette_basin):~/data/grassdata/willamette_basin > db.connect -p
driver: pg
database: willamette-river-hg

v.colors -c map=stations use=attr column=howmany rules=rules.txt rgb_col=bin_col --o
DBMI-SQLite driver error:
Error in sqlite3_prepare(): select * from stations where oid < 0
no such table: stations

DBMI-SQLite driver error:
Error in sqlite3_prepare(): select * from stations where oid < 0
no such table: stations

ERROR: Column not found in table

db.connect -p driver: pg database: willamette-river-hg schema: group:
GRASS 7.7.svn (willamette_basin):~/data/grassdata/willamette_basin

v.db.connect map=stations -p Vector map is connected by: layer
<1/stations> table in database
</home/rshepard/data/grassdata/willamette_basin/mercury/sqlite/sqlite.db>
through driver with key

So, grass tells me it dropped the sqlite database table, but it still
looks for it when I run v.colors. I suppose I need to manually change the
database type.

Regards,

Rich

---

grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user

[rshepard]

On Wed, 7 Nov 2018, Markus Metz wrote:

I suggested v.db.droptable, not db.droptable. db.* commands work on
databases whereas v.* commands work on vectors. Thus you need to use
v.db.connect, not db.connect, to connect a table to a vector, e.g.:

v.db.connect map=stations driver=pg database="willamette-river-hg"
table=stations layer=1

Markus M,

   Okay. Another step in my learning to use postgres databases with grass
rther than the default.

   I don't know if this makes a difference, but the stations table has the
same name in both databases. The grass map attribute table came from the
postgres table. The other maps were developed in grass and I used db.copy
with a name change to insert them in the postgres database.

   I'll use v.db.connect and v.db.droptable now.

Many thanks,

Rich

[Markus_Metz]

On Wed, Nov 7, 2018 at 2:39 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Wed, 7 Nov 2018, Markus Metz wrote:

I suggested v.db.droptable, not db.droptable. db.* commands work on
databases whereas v.* commands work on vectors. Thus you need to use
v.db.connect, not db.connect, to connect a table to a vector, e.g.:

v.db.connect map=stations driver=pg database=“willamette-river-hg”
table=stations layer=1

Markus M,

Okay. Another step in my learning to use postgres databases with grass
rther than the default.

I don’t know if this makes a difference, but the stations table has the
same name in both databases. The grass map attribute table came from the
postgres table. The other maps were developed in grass and I used db.copy
with a name change to insert them in the postgres database.

I’ll use v.db.connect and v.db.droptable now.

a short summary (others may have similar problems).

db.connect sets the default database connection for subsequently created vector maps, already existing vector maps are not affected

v.db.connect sets the table and database connection for a given, existing vector. The default database connection can be overridden with the driver= and database= options

Hope that makes it clearer

Markus M

Many thanks,

Rich

---

grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user

[rshepard]

On Wed, 7 Nov 2018, Markus Metz wrote:

a short summary (others may have similar problems).

db.connect sets the default database connection for subsequently created
vector maps, already existing vector maps are not affected

v.db.connect sets the table and database connection for a given, existing
vector. The default database connection can be overridden with the driver=
and database= options

Hope that makes it clearer

Markus M,

   Yes, it certainly does make it clearer. Thanks for your patient help.

Best regards,

Rich

[rshepard]

On Wed, 7 Nov 2018, Markus Metz wrote:

v.db.connect sets the table and database connection for a given, existing
vector. The default database connection can be overridden with the driver=
and database= options

   I reset the attribute database to the sqlite default with 'db.connect
driver=sqlite database=mercury' and that's the database shown in
response to db.connect -p:

db.connect -p

driver: sqlite
database: mercury

   The next step in the example is to list the tables:

db.tables -p

DBMI-SQLite driver error:
Unable to open database: mercury
unable to open database file

DBMI-SQLite driver error:
Unable to open database: mercury
unable to open database file

ERROR: Unable to open database <mercury>

   What error have I made here?

Rich

[Markus_Metz]

On Wed, Nov 7, 2018 at 4:51 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Wed, 7 Nov 2018, Markus Metz wrote:

v.db.connect sets the table and database connection for a given, existing
vector. The default database connection can be overridden with the driver=
and database= options

I reset the attribute database to the sqlite default with ‘db.connect
driver=sqlite database=mercury’ and that’s the database shown in

response to db.connect -p:

for sqlite, the database must be a path to a sqlite database, in your case, as you used yourself before:

database=“$HOME/data/grassdata/willamette_basin/mercury/sqlite/sqlite.db”

Markus M

db.connect -p
driver: sqlite
database: mercury

The next step in the example is to list the tables:

db.tables -p
DBMI-SQLite driver error:
Unable to open database: mercury
unable to open database file

DBMI-SQLite driver error:
Unable to open database: mercury
unable to open database file

ERROR: Unable to open database

What error have I made here?

Rich

---

grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user

[Markus_Metz]

On Wed, Nov 7, 2018 at 5:03 PM Markus Metz <markus.metz.giswork@gmail.com> wrote:

On Wed, Nov 7, 2018 at 4:51 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Wed, 7 Nov 2018, Markus Metz wrote:

v.db.connect sets the table and database connection for a given, existing
vector. The default database connection can be overridden with the driver=
and database= options

I reset the attribute database to the sqlite default with ‘db.connect
driver=sqlite database=mercury’ and that’s the database shown in
response to db.connect -p:

for sqlite, the database must be a path to a sqlite database, in your case, as you used yourself before:

database=“$HOME/data/grassdata/willamette_basin/mercury/sqlite/sqlite.db”

for default sqlite databases you can use simply

db.connect -d

Markus M

db.connect -p
driver: sqlite
database: mercury

The next step in the example is to list the tables:

db.tables -p
DBMI-SQLite driver error:
Unable to open database: mercury
unable to open database file

DBMI-SQLite driver error:
Unable to open database: mercury
unable to open database file

ERROR: Unable to open database

What error have I made here?

Rich

---

grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user

[rshepard]

On Wed, 7 Nov 2018, Markus Metz wrote:

for sqlite, the database must be a path to a sqlite database, in your case,
as you used yourself before:
database="$HOME/data/grassdata/willamette_basin/mercury/sqlite/sqlite.db"

Markus M,

   Thanks again for your advice here and using only db.connect -p for the
sqlite database. Won't be long before I've gained enough experience to get
commands right the first time.

Best regards,

Rich

[rshepard]

On Wed, 7 Nov 2018, Markus Metz wrote:

for sqlite, the database must be a path to a sqlite database, in your
case, as you used yourself before:
database="$HOME/data/grassdata/willamette_basin/mercury/sqlite/sqlite.db"

   Well, the situation here has become FUBAR and I need help fixing things.

   The geometry/database in grass has 8 maps, each with an associated
attribute table. The stations map is connected to the postgres table:

$ less data/grassdata/willamette_basin/mercury/vector/stations/dbln
1/stations|stations|cat|/home/rshepard/data/grassdata/willamette_basin/willamette-river-hg|pg

Other maps still have their tables in the sqlite.db; e.g.,
1/FishD_AllSpecies_January2012|all_species|cat|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db|sqlite

so I tried to re-connect it to the postgres table using v.db.connect with
this result:

v.db.connect map=all_species driver=pg database="$HOME/data/grassdata/willamette_basin/willamette-river-hg" table=all_species -o

DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "/home/rshepard/data/grassdata/willamette_basin/willamette-river" does not exist
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>
WARNING: Unable open database
          </home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>
          by driver <pg>
ERROR: Table <all_species> does not exist in database
        </home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>

   Grass is connected to that database and I'm logged in:

db.connect -p

driver: pg
database: /home/rshepard/data/grassdata/willamette_basin/willamette-river-hg

and, of course, the table 'all_species' exists there:

willamette-river-hg=# \d
              List of relations
  Schema | Name | Type | Owner --------+----------------+-------+----------
  public | all_species | table | rshepard

   So, I've obviously missed a step or the proper syntax and have become
sufficiently confused by what's to be done when connected to each database
that I need to be shown the True Path to transferring the rest of the map
attribute tables to the ones db.cop(ied) to the postgres database.

TIA,

Rich

[Markus_Metz]

On Wed, Nov 7, 2018 at 7:06 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Wed, 7 Nov 2018, Markus Metz wrote:

for sqlite, the database must be a path to a sqlite database, in your
case, as you used yourself before:
database=“$HOME/data/grassdata/willamette_basin/mercury/sqlite/sqlite.db”

Well, the situation here has become FUBAR and I need help fixing things.

The geometry/database in grass has 8 maps, each with an associated
attribute table. The stations map is connected to the postgres table:

$ less data/grassdata/willamette_basin/mercury/vector/stations/dbln
1/stations|stations|cat|/home/rshepard/data/grassdata/willamette_basin/willamette-river-hg|pg

Other maps still have their tables in the sqlite.db; e.g.,
1/FishD_AllSpecies_January2012|all_species|cat|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db|sqlite

so I tried to re-connect it to the postgres table using v.db.connect with
this result:

v.db.connect map=all_species driver=pg database=“$HOME/data/grassdata/willamette_basin/willamette-river-hg” table=all_species -o
DBMI-PostgreSQL driver error:

Connection failed.

for pg you don’t specify a path/to/file as database, instead you use a name: database=willamette-river-hg

Markus M

FATAL: database “/home/rshepard/data/grassdata/willamette_basin/willamette-river” does not exist
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
WARNING: Unable open database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>
by driver
ERROR: Table <all_species> does not exist in database
</home/rshepard/data/grassdata/willamette_basin/willamette-river-hg>

Grass is connected to that database and I’m logged in:

db.connect -p
driver: pg
database: /home/rshepard/data/grassdata/willamette_basin/willamette-river-hg

and, of course, the table ‘all_species’ exists there:

willamette-river-hg=# \d
List of relations
Schema | Name | Type | Owner
--------±---------------±------±---------
public | all_species | table | rshepard

So, I’ve obviously missed a step or the proper syntax and have become
sufficiently confused by what’s to be done when connected to each database
that I need to be shown the True Path to transferring the rest of the map
attribute tables to the ones db.cop(ied) to the postgres database.

TIA,

Rich

---

grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user

[rshepard]

On Wed, 7 Nov 2018, Rich Shepard wrote:

Other maps still have their tables in the sqlite.db; e.g.,
1/FishD_AllSpecies_January2012|all_species|cat|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db|sqlite

   Perhaps this is the problem: some maps seem to have a source name as well
as a table name. How can I remove this; for example, removing the string
"FishD_AllSpecies_January2012|' from grass?

   Can I simply edit that file?

Regards,

Rich

[rshepard]

On Wed, 7 Nov 2018, Markus Metz wrote:

for pg you don't specify a path/to/file as database, instead you use a
name: database=willamette-river-hg

Markus M,

   Well, darn! I did not pick up on this difference when reading the manual
pages. It certainly makes a difference!

   When I get the maps connected to the postgres tables can I then remove the
sqlite versions? The v.db.droptable looks like it would act on the postgres
table rather than the sqlite table.

Many thanks,

Rich