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.

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.

Thanks in advance..

share|improve this question

3 Answers 3

up vote 11 down vote accepted

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

share|improve this answer
    
Thank u very much...I think I have missed a simple thing –  kishor Aug 8 '12 at 6:58
1  
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

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.

share|improve this answer

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.

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.