[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] Bounding box intersect on multiple spatial
From:       fork <forkandwait () gmail ! com>
Date:       2011-05-24 16:05:27
Message-ID: loom.20110524T174737-173 () post ! gmane ! org
[Download RAW message or body]


> 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.

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]