[GRASS-user] load vector map from PostGIS

[Moritz Lennert]

Le Tue, 13 Feb 2018 14:09:31 +0100 (CET),
"Vilem Ded" <Ded.V at seznam.cz> a écrit :

> Hi, 
> is there any possibility to download data  into local grass database 
> (topologcal) from remote postgis server? (without getting error)
> Or is the issue still pending?
> (http://osgeo-org.1560.x6.nabble.com/Unable-
> to-link-to-postgis-postgresql-td5285313.html) I am using Python to
> work with GRASS. And honestly, I got lost in outputs of db.*
> functions:

AFAIK, you should not need to go through all the db.* functions to
import your data. Not much time to look any further into this, but check
this thread:
https://lists.osgeo.org/pipermail/grass-dev/2017-May/085032.html/

In brief, you should be able to import your data with a simple v.in.ogr
call, without any db.connect/db.login magic.

Moritz

> 
> #set database and login
> gscript.run_command('db.connect',
> database="host=172.21.3.20,dbname=mydb", driver="pg",  verbose = True)
> gscript.run_command('db.login', user="dbusername", password="passwd", 
> overwrite = True)
> 
> #verifying connection
> gscript.run_command('db.connect', flags='p')
> output:
> driver: pg
> database: host=172.21.3.20,dbname=mydb
> schema: 
> group: 
> #so everything looks fine so far
> 
> #here comes the trouble - simple list tables in db
> gscript.run_command('db.tables', flags='p', verbose = True)
> output:
> DBMI-PostgreSQL driver error:
> Connection failed.
> could not connect to server: No such file or directory
>     Is the server running locally and accepting
>     connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.5432"?
> 
> DBMI-PostgreSQL driver error:
> Connection failed.
> could not connect to server: No such file or directory
>     Is the server running locally and accepting
>     connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.5432"?
> 
> ERROR: Unable to open database <host=172.21.3.20,dbname=mydb>
> 
> #but for example:
> gscript.run_command('v.external',
>                     input="PG:dbname=mydb host=172.21.3.20",
>                     flags='l', overwrite =  True)
> output:
> --actuall list of all tables in mydb - so i CAN connect to the server!
> 
> #same for mapping external link - following will create external link
> gscript.run_command('v.external',
>                     input="PG:dbname=mydb host=172.21.3.20
> user=dbusername password=password",
>                     layer="myschema.mytable", output='outpoints,
> overwrite = True)
> output:
> WARNING: No key column detected.
> WARNING: Feature table <outpoints> has no primary key defined. Unable
> to define DB links.
> WARNING: Feature table <outpoints> has no primary key defined
> WARNING: Random read is not supported for this layer. Unable to build
>          topology.
> v.external complete. Link to vector map <outpoints> created.
> 
> #weird, because i have primary key defined in the table, following is 
> printing description of that external link
> gscript.vector_info("outpoints")
> 
> #if i try to load data from database
> gscript.run_command('v.in.ogr',
>                     input="PG:dbname=mydb host=172.21.3.20",
>                     layer='myschema.mytable',geometry="mygeomcolumn",
> output = "outpoints2", overwrite =  True, type="point")
> output:
> Check if OGR layer <myschema.mytable> contains polygons...
>  100%�����
> WARNING: Vector map <outpoints2> already exists and will be
> overwritten DBMI-PostgreSQL driver error:
> Connection failed.
> could not connect to server: No such file or directory
>     Is the server running locally and accepting
>     connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.5432"?
> 
> DBMI-PostgreSQL driver error:
> Connection failed.
> could not connect to server: No such file or directory
>     Is the server running locally and accepting
>     connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.5432"?
> 
> WARNING: Unable to open database <host=172.21.3.20,dbname=macfishdb>
> by driver <pg>
> WARNING: Unable open database <host=172.21.3.20,dbname=macfishdb> by
> driver <pg>
> WARNING: Unable to find table <outpoints2> linked to vector map
> <lp_grass> Creating attribute table for layer
> <at_macfish.loggerpos>... DBMI-PostgreSQL driver error:
> Connection failed.
> could not connect to server: No such file or directory
>     Is the server running locally and accepting
>     connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.5432"?
> 
> DBMI-PostgreSQL driver error:
> Connection failed.
> could not connect to server: No such file or directory
>     Is the server running locally and accepting
>     connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.5432"?
> 
> WARNING: Unable to open database <host=172.21.3.20,dbname=mydb> by
>          driver <pg>
> ERROR: Unable to open database <host=172.21.3.20,dbname=mydb> by
>        driver <pg>
> 
> My version of grass:
> GRASS version:
> 7.4.0 
> GRASS SVN revision:
> exported 
> Build date:
> 2018-01-26 
> Build platform:
> x86_64-pc-linux-gnu 
> GDAL:
> 2.2.2 
> PROJ.4:
> 4.9.2 
> GEOS:
> 3.5.1 
> SQLite:
> 3.11.0 
> Python:
> 2.7.12 
> wxPython:
> 3.0.2.0 
> Platform: Linux-4.4.0-112-generic-x86_64-with-Ubuntu-16.04-xenial   
> 
> Version of python:
> 
> Python 2.7.12 64bits
> 
> Content of my .grass7/dblogin:
> pg|host=172.21.3.20, dbname=mydb|dbusername|passwd||
> sqlite|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db|dbusername|passwd||
> pg|macfishdb|dbusername|passwd||
> pg|host=172.21.3.20,dbname=mydb|dbusername|passwd||
> 
> Thank you for any comment
> Dead Vil
> 
>