[gdal-dev] Re: [mapserver-users] ogr connection to postgis problems

[Even Rouault]

(Moving it to gdal-dev list as it is really OGR related)

Travis,

Actually there's one test you didn't make. Can you test this :

ogrinfo -ro -al PG:'dbname=fbs user=webserver password=topsecret' 
canadian_municipalities

and this :

ogrinfo -al PG:'dbname=fbs user=webserver password=topsecret' 
canadian_municipalities

I suspect the first one will fail with "Unable to open datasource...." and 
that the second one will succeed but report 0 layer (only : "INFO: Open of 
`PG:'. Using driver `PostgreSQL' successful.)

There must be something wrong (or not correctly handled by OGR) in your table 
definition. You could check that the column is being properly registered in 
the Postgis geometry_columns table. But it might be another reason (unhandled 
Postgres data type for a column, ...). Perhaps you could try again with GDAL 
1.6.0 as there were lots of substantial changes done for that release. If it 
still doesn't work, perhaps you could dump the structure of your 
canadian_municipalities tables.

Accessing the database through the -sql option use different mechanisms from 
accessing through the layers reported by the OGR PG driver. But 
generally, "ogrinfo -ro -al PG:'dbname=fbs user=webserver password=topsecret' 
your_layer" and "ogrinfo -ro PG:'dbname=fbs user=webserver 
password=topsecret' -sql 'SELECT * FROM your_layer'" should yield to similar 
results.

Best regards,
Even

Le Thursday 26 March 2009 19:12:22, vous avez écrit :
> Even
> I'm using GDAL 1.5.1, released 2008/03/14
>
> This is strange, I can get the -sql to return result but as soon as I
> attempt to convert the result to a shp I get a failure
>
> [root at server34 support]# ogrinfo PG:'dbname=fbs user=webserver
> password=topsecre                  t' -sql "select * from
> canadian_municipalities WHERE name = 'OTTAWA'" | more
> INFO: Open of `PG:dbname=fbs user=webserver password=topsecret'
>       using driver `PostgreSQL' successful.
>
> Layer name: sql_statement
> Geometry: Unknown (any)
> Feature Count: 1
> Extent: (-75.818115, 45.319733) - (-75.590118, 45.468719)
> Layer SRS WKT:
> (unknown)
> Geometry Column = the_geom
> gid: Integer (0.0)
> prov: String (0.0)
> name: String (0.0)
> name_1: String (0.0)
> OGRFeature(sql_statement):0
>   gid (Integer) = 390
>   prov (String) = ON
>   name (String) = OTTAWA
>   name_1 (String) = OTTAWA-CARLETON REGIONAL MUNICIPALITY
>   MULTIPOLYGON (((-75.747100987514116
> 45.357929043660768,-75.747139025748226 45.
> 357913967904082,-75.748404955984952 45.357349002525353,-75.749320978319517
> 45.35 6940963375777,-75.750800938009831 45.356312037549...................
>
> #####################
>
>
> ogr2ogr ottawa.shp PG:'dbname=fbs user=webserver password=topsecret'
> -sql "select * from canadian_municipalities WHERE name = 'OTTAWA'"
> FAILURE:
> Unable to open datasource `PG:dbname=fbs user=webserver
> password=topsecret' with the following drivers.
>
> 2009/3/25 Even Rouault <even.rouault at mines-paris.org>:
> > Travis,
> >
> > this is really weird. I've reviewed a bit the source of the PG driver and
> > the influence of the read-only mode vs update mode is very thin. The only
> > behavioural differences are :
> > 1) the result of TestCapabilities() method depend on it, but ogrinfo
> > doesn't request layer TestCapabilities().
> > 2) if the PG database has no valid tables (for OGR), its opening will
> > fail in read-only mode, whereas it would succeed in update mode.
> >
> > So in your use case, provided that there's at least one table reported by
> > ogrinfo in update mode, I don't understand why it fails. What's the
> > version of GDAL/OGR you're using ?
> >
> > Le Wednesday 25 March 2009 19:51:39 Travis Kirstine, vous avez écrit :
> >> 2009/3/25 Stephen Woodbridge <woodbri at swoodbridge.com>:
> >> > Jeff McKenna wrote:
> >> >> Travis Kirstine wrote:
> >> >>> I am trying to test a connection to a postgis db  using ogrinfo and
> >> >>> am having difficulties.  I am running the command on the same server
> >> >>> that hosts the postgis db.  I checked the pg_hba.config file to see
> >> >>> if it allows local connections and everything looks OK but I still
> >> >>> get "Failure" when connecting with ogr.
> >> >>>
> >> >>> Am I missing something?
> >> >>>
> >> >>>
> >> >>> command
> >> >>> ogrinfo -ro PG:'host=server34 dbname=earth user=bob
> >> >>> password=suruncle'
> >> >>
> >> >> My thoughts:
> >> >>
> >> >> - since you are on the same server, use host=127.0.0.1
> >> >> - include the port parameter (usually port=5432)
> >> >
> >> > Have you try from the command line:
> >> >
> >> > psql -h server34 -U bob earth
> >> > psql -h localhost -U bob earth
> >> > psql -U bob earth
> >> >
> >> > Should prompt you for the password.
> >> >
> >> > Only the last on will use the local socket or named pipe. If the last
> >> > one works the try:
> >> >
> >> > ogrinfo -ro PG:'host= dbname=earth user=bob password=suruncle'
> >> > ogrinfo -ro PG:'dbname=earth user=bob password=suruncle'
> >> >
> >> > and see if either of these work.
> >>
> >> I got the query / connection to work, it seems that the -ro flag was
> >> causing the problem
> >> which is strange.
> >>
> >> this works
> >> ogrinfo -sql 'SELECT * FROM counties' PG:'host= dbname=earth user=bob
> >> password=suruncle'
> >>
> >> this dosen't
> >> ogrinfo -ro -sql 'SELECT * FROM counties' PG:'host= dbname=earth
> >> user=bob password=suruncle'
> >>
> >> I'm not sure what the problem is as the user bob has su privileges
> >>
> >>
> >>                                List of roles
> >>  Role name | Superuser | Create role | Create DB | Connections | Member
> >> of
> >> -----------+-----------+-------------+-----------+-------------+--------
> >>--- postgres  | yes       | yes         | yes       | no limit    | bob  
> >>        | yes       | no          | yes       | no limit    |