[GRASS-user] PostgreSQL connection just not working - v.in.ogr and QuantumGIS

GRASS GRASS user
, ,
1

I am having a heck of a time getting a connection to PostGIS/PostgreSQL to work.

Quantum GIS 0.9.0 connects just fine by itself. I can add a layer to Quantum GIS. My connection info for QGIS is
Name local
Host localhost
Database postgis
Port 5432
Username John

But if I try to do anything with the grass plugin to QGIS (I am trying to us v.in.ogr) QGIS gives me the command

v.in.ogr ‘dsn=PG:host=localhost dbname=postgis port=5432 user=John’ layer=locationmap output=stuff -o

and I get the following result:

Unable to open data source

Finished with error

I also installed grass63RC1 standalone. I got it running, but can’t connect to my data. I’ve tried every connection string I can think of, and I always get
Unable to open datasource.

I went into the ControlPanel → Administrative Tools → Datasources and defined an ODBC datasource that seems to connect to the database, but I have no idea how to make Grass connect via a pre-defined ODBC connection.

I see this web page http://grass.itc.it/grass63/manuals/html63_user/grass-odbc.html but it seems to be unix specific. I’m on Windows.

What the heck am I doing wrong? Any and all ideas are much appreciated. Arghh. QuantumGIS just doesn’t do what I need, and I can see the grass functionality, but can’t use it! It’s so frustrating!

Thanks,

2

John Abraham wrote:

I am having a heck of a time getting a connection to PostGIS/PostgreSQL to work.

[...]

I can't say anything about QGIS, but:

I also installed grass63RC1 standalone. I got it running, but can't connect to my data. I've tried every connection string I can think of, and I always get
Unable to open datasource.

This could be because GRASS doesn't find the path to your postgres libraries.

For the standalone wingrass binary, you can set the path in the grass63.bat file using something like this:

set PATH=c:\PathToPostgres\lib;%PATH%

Be sure to put the postgres path at the beginning, not after the %PATH% because this could cause a library conflict.

I went into the ControlPanel -> Administrative Tools -> Datasources and defined an ODBC datasource that seems to connect to the database, but I have no idea how to make Grass connect via a pre-defined ODBC connection.

ODBC is not compiled as a driver in the current version of the windows GRASS binaries. See http://geog-pc40.ulb.ac.be/grass/wingrass/winconfig.log for a list of what is and is not compiled.

It will in a future version.

3

Well I did manage to get something to work. I’m not sure if setting the path to the Postgres libraries was important or not.

In QGIS I’m able to connect to the database in QGIS using
db.connect driver=pg database=postgis schema=public

and then I’m able to import a shapefile into the PostGIS database using

v.in.ogr dsn=C:\MontgomeryShapefiles\Autauga\BLDG_FOOTPRINT.shp output=bldg_footprint2 -o

That put a table in the database from the shapefile. Not sure what to do with it now, because I guess grass vector layers aren’t PostGIS tables, and when I try to add it as a GRASS vector layer QGIS crashes. But it’s movement foreward anyways.

Thanks for caring, I’ll struggle some more and try to figure this thing out. I think I have a paradigm problem in that I’m expecting everything to be stored in a PostGIS database using PostgreSQL, but Grass seems to be insisting on keeping things in its own dbf files instead.

4

I’m having some trouble with the database paradigm. Can someone answer some questions?

  1. is it possible to only use PostGIS and not use a Grass directory? If so, what would you put in the “GIS Data Directory” on the opening splash screen? I would rather store everything in PostGIS but I’m starting to think that’s not possible.

  2. with db.connect, am I just setting the parameters for the connection to the database to read attribute data? Or is it also linking to the directory to display vector data without converting it using v.in.ogr?

  3. with v.out.ogr, can I export to PostGIS? It doesn’t show up on the dropdown list of formats, but on the man page it has an entry for format=PostgreSQL.

  4. with v.in.org, is there anyway to enable more debugging information? with the command
    v.in.ogr -l {dsn=PG:host=localhost dbname=postgis user=John} min_area=0.0001 snap=-1
    I just get
    Unable to open data source <PG:host=localhost dbname=postgis user=John>
    and it’s killing me to constantly see that error message and no other information. How could I get more information about what is wrong?

I am downloading FWTools142.exe separately, in case that helps. But in the meantime any advice or explanation would be appreciated.

5

On 15/11/07 05:09, John Abraham wrote:

I'm having some trouble with the database paradigm. Can someone answer some questions?

1) is it possible to only use PostGIS and not use a Grass directory? If so, what would you put in the "GIS Data Directory" on the opening splash screen? I would rather store everything in PostGIS but I'm starting to think that's not possible.

