[mapserver-users] ogr connection to postgis problems

[Travis Kirstine]

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    |
>
>
>



-- 
Travis K.

Toronto, Canada
------------------------------------------------------------
"She knows there's no success like failure
and that failure's no success at all."
-Bob Dylan-
------------------------------------------------------------