I'm trying to query the_geom value for all spatial tables in our DB.
For one, I know I can get the tables in question with:
select * from geometry_columns where f_table_name like 'ParcelPoints%';
But is it possible to get all the geometry values from all tables at once?
Ultimately, I'd like to perform something like the following where '...public."ParcelPoints_01001"...' is the result of all geometry values of all tables:
select the_geom from public."ParcelPoints_01001" WHERE ST_DWithin(the_geom, ST_MakePoint(-86.852470,32.628280)::geography, 1000) limit 3000;
1 Answer 1
You need to explicitly refer to all tables with geometric field types, for example:
select public.tbl1.geom as geom1, public.tbl2.geom as geom2,
public.tbl3.geom as geom3
from public.tbl1, public.tbl2, public.tbl3
WHERE ST_DWithin(public.tbl1.geom, ST_MakePoint(-86.852470,32.628280)::geography, 1000)
or
ST_DWithin(public.tbl2.geom, ST_MakePoint(-86.852470,32.628280)::geography, 1000)
or
ST_DWithin(public.tbl3.geom, ST_MakePoint(-86.852470,32.628280)::geography, 1000)
limit 3000;
To view all your tables in the public schema, run the script:
SELECT n.nspname AS "Schema",
c.relname AS "Name",
CASE c.relkind WHEN'r'THEN'table'
WHEN'v'THEN'view'
WHEN'i'THEN'index'
WHEN'S'THEN'sequence'
WHEN's'THEN'special'
END AS"Type",
r.rolname AS "Owner",
pg_catalog.obj_description(c.oid,'pg_class')AS "Description"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN('r')
AND n.nspname <>'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid);
In order to get all the geometric tables, run the script:
SELECT * from information_schema.columns WHERE column_name = 'geom'