Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

When connecting to PostgreSQL/PostGIS using the OGR Python bindings is it possible to get non-spatial table names (i.e. OGR layers) from the connection?

Currently, doing:

conn = ogr.Open("PG: #params")
for layer in conn:
  print layer.GetName()

Will only print the names of spatial tables, however I can access non-spatial tables by (for example) directly specifying the table name, e.g.:

layer = conn.GetLayerByName('non_spatial_table_name')

The OGR docs refer to the "PG_LIST_ALL_TABLES=YES" parameter, but I can't find how this links to API (let alone the Python SWIG bindings!).

Any pointers appreciated.

Thanks,

Tom

share|improve this question

2 Answers 2

You can execute any arbitrary SQL from the connection. For example, to get data from spatial_ref_sys, which is a non-spatial table:

from osgeo import ogr
conn = ogr.Open('PG:dbname=postgis user=postgres')

sql = 'SELECT * FROM spatial_ref_sys LIMIT 10;'
for row in conn.ExecuteSQL(sql):
    print('%s:%d | %s'%(row.auth_name, row.auth_srid, row.proj4text))
share|improve this answer
    
Thanks Mike, what I'm aiming for is to check whether a non-spatial table exists at runtime (i.e. checking table name against user input). I could use your method to query pg_tables by tablebname instead. –  Tomas Jan 23 '12 at 22:40
    
@thom if you can read SQL/C++, you can pull some snippets from the source to query pg_class to find spatial and non-spatial tables from GDAL or QGIS –  Mike T Jan 23 '12 at 23:46
up vote 1 down vote accepted

For reference, it is possible to set GDAL configuration options using gdal.SetConfigOption().

To list all tables:

import osgeo.gdal as gdal
import osgeo.ogr as ogr

gdal.SetConfigOption("PG_LIST_ALL_TABLES", "YES")

conn = ogr.Open("PG: #params")
for layer in conn:
    print layer.GetName()
    # Will print all tables including non-spatial ones.

You can see further examples of gdal.SetConfigOption in the GDAL/OGR test suite PostGIS module: http://svn.osgeo.org/gdal/trunk/autotest/ogr/ogr_pg.py

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.