[postgis-users] SQL query to identify tables and views with geometry field

[Armin Burger]

Thanks. I think that the query

SELECT c.relname
FROM pg_attribute a, pg_class c, pg_type t
WHERE a.attrelid=c.oid
   AND a.atttypid=t.oid
   AND t.typname='geometry'
   AND (c.relkind = 'r' OR c.relkind = 'v')

should give the correct results.

Armin

jcradock at me3.com wrote:
> Armin,
> 
> Ah.
> 
> Look in pg_catalog. I believe qGIS is querying these tables. Probably
> pg_attribute joined to pg_type joined to pg_table.
> 
> http://www.postgresql.org/docs/8.1/interactive/catalogs.html
> 
> Jim
> 
> 
>>But this query does not find the views based on tables, even if the
>>tables themselves are registered in geometry_columns. Programs like QGIS
>>and uDig seem to be able to find all tables and views with geometry
>>columns, so I guess there seems to be a SQL query based on PG system
>>tables for this.
>>
>>Armin
>>
>>Sean Gillies wrote:
>>
>>>On Nov 25, 2005, at 12:28 PM, Armin Burger wrote:
>>>
>>>
>>>>Hi everyone,
>>>>
>>>>is there a way to find all tables and views in a PostGIS-enabled
>>>>database that contain a geometry field, using just a SQL query?
>>>>
>>>>Armin
>>>
>>>
>>>If your database is in proper shape
>>>
>>>  select f_table_name from geometry_columns;
>>>
>>>cheers,
>>>Sean
>>>
>>>
>>>
>>>---
>>>Sean Gillies
>>>sgillies at frii dot com
>>>http://zcologia.com/news
>>>
>>>
>>>
>>>_______________________________________________
>>>postgis-users mailing list
>>>postgis-users at postgis.refractions.net
>>>http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>
>>_______________________________________________
>>postgis-users mailing list
>>postgis-users at postgis.refractions.net
>>http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> 
> 
> 
> 
> -----
> James Cradock, jcradock at me3.com
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
>