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.

How can I get the tablename, Srid and GeometryType of all spatial tables in Oracle(I use 11g)?

In postgis, I can do something like

select f_table_name,srid,type from public.geometry_columns order by f_table_schema, f_table_name;

Does anything similar exist in Oracle?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

The easy way is to select * from user_sdo_geom_metadata or select * from all_sdo_geom_metadata. This isn't as thorough as pecoanddeco's answer would provide but if your metadata is up to date it's pretty good.

Note that individual geometry rows can have different SRIDs from the metadata (although that will break spatial indexing). For geometry types your best bet is to look at the parameters on the spatial index, it's common (but not essential) to have a layer_gtype= parameter, eg layer_gtype=point.

share|improve this answer
add comment

I've not got a copy of Oracle running here at the moment but the ALL_TAB_COLUMNS table will give you a list of all columns and their related tables in the database, so you should be able to filter this to give you a list of the geometry tables.

See http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2091.htm

I'm not sure you can get a list of the SRID and geometry types per table though as I think this is set on the geometry object itself rather than on each table, i.e. each geometry column can contain a mixture of geometry types and SRIDs.

share|improve this answer
    
"each geometry column can contain a mixture of geometry types and SRIDs"..thats strange! why would they do that :( –  vinayan Jan 7 '13 at 11:31
    
Actually, try the USER_SDO_GEOM_METADATA or ALL_SDO_GEOM_METADATA views. These contain spatial info for tables. –  pecoanddeco Jan 7 '13 at 11:42
add comment

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.