Selecting only Spatial tables from PostgreSQL database?

Question

My database contains both Spatial and Non-Spatial tables, but I want to retrieve only Spatial table from the query.

Any suggestions to select only Spatial tables. 'the_geom' is the geometry column in the spatial table.

Otherwise, is it possible to select tables from its column name.

I tried with this code select relname from pg_stat_user_tables WHERE schemaname='public' ; but from this we get all table names.

Answer 1

All spatial table references are held in the geometry_columns metadata table. So try:

select * from geometry_columns

and you should get just the spatial tables

• Kishor: Thank u very much...I think I have missed a simple thing
• Kishor: I got another code, SELECT table_name FROM information_schema.columns WHERE column_name = 'the_geom'
• mapoholic: yip- that's the long way around :-)
• Kishor: ya, you are correct.
• RyanKDalton: @kishor, you should add your comment as an "answer", just so people see it as an option, too, even if its been established that mapoholic's answer may be the preferred method.

Answer 2

Another one to select only spatial tables in database..

SELECT table_name FROM information_schema.columns WHERE column_name = 'the_geom'`

Using this code we can also retrieve table info by knowing its column name.

Answer 3

Short way

select * from geometry_columns

Deeper way

SELECT table_name FROM information_schema.columns WHERE column_name = 'the_geom' or column_name = 'wkb_geometry'

The second option should work even if the information of geometry_columns have been deleted. The 'wkb_geometry' is the default name of geometry data columns if you used ogr2ogr tool to feed your database.