The only way to use PostGIS without storing the geometries in GRASS is via v.external. I don't know which modules work with maps linked in via v.external. For example, any module which creates a new map will create it in GRASS, not in PostGIS, so you would have to export it manually via v.out.ogr.

2) with db.connect, am I just setting the parameters for the connection to the database to read attribute data? Or is it also linking to the directory to display vector data without converting it using v.in.ogr?

No, only attribute data for vector geometries.

3) with v.out.ogr, can I export to PostGIS? It doesn't show up on the dropdown list of formats, but on the man page it has an entry for format=PostgreSQL.

v.out.ogr creates this list on the fly, so if you don't see it, this might mean that your installation of ogr does not support PostgreSQL.

In both 6.3 and 6.2.3RC1, PostgreSQL is in the drop down list for me.

To use v.out.ogr with PostgreSQL:
v.out.ogr input=polygons type=area dsn="PG:host=localhost dbname=postgis user=postgres" olayer=polymap format=PostgreSQL
(from the man page)

4) with v.in.org, is there anyway to enable more debugging information? with the command
v.in.ogr -l {dsn=PG:host=localhost dbname=postgis user=John} min_area=0.0001 snap=-1
I just get
Unable to open data source <PG:host=localhost dbname=postgis user=John>
and it's killing me to constantly see that error message and no other information. How could I get more information about what is wrong?

You can try to connect to that data source with db.connect and then run db.test.

You can also enable debugging information by setting the DEBUG variable:

g.gisenv set=DEBUG=3

I am downloading FWTools142.exe separately, in case that helps. But in the meantime any advice or explanation would be appreciated.

If you want to only work with vector data and want it stored in PostGIS, then I'm not sure GRASS is the right solution. You might want to look at qgis or udig.
If you want to profit of the full analytical capacity of GRASS, you're probably better off using the native GRASS format.

Moritz

6

v.out.ogr creates this list on the fly, so if you don't see it, this might mean that your installation of ogr does not support PostgreSQL.

I have ogr2ogr installed (from FWTools142) and working now, and it supports PostgreSQL. Is there a way to get grass63RC1 for windows to use that ogr? I tried setting the path to the directory containing the FWTools142 OGR, but I still get:

Unable to open data source <PG:host=localhost dbname=postgis user=John>

Full command and error message is:

v.in.ogr -l {dsn=PG:host=localhost dbname=postgis user=John} min_area=0.0001 snap=-1
D2/3: G__read_Cell_head
D2/3: G__read_Cell_head_array
D3/3: region item: proj: 99
D3/3: region item: zone: 0
D3/3: region item: north: 792983.53551085
D3/3: region item: south: 587911.71541368
D3/3: region item: east: 473652.88022388
D3/3: region item: west: 247879.38013186
D3/3: region item: cols: 20
D3/3: region item: rows: 20
D3/3: region item: e-w resol: 11288.6750046
D3/3: region item: n-s resol: 10253.59100486
D3/3: region item: top: 1
D3/3: region item: bottom: 0
D3/3: region item: cols3: 11
D3/3: region item: rows3: 12
D3/3: region item: depths: 1
D3/3: region item: e-w resol3: 33.09079794
D3/3: region item: n-s resol3: 30.37584014
D3/3: region item: t-b resol: 1

Unable to open data source <PG:host=localhost dbname=postgis user=John>

--
John

7

On 16/11/07 06:54, John Abraham wrote:

v.out.ogr creates this list on the fly, so if you don't see it, this might mean that your installation of ogr does not support PostgreSQL.

I have ogr2ogr installed (from FWTools142) and working now, and it supports PostgreSQL. Is there a way to get grass63RC1 for windows to use that ogr?

In my last response I forgot that you were using Windows. I can confirm that in the current ogr shipped with GRASS, postgreSQL is not included. For the list of included drivers, see
http://geog-pc40.ulb.ac.be/grass/wingrass/fileformats.txt

I tried setting the path to the directory containing the FWTools142 OGR, but I still get:

To what did you set the path. GRASS needs to find the library, not a command such as ogr2ogr. In the grass distribution, this library is c:\grass\grass-6.3.0RC1/lib/libgdal.dll (OGR is part of GDAL). So, if you want grass to take into account another libgdal.dll, you have to put its path before the one to c:\grass\grass-6.3.0RC1/lib. Or you can try moving libgdal.dll away from c:\grass\grass-6.3.0RC1/lib/.

Unable to open data source <PG:host=localhost dbname=postgis user=John>

Can you connect to this database from the command line with something like

psql -h localhost -d postgis -u John

Did you try db.test ?

Is a password required for the user John ? If yes, run db.login.

Moritz