[gdal-dev] PostgreSQL problems
David J. Bakeman
dbakeman at comcast.net
Fri Oct 14 17:44:33 EDT 2011
Even Rouault wrote:
> Le vendredi 14 octobre 2011 22:57:32, David J. Bakeman a écrit :
>
>> Even Rouault wrote:
>>
>>> Le vendredi 14 octobre 2011 21:04:12, David J. Bakeman a écrit :
>>>
>>> Try ogrinfo --debug on -ro PG:'dbname=mydb'
>>>
>> OK I am totally confused now here is the output:
>> ogrinfo --debug -ro PG:'dbname=mydb'
>> INFO: Open of `PG:dbname=mydb'
>> using driver `PostgreSQL' successful.
>>
> I asked for "--debug on", not just "--debug". The latter will not work as a
> debugging option : it will "eat" the following -ro.
>
> So basically, it is as if you had type ogrinfo PG:'dbname=mydb' without the -
> ro option, so in update mode
>
> But that's interesting. Because actually when the PG driver cannot find any
> Postgis layer in a database opened as read-only, it returns as if there was an
> error (that behaviour might be discussed). Whereas when you open in update
> mode, it succeeds (the justification being that in update mode, you can create
> a new layer in an empty database).
>
> All in all, it means that the driver doesn't recognize valid Postgis layers
> (that is to say tables who have a geometry column referenced in the
> geometry_column table) in your database.
>
> You can try defining the environment variable PG_LIST_ALL_TABLES=YES as
> documented in http://gdal.org/ogr/drv_pg_advanced.html .
>
OK now we are getting somewhere! I did the following:
env PG_LIST_ALL_TABLES=YES ogrinfo --debug on PG:'dbname=mydb'
PG: DBName="mydb"
PG: PostgreSQL version string : 'PostgreSQL 8.4.9 on
x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.5.1 20100924 (Red
Hat 4.5.1-4), 64-bit'
PG: PostGIS version string : '1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
PG: Field f_table_name is of unknown format type name (type=name).
PG: Field f_table_catalog is of unknown format type name (type=name).
PG: Field f_table_schema is of unknown format type name (type=name).
PG: Field f_geography_column is of unknown format type name (type=name).
PG: Primary key name (FID): id
PG: Using column 'id' as FID for table 'products_swordxml'
OGR: OGROpen(PG:dbname=mydb/0xbd4940) succeeded as PostgreSQL.
INFO: Open of `PG:dbname=mydb'
using driver `PostgreSQL' successful.
OGR: GetLayerCount() = 6
1: geography_columns (None)
2: products_swordxml (None)
3: pxml_points
4: sword_trap
5: sword_trap2
6: sword_trapz
Now I know the last 3 tables/layers do have geometry because I can
display them in QGIS.
The original table products_swordxml was given to me as a mysql file and
has no gemetry per se.
There are coordinates embedded in the table. So I did a create table as
select *, geomfromtext etc
to get the geometry into geometry. Could this be why ogr is not seeing
it as geometry even though qgis does?
Or do the above unknown format type messages mean the mysql conversion I
attempted has screwed something up?
I'm willing to do the work just hoping for a tip in the right direction.
Thanks you guys have already helped a bunch!
>
>> That's all though? I expected it to give a list of the layers as it
>> says in the driver docs.
>>
>> By the way without the --debug it still says FAILURE: and list all of
>> the drivers????
>>
>>
>>> and see if something interesting shows up (and post the output to the
>>> list if you don't find the solution by yourself)
>>>
>>> It is really weird that it works with QGIS but not with OGR. That
>>> eliminates the usual suspects, like insufficient rights on the postgis
>>> "system" tables (geometry_columns, spatial_ref_sys), as a cause for the
>>> failure.
>>>
>>>
>>>> Sorry I was trying to limit the bandwidth I used I figured it was
>>>> probably something really stupid and someone would just say you forgot
>>>> to XX :<;
>>>>
>>>> Anyway this is a test database so it really is named mydb and I copied
>>>> the ogrinfo command from the PostgreSQL driver page for OGR. I checked
>>>> before I started to confirm that the version of gdal I'm using supports
>>>> Postgresql and the ogrinfo --formats prints -> "PostgreSQL"
>>>> (read/write) so I believe that confirms it.
>>>> so both ogrinfo -ro PG:'dbname=mydb' and ogrinfo -ro PG:"dbname='mydb'"
>>>> and ogrinfo -ro PG:"dbname='mydb' user='dbakeman' port='5432'" return
>>>> the same failure which is to print FAILURE: and a list of drivers which
>>>> again includes postgresql. I skipped the password part because I'm on a
>>>> system where I'm happy to run the ident authentication locally at least.
>>>>
>>>> Here's what happens if I do psql -d mydb
>>>>
>>>> \d
>>>>
>>>> List of relations
>>>>
>>>> Schema | Name | Type | Owner
>>>>
>>>> --------+-------------------+-------+----------
>>>>
>>>> public | geography_columns | view | dbakeman
>>>> public | geometry_columns | table | dbakeman
>>>> public | products_swordxml | table | dbakeman
>>>> public | pxml_points | view | dbakeman
>>>> public | spatial_ref_sys | table | dbakeman
>>>> public | sword_trap | table | dbakeman
>>>>
>>>> Which is a list of the tables I've created.
>>>>
>>>> Eli Adam wrote:
>>>>
>>>>> David,
>>>>>
>>>>> It probably is a configuration or simple error, however, you've
>>>>>
>>>>> not given much detail to let other people try to guess what it might
>>>>> be. Kyle asked for the output of your command. Also, the exact
>>>>> command (copied from terminal) may help too. I looked at the OGR
>>>>> PosrgreSQL/PostGIS format page and copied this:
>>>>>
>>>>> PG:"dbname='databasename' host='addr' port='5432' user='x'
>>>>> password='y'"
>>>>>
>>>>> Could you try
>>>>>
>>>>> ogrinfo PG:"dbname='mydb' host='localhost' port='5432' user='x'
>>>>> password='y'"
>>>>>
>>>>> from the host machine?
>>>>>
>>>>> What do you get?
>>>>>
>>>>> PostgreSQL requires very complex quoting if you have any CAPs, do you
>>>>> have any caps in the db name, table, name, fieldname, etc? With lack
>>>>> of information, I'm making random guesses that may not be relevant.
>>>>> I'm sure that if you provide detailed information, someone
>>>>> knowledgeable will quickly help you (or at least that is what I often
>>>>> observe on this list).
>>>>>
>>>>> Bests, Eli
>>>>>
>>>>> On Thu, Oct 13, 2011 at 5:10 PM, David J. Bakeman<dbakeman at comcast.net>
>>>>>
>>> wrote:
>>>
>>>>>> Mateusz Łoskot wrote:
>>>>>>
>>>>>>> On 13 October 2011 23:47, David J. Bakeman<dbakeman at comcast.net>
>>>>>>>
>>> wrote:
>>>
>>>>>>>> I'm on Fedora core 14 with gdal 1.7.3.
>>>>>>>>
>>>>>>>> I setup postgre and created a spatial enabled database with a table
>>>>>>>> that includes geometry. I can connect using psql mydb. However
>>>>>>>> when I try ogrinfo -ro PG:dbname=mydb it fails saying no driver
>>>>>>>> found.
>>>>>>>>
>>>>>>> ogrinfo --formats | grep -i post
>>>>>>>
>>>>>> -> "PostgreSQL" (read/write)
>>>>>>
>>>>>> I really think it's some configuration thing but I don't know what it
>>>>>> is?
>>>>>>
>>>>>>
>>>>>>> and see if your GDAL/OGR installation has built-in PostgreSQL
>>>>>>> support.
>>>>>>>
>>>>>>> Best regards,
>>>>>>>
>>>>>> _______________________________________________
>>>>>> gdal-dev mailing list
>>>>>> gdal-dev at lists.osgeo.org
>>>>>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>>>>>
>>>> _______________________________________________
>>>> gdal-dev mailing list
>>>> gdal-dev at lists.osgeo.org
>>>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>>>
>> _______________________________________________
>> gdal-dev mailing list
>> gdal-dev at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>
More information about the gdal-dev
mailing list