[gdal-dev] PostgreSQL problems

[Even Rouault]

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