[GIS] Selecting only Spatial tables from PostgreSQL database

postgispostgresqlquery

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.

Best Answer

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

Related Solutions

python – Accessing Non-Spatial PostgreSQL Tables with OGR Python Bindings

You can execute any arbitrary SQL from the connection. For example, to get data from spatial_ref_sys, which is a non-spatial table:

from osgeo import ogr
conn = ogr.Open('PG:dbname=postgis user=postgres')

sql = 'SELECT * FROM spatial_ref_sys LIMIT 10;'
for row in conn.ExecuteSQL(sql):
    print('%s:%d | %s'%(row.auth_name, row.auth_srid, row.proj4text))

[GIS] PostgreSQL/PostGIS: create spatial view

You may be simply getting hung up on terminology - it seems like your view definition is correct:

1) You don't necessarily need to insert the view geometry column into the geometry_columns table - it's not normally done for postgres views;

2) You can alter your SQL a bit to assign the view geometry an SRID:

CREATE OR REPLACE VIEW view_waypAZ AS 
SELECT polR.codeR, polR.nameR, ST_SetSRID(ST_centroid(polR.geom),4326) as geom
FROM boundaryR as polR;

3) The data type of the column is "geometry", and the result of ST_Centroid() is geometry of geometry type "POINT" (not "MULTIPOINT"). If you really need MULTIPOINT geometry type, you could apply ST_Multi() like this:

CREATE OR REPLACE VIEW view_waypAZ AS 
SELECT polR.codeR, polR.nameR, ST_SetSRID(ST_Multi(ST_centroid(polR.geom)),4326) as geom
FROM boundaryR as polR;

Best Answer

Related Solutions

python – Accessing Non-Spatial PostgreSQL Tables with OGR Python Bindings

[GIS] PostgreSQL/PostGIS: create spatial view

Related Question