[postgis-users] Bounding box intersect on multiple spatial tables

[fork]

> What I don't know how to do is to write a query that retrieves
> ALL spatial data (point, line and polygon layers/tables) in the database within
> the specified search area.

My first thought is that you need to query the system tables to get a list of
tables and geo columns in (say) a schema dedicated to handling arbitrary data  .
 Then for each data table, you can create a dynamic query using the returned
table and column names and a bounding box or st_within() function.  Then you can
execute that and append it to a list that the user choose from.  You would need
to do it in a procedural language so that you can dynamically create a SQL
string and execute it -- PL/PGSQL would be my choice -- read the postgres docks
for looping and dynamic queries. 

Sorry that that is a bunch of handwaving -- I don't think this is a trivial
exercise, so I can't knock out the code.  

Here is an example of querying system tables, though: 

select * from information_schema.tables where table_schema =
'data_tables_of_possible_interest'.

If you are going to be adding lots of tables, it might be worth keeping a meta
table with their bbox, a description, etc; then you could just query that.  You
would have to maintain it along with each table change, though.