10

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.

15

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

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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