[gdal-dev] OGR is not able to determine PostGIS table SRS

Tue Jul 7 22:02:58 EDT 2009

Franks,

Thank you for your reply, I did some more checks.  Please see my comments below.

> I am trying to determine an SRS for a table in PostGIS database but I am
> getting NULL.  I have a table that in PostGIS that has SRID defined:
>
> usa=# select ST_SRID(the_geom) from census.counties;
>  st_srid
> ---------
>     4326
>     4326
>
> 
> OGR doesn't look at the SRID of individual geometries, only at the SRID of
> the table as registered in the srid column of the geometry_columns table
> for that target table.

I actually tried both for the whole layer (OGR_L_GetSpatialRef) and for each feature, both are NULL.

> Perhaps you didn't set the SRID in the geometry_columns table?

I checked it, all set:

# select srid from geometry_columns where f_table_name='counties' and f_table_schema='census';
 srid 
------
 4326
(1 row)

>  Or perhaps there is a problem looking up the composite name census.counties?

I created another table in the public schema, same problem: 

==========
# select * into tmp from census.counties;
...
$ ogrinfo -al -ro PG:'host=geodb dbname=usa tables=tmp'
INFO: Open of `PG:host=geodb dbname=usa tables=tmp'
      using driver `PostgreSQL' successful.
INFO: Internal data source name `PG:host=geodb dbname=usa '
      different from user name `PG:host=geodb dbname=usa tables=tmp'.

Layer name: tmp
Geometry: Unknown (any)
Feature Count: 3141
Extent: (-178.217606, 18.921785) - (-66.969269, 71.406242)
Layer SRS WKT:
(unknown)
....
==========

(Even though there may be a separate problem when tables with identical names are present in different schemata.)

> You might try running ogrinfo with the --debug on commandline switch to see
> some details of what is going on internally.

Here it is:

==========
$ CPL_DEBUG=ON ogrinfo -al -ro PG:'host=geodb dbname=usa tables=tmp' 
PG: DBName="usa"
PG: PostgreSQL version string : 'PostgreSQL 8.3.7 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)'
PG: PostGIS version string : '1.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
OGR: OGROpen(PG:host=geodb dbname=usa tables=tmp/0x1000b40) succeeded as PostgreSQL.
OGR: GetLayerCount() = 1

PG: PQexec(SELECT count(*) FROM "tmp" )

PG: PQexec(SELECT Extent("the_geom") FROM "tmp")

PG: PQexec(DECLARE OGRPGLayerReader CURSOR for SELECT AsEWKT("the_geom"), "age_18_21", "age_22_29", "age_30_39", "age_40_49", "age_50_64", "age_5_17", "age_65_up", "age_under5", "ameri_es", "asian", "ave_fam_sz", "ave_hh_sz", "avg_sale97", "avg_size97", "black", "cnty_fips", "crop_acr97", "families", "females", "fhh_child", "fips", "gid", "hawn_pi", "hispanic", "households", "hse_units", "hsehld_1_f", "hsehld_1_m", "males", "marhh_chd", "marhh_no_c", "med_age", "med_age_f", "med_age_m", "mhh_child", "mult_race", "name", "no_farms97", "other", "owner_occ", "pop00_sqmi", "pop07_sqmi", "pop2000", "pop2007", "renter_occ", "sqmi", "state_fips", "state_name", "vacant", "white" FROM "tmp" )
INFO: Open of `PG:host=geodb dbname=usa tables=tmp'
      using driver `PostgreSQL' successful.
INFO: Internal data source name `PG:host=geodb dbname=usa '
      different from user name `PG:host=geodb dbname=usa tables=tmp'.

Layer name: tmp
Geometry: Unknown (any)
Feature Count: 3141
Extent: (-178.217606, 18.921785) - (-66.969269, 71.406242)
Layer SRS WKT:
(unknown)
Geometry Column = the_geom
age_18_21: Integer (0.0)
....
==========

Below is another demonstration of the same problem I think.  I am setting an envelope but the whole query fails because ogr sets a wrong SRS (-1/unknow instead of 4326) to the WHERE clause in the cursor:

==========
$ CPL_DEBUG=ON ogrinfo -al -ro PG:'host=geodb dbname=usa tables=census.counties' -spat -178.217606 18.921785 -66.969269 71.406242  
PG: DBName="usa"
PG: PostgreSQL version string : 'PostgreSQL 8.3.7 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)'
PG: PostGIS version string : '1.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
PG: Primary key name (FID): gid
PG: Using column 'gid' as FID for table 'counties'
OGR: OGROpen(PG:host=geodb dbname=usa tables=census.counties/0x1000ad0) succeeded as PostgreSQL.
INFO: Open of `PG:host=geodb dbname=usa tables=census.counties'
      using driver `PostgreSQL' successful.
INFO: Internal data source name `PG:host=geodb dbname=usa '
      different from user name `PG:host=geodb dbname=usa tables=census.counties'.
OGR: GetLayerCount() = 1

PG: bHasPostGISGeometry == TRUE
PG: OGRPGTableLayer::BuildWhere returns: WHERE the_geom && SetSRID('BOX3D(-178.217606000000 18.921785000000, -66.969269000000 71.406242000000)'::box3d,-1) 

Layer name: census.counties
Geometry: Unknown (any)
PG: PQexec(SELECT count(*) FROM "census"."counties" WHERE the_geom && SetSRID('BOX3D(-178.217606000000 18.921785000000, -66.969269000000 71.406242000000)'::box3d,-1) )

PG: SELECT count(*) FROM "census"."counties" WHERE the_geom && SetSRID('BOX3D(-178.217606000000 18.921785000000, -66.969269000000 71.406242000000)'::box3d,-1) ; failed.
Feature Count: 0
PG: PQexec(SELECT Extent("the_geom") FROM "census"."counties")

Extent: (-178.217606, 18.921785) - (-66.969269, 71.406242)
Layer SRS WKT:
(unknown)
FID Column = gid
Geometry Column = the_geom
age_18_21: Integer (0.0)
age_22_29: Integer (0.0)
age_30_39: Integer (0.0)
age_40_49: Integer (0.0)
age_50_64: Integer (0.0)
age_5_17: Integer (0.0)
age_65_up: Integer (0.0)
age_under5: Integer (0.0)
ameri_es: Integer (0.0)
asian: Integer (0.0)
ave_fam_sz: Real (0.0)
ave_hh_sz: Real (0.0)
avg_sale97: Real (0.0)
avg_size97: Integer (0.0)
black: Integer (0.0)
cnty_fips: String (3.0)
crop_acr97: Integer (0.0)
families: Integer (0.0)
females: Integer (0.0)
fhh_child: Integer (0.0)
fips: String (5.0)
hawn_pi: Integer (0.0)
hispanic: Integer (0.0)
households: Integer (0.0)
hse_units: Integer (0.0)
hsehld_1_f: Integer (0.0)
hsehld_1_m: Integer (0.0)
males: Integer (0.0)
marhh_chd: Integer (0.0)
marhh_no_c: Integer (0.0)
med_age: Real (0.0)
med_age_f: Real (0.0)
med_age_m: Real (0.0)
mhh_child: Integer (0.0)
mult_race: Integer (0.0)
name: String (32.0)
no_farms97: Integer (0.0)
other: Integer (0.0)
owner_occ: Integer (0.0)
pop00_sqmi: Real (0.0)
pop07_sqmi: Real (0.0)
pop2000: Integer (0.0)
pop2007: Integer (0.0)
renter_occ: Integer (0.0)
sqmi: Real (0.0)
state_fips: String (2.0)
state_name: String (25.0)
vacant: Integer (0.0)
white: Integer (0.0)
PG: PQexec(DECLARE OGRPGLayerReader CURSOR for SELECT "gid", AsEWKT("the_geom"), "age_18_21", "age_22_29", "age_30_39", "age_40_49", "age_50_64", "age_5_17", "age_65_up", "age_under5", "ameri_es", "asian", "ave_fam_sz", "ave_hh_sz", "avg_sale97", "avg_size97", "black", "cnty_fips", "crop_acr97", "families", "females", "fhh_child", "fips", "hawn_pi", "hispanic", "households", "hse_units", "hsehld_1_f", "hsehld_1_m", "males", "marhh_chd", "marhh_no_c", "med_age", "med_age_f", "med_age_m", "mhh_child", "mult_race", "name", "no_farms97", "other", "owner_occ", "pop00_sqmi", "pop07_sqmi", "pop2000", "pop2007", "renter_occ", "sqmi", "state_fips", "state_name", "vacant", "white" FROM "census"."counties" WHERE the_geom && SetSRID('BOX3D(-178.217606000000 18.921785000000, -66.969269000000 71.406242000000)'::box3d,-1) )
PG: PQclear() on an error condition
==========