6

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

Andre Silva
  • 10,259
  • 12
  • 54
  • 106
Tomas
  • 748
  • 1
  • 7
  • 16

2 Answers2

3

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))
Mike T
  • 42,095
  • 10
  • 126
  • 187
  • 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
3

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

Tomas
  • 748
  • 1
  • 7
  • 16