4

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

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.