11

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.

Improve this question

3 Answers 3

Reset to default
19

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

Improve this answer
5
  • Thank u very much...I think I have missed a simple thing
    – Kishor
    Commented Aug 8, 2012 at 6:58
  • 2
    I got another code, SELECT table_name FROM information_schema.columns WHERE column_name = 'the_geom'
    – Kishor
    Commented Aug 8, 2012 at 7:01
  • yip- that's the long way around :-)
    – mapoholic
    Commented Aug 8, 2012 at 7:09
  • ya, you are correct.
    – Kishor
    Commented Aug 8, 2012 at 7:17
  • 1
    @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.
    – RyanKDalton
    Commented Aug 8, 2012 at 14:19
3

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.

Improve this answer
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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