So I have a bounding box drawn by the user on Leaflet from which i can get features of specific table as follows SELECT * FROM $table WHERE $table.geom && ST_MakeEnvelope($test, 4326) where $table is the variable holding table names selected by the user and $test is the BBOX coordinates.

This works fine, I get the list of features within the BBOX of that selected table.

Now if I have to List all tables in my database within the BBOX how would I proceed?

  • you mean all geometries from all tables in your DB that are within the BBOX? – ThingumaBob Dec 3 at 13:06
  • Preferably just the table names which holds these geometries, but your answer seems interesting. I'm not sure which function to use. – Amruth Kiran Dec 4 at 5:11
  • SELECT * FROM information_schema.columns WHERE table_schema='public' AND column_name='geom' This lists the tables with the column name as geom. Full code: SELECT * FROM information_schema.columns WHERE table_schema='public' AND column_name='geom' && ST_MakeEnvelope(77.52914428710939,12.857326637394761,77.71041870117189,13.00990996390651, 4326); ST_MakeEnvelopeworks around these geometries should list only the geometries within the BBOX, but error; "ge" <-- parse error at position 2 within geometry – Amruth Kiran Dec 4 at 10:19
  • nah, that's not going to work, you are only getting names of all relations and columns (as VARCHAR), not the relations themselves. do your tables have the same column structure? – ThingumaBob Dec 4 at 10:36
  • Yes, all of them have been ingested using Shp2pgsql. So they have all the same columns where the geom column is what i want to calculate with. – Amruth Kiran Dec 4 at 10:43
up vote 1 down vote accepted

In a more general sense: there is no simple, functional way for a dynamic cross-table query, for neither case (selecting geometries or listing tables). No matter how you gather the data in the end, you will first need to read the table names from a system table (I'd go for the geometry_columns view here) and loop over them (dynamic SQL, PL/PGSQL), either within a function or an anonymous function block (DO).

Now, there are things to consider due to the fact that you have the same column structure in all tables; since then, most of the times and up to a certain table size, there's no reason to not hold all that data in one table, with an (indexed) attribute representing the cause of you separating the files (e.g. province name or whatever). shp2pgsql can easily do that with the -a (append) flag.

All problems solved.

However, if you are working with (very) large or growing data sets and/or e.g. time dependencies (e.g. DATE/TIMESTAMPS) that separates the data semantically and uniquely, you might actually want to consider true partitioning (or specialized extensions like TimescaleDB for PostgreSQL) instead.


If you don't want or can't use any of that, for your own reasons, there is an elegant way that can be derived from the partitioning workflow in <10 versions of PostgreSQL: table inheritance.

Create a parent table, having the exact column structure of your (soon to be) child tables, and let all of them INHERIT from it:

CREATE TABLE _superset (<name DATATYPE>, ...);

DO
  $$
    DECLARE
      _rec RECORD;
    BEGIN
      FOR _rec IN
        SELECT f_table_schema AS ts,
               f_table_name AS tn
        FROM   geometry_columns
        WHERE  f_table_schema = 'public'
      LOOP
        EXECUTE FORMAT (
                  '
                  ALTER TABLE %1$s.%2$s
                    INHERIT _superset;
                  ',
                  _rec.ts, _rec.tn
                );
      END LOOP;
    END;
  $$;

(Btw., the DO command I used would be at the core of what I proposed about writing a custom function.)

You can now run a query on _superset that will query all child tables

SELECT *
FROM   _superset AS a
WHERE  a.geom && ST_MakeEnvelope(<geometry>, 4326);

or query any child table directly

... FROM <child_table_name> ...

to limit the query to the one you specified.

Important: For any data manipulation (e.g. INSERT/UPDATE/DELETE), or generally anything other than selecting, refer to the child tables directly (or get acquainted with the more delicate sideeffects and limitations of inheritance)!

The parent table will also have a special column tableoid that holds the systems OID of the physical relation each row is stored in; this allows you to do a bunch of nice things, including getting your list of tables:

SELECT DISTINCT b.relname
FROM   _superset AS a
JOIN   pg_class AS b
  ON   a.tableoid = b.oid
WHERE  a.geom && ST_MakeEnvelope(<geometry>, 4326);

Neat.


Notes:

  • Create any indexes on the child tables directly.

  • You cannot simply delete the parent table, or any child tables; to undo the inheritance, run the DO command with ... NO INHERIT ... in the ALTER TABLE ... loop. Then delete the parent table.

  • There are more limitations and caveats (see the link) and things to know about, especially concerning constraints and such. But for the sole purpose of your question, this should work just fine.

  • "..there's no reason to not hold all that data in one table.." I never gave this a thought actually, especially since I was using Shp2pgsql (subprocessed via Python) to import numerous shapefiles. Thanks for clarifying the "-a" option, this would save a lot of time and effort. "you might actually want to consider true partitioning (or specialized extensions like TimescaleDB for PostgreSQL) instead" This, would this be a probable tool for spatial data as well? Since most of my work involves such data almost entirely. Thank you, I've accepted your answer. – Amruth Kiran Dec 5 at 10:19

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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