[postgis-users] How to get geometry enabled Tables form Postgresql/postgis

[venkat]

Dear All,

   How can i retrieve  only spatial enabled tables form the database(Postgresql/PostGIS).Please let me know. 

I am waiting for your great response.

Thanks and Regards,

Venkat

[William Furnass]

On 29 July 2010 13:57, venkat <ven.ta...@gmail.com> wrote:
>
>    How can i retrieve  only spatial enabled tables form the
> database(Postgresql/PostGIS).Please let me know.

You need to identify those tables that have more than one column of
the data type 'geometry' or 'geography'.

Will
_______________________________________________
postgis-users mailing list
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Mike Toews]

You can either assume that they are all listed in geometry_columns or geography_columns. Or you can do a more complicated query to include other tables than in geometry/geography, (this is from QGIS code[1]):

select f_table_name, f_table_schema, upper(type), pg_class.relkind

from geometry_columns, pg_class, pg_namespace

where relname=f_table_name

    and f_table_schema=nspname

    and pg_namespace.oid=pg_class.relnamespace

    and has_schema_privilege(pg_namespace.nspname,'usage')

    and has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select')

order by f_table_schema,f_table_name;

To search geography, replace "geometry_columns" with "geography_columns". Geography column support is new in QGIS, and is only in trunk at the moment.

[1] https://trac.osgeo.org/qgis/browser/trunk/qgis/src/app/postgres/qgspgsourceselect.cpp?rev=13922#L632

-Mike

[Mike Toews]

Oh, sorry, the search that looks for tables not listed in geometry_columns/geography_columns is further down in the QGIS source:

https://trac.osgeo.org/qgis/browser/trunk/qgis/src/app/postgres/qgspgsourceselect.cpp?rev=13922#L714

-Mike