[gdal-dev] PostgreSQL problems

[David J. Bakeman]

Thanks Even and everyone else who responded!  It was the fact that I had 
created the tables without registering the geometry column.  I did that 
and now things appear good to go!  That is ogrinfo is successful and 
lists the three tables with geometry.

Perhaps the normal user of ogr is aware that only geometry columns are 
listed and you get a FAILURE otherwise but perhaps an empty list would 
be better? So I would have gotten:
  INFO: Open of `PG:dbname=mydb'
       using driver `PostgreSQL' successful.

Instead of the FAILURE.  I know the structure of the software might not 
make this possible so maybe a Note in the driver page for PostgreSQL?

Even Rouault wrote:
>> 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.
>>      
> I believe that now you should be able to display their content.
>
> The (none) behind the 2 first one means that OGR detects that they are indeed
> not spatial tables. The absence of it on the 4 last tables means that OGR
> detects a geometry column but cannot determine the precise geometry type :
> that is to say if its a point, line, polygon, etc... But that's OK.
>
> So you could try "env PG_LIST_ALL_TABLES=YES ogrinfo PG:'dbname=mydb'
> pxml_points" and I believe you should see features with their geometry
> reported.
>
>    
>> 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?
>>      
> Hum, my guess is that the way you have created them did not register them in
> the geometry_columns table. OGR only lists by default spatial tables
> registered into that table.
>
> So you could try adding manually an entry in geometry_columns for those tables
> with the appropriate SELECT AddGeometryColumn(....) statement. See PostGIS doc
> for that. After doing so, the PG_LIST_ALL_TABLES=YES will not be necessary.
>
>
>