PostGIS – Troubleshooting PostGIS Database Connection with GRASS GIS

grass-gispostgis

I am trying to connect to a PostGIS database (hosted in a docker container) on a remote server with GRASS. I am able to connect with psql and confirm that the table is there (IP redacted):

psql -h x.x.x.x -U store_sa -d lenistore -p 5432
re -p 5432
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1), server 13.4)
WARNING: psql major version 12, server major version 13.
         Some psql features might not work.
Type "help" for help.
lenistore=> select count(*) from store_sa.all_roads;
 count
-------
  5000
(1 row)

I then make a connection to a table in GRASS:

GRASS 7.8.2 (newLocation):~ > v.external -o input="PG:dbname=lenistore user=store_sa host=x.x.x.x port=5432" layer="store_sa.all_roads" out=network_to_clean --overwrite
Over-riding projection check
WARNING: Vector map <network_to_clean> already exists and will be
         overwritten
v.external complete. Link to vector map <network_to_clean> created.

But when I try to run v.clean, it fails, complaining that the database does not exist.

GRASS 7.8.2 (newLocation):~ > v.clean -c input=network_to_clean output="routing_network" tool=break,chdangl threshold=0.00025,0.0005 --overwrite
Note: In latitude-longitude coordinate system specify threshold in degree
unit
WARNING: Threshold for tool 1 may not be > 0, set to 0
--------------------------------------------------
Tool: Threshold
Break: 0
Change type of boundary dangles: 0.0005
--------------------------------------------------
WARNING: Vector map <routing_network> already exists and will be
         overwritten
WARNING: Table <routing_network> linked to vector map <routing_network>
         does not exist
DBMI-PostgreSQL driver error:
Connection failed.
FATAL:  database "lenistore" does not exist


DBMI-PostgreSQL driver error:
Connection failed.
FATAL:  database "lenistore" does not exist


WARNING: Unable to open database <lenistore> by driver <pg>
no database is open
no database is open
WARNING: Unable to copy table <routing_network>
WARNING: Unable to copy table <store_sa.all_roads> for layer 1 from
         <network_to_clean@PERMANENT> to <routing_network>
WARNING: Failed to copy attribute table to output vector map
Rebuilding parts of topology...
Building topology for vector map <routing_network@PERMANENT>...
Registering primitives...
--------------------------------------------------
Tool: Split lines
Tool: Break lines at intersections
 100%
Tool: Remove duplicates
 100%
Tool: Merge lines
 100%
--------------------------------------------------
Building topology for vector map <routing_network@PERMANENT>...
--------------------------------------------------
Tool: Change type of boundary dangles
 100%
--------------------------------------------------
Rebuilding topology for output vector map...
Building topology for vector map <routing_network@PERMANENT>...
Registering primitives...

I can also confirm that the tables are there by executing v.external -l:

GRASS 7.8.2 (newLocation):~ > v.external -l input="PG:dbname=lenistore user=store_sa host=x.x.x.x port=5432"
PostGIS database <lenistore> contains 7 feature tables:
store_sa.all_roads
...

I see that I have a psql mismatch, but I don't know if GRASS uses psql or not, and I didn't want to go mucking around too much with that because this server is used by other people. I can try to change it if all else fails but I was hoping that someone could see something simple that I am just completely missing. I tried again with a PostgreSQL 12 image and it still fails for the same reason.

What I find very strange is that when v.clean executes, it actually seems to go through the process, with the progress bars progressing rather slowly, as if it is processing a large table.

I have also tried altering the user (store_sa) to be SUPERUSER, and even that doesn't work. I am at my wit's end here. It works fine with a local database, only when I connect to this remote one does it fail.

Best Answer

After a very frustrating afternoon with a colleague, we figured it out.

The reason it had always worked before this is because all my code was previously configured to work on a local Postgres instance. (i.e. on localhost).

My explanation here is based on what appears to have happened, I am not an expert on the inner workings on GRASS and my brain feels like a pretzel after this afternoon, so don't take this explanation as the absolute truth.

It seems that v.external -o input="PG:dbname=lenistore user=store_sa host=x.x.x.x port=5432" layer="store_sa.all_roads" out=network_to_clean --overwrite does not store the postgres login credentials as part of the created layer, nor does it store the data locally. It is a one-time read of the data, and seems to store it as a layer in your GRASS session which serves as a kind of pointer to the data source.

That means that the second command, v.clean -c input=network_to_clean output="routing_network" tool=break,chdangl threshold=0.00025,0.0005 --overwrite, has no concept of how to access the database, because it only sees the name of the layer in grass (network_to_clean), and not the credentials to go actually get the data.

We have to run another couple of commands before running v.clean:

db.connect driver=pg database=lenistore
db.login user=store_sa pass=<password> host=x.x.x.x port=5432 --overwrite

At first I was confused because I thought that db.login would perform the action of logging into the database. No - it saves the login credentials to a config file used by GRASS to make future connections. So by running this command first, we seem to save the signature of the login credentials for future uses, similar to a .pgpass file.

So, in short, the full process is:

db.connect driver=pg database=lenistore
db.login user=store_sa pass=<password> host=x.x.x.x port=5432 --overwrite
v.external -o input="PG:dbname=lenistore user=store_sa host=x.x.x.x port=5432" layer="togo.all_roads" out=network_to_clean --overwrite
v.clean -c input=network_to_clean output="routing_network" tool=break,chdangl threshold=0.00025,0.0005 --overwrite
... do whetver else at this point

You probably don't need to run the first two commands every time if this is being executed as a script in the same GRASS context, but it seems to not hurt my process (which is internal and not sensitive, so I don't care), so I'm just leaving it like that.

Related Solutions

[GIS] Does PostGIS not work with psql

If PGAdmin is definitely succeeding at operations involving the geography type, then I can only suggest making sure that psql and PgAdmin are actually connected to the same database. The error reported by psql is from the server, psql is just the messenger. are you specifying a spatially-enabled database when you invoke psql? If not, see http://postgis.net/docs/postgis_installation.html#templatepostgis

[GIS] Consistent Errors with QGIS using GRASS tools

When I run v.build.check, I get:

WARNING: Unable to determine input map's vector feature type(s). WARNING: No attribute table found -> using only category numbers as attributes

The v.build.check algorithm simplifies the execution of some GRASS functions:

g.proj -c proj4="+proj=longlat +datum=WGS84 +no_defs"
v.in.ogr min_area=0.0001 snap=-1.0 input="D:\a-folder\a-layer-file.shp" layer="a-layer-name" output="vector_5c2d30d9d13569" --overwrite -o
g.region n=90.0 s=-90.0 e=180.0 w=-180.0
v.build -e map=vector_5c2d30d9d13569 error=errore3d9a3b140e544858205d7609e40ce88 --overwrite
v.out.ogr -c type="auto" input="errore3d9a3b140e544858205d7609e40ce88" output="C:\Users\MyUser\AppData\Local\Temp\processing_546e3a62f29740018fb5e979e41e7ebb\73ab30660c314a27a97e2b609f74d441\error.gpkg" format="GPKG" --overwrite

You can read the manual of each function used by the algorithm: g.proj, v.in.ogr, g.region, v.build, and v.out.ogr.

Precisely, these warnings are issued by the v.out.ogr function when no topology was created by v.build -e (that is, it didn't find topological errors in the analyzed layer):

Checking for topological errors...
0..2..4..6..8..10..12..14..16..18..20..22..24..26..28..30..32..34..36..38..40..42..44..46..48..50..52..54..56..58..60..62..64..66..68..70..72..74..76..78..80..82..84..86..88..90..92..94..96..98..100
Building topology for vector map <errore3d9a3b140e544858205d7609e40ce88@PERMANENT>...
Registering primitives...
0 primitives registered
0 vertices registered
Building areas...
100
0 areas built
0 isles built
Attaching islands...
Attaching centroids...
Number of nodes: 0
Number of primitives: 0
Number of points: 0
Number of lines: 0
Number of boundaries: 0
Number of centroids: 0
Number of areas: 0
Number of isles: 0
C:\PROGRA~1\QGIS3~1.4\bin>v.out.ogr -c type="auto" input="errore3d9a3b140e544858205d7609e40ce88" output="C:\Users\MyUser\AppData\Local\Temp\processing_546e3a62f29740018fb5e979e41e7ebb\73ab30660c314a27a97e2b609f74d441\error.gpkg" format="GPKG" --overwrite
WARNING: Unable to determine input map's vector feature type(s).
WARNING: No attribute table found -> using only category numbers as attributes
WARNING: Output layer is empty, no features written
v.out.ogr complete. 0 features (Unknown (any) type) written to <errore3d9a3b140e544858205d7609e40ce88> (GPKG format).

I would think it's an expected behavior, at least in this case, and you should not worry about those warnings, other than knowing that v.out.ogr had nothing to export.

When I run the (v.net.distance) tool, I get this message:

WARNING: Unable to open vector map on level 2. Try to rebuild vector topology with v.build

In the case of v.net.distance we should see what is the particular situation in which it returns that warning. Note that it does not refer to the v.build.check algorithm but to the GRASS v.build function that Creates topology for vector map. It may be appropriate to start a new question for that warning by attaching the data set that produces it. Also, I leave a link to the manual.

Best Answer

Related Solutions

[GIS] Does PostGIS not work with psql

[GIS] Consistent Errors with QGIS using GRASS tools

Related Question