13. November 2020
mika
GIS
Recently I had to automate postgis export via ogr2ogr. In order to list all the sptatial table a following query came in handy:
select
t.table_name,
c.column_name
from
information_schema.tables t
left outer join information_schema.columns c on t.table_name = c.table_name
where
t.table_schema = 'public' and
t.table_name like 'vw_%' and
c.column_name = 'wkb_geometry'
and the actual command generation
select
'ogr2ogr -f "ESRI Shapefile" d:\temp\' || t.table_name || '.shp PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" "' || t.table_name || '"'
from
information_schema.tables t
left outer join information_schema.columns c on t.table_name = c.table_name
where
t.table_schema = 'public' and
t.table_name like 'vw_%' and
c.column_name = 'wkb_geometry'