1

I have this query to know if a layer intersects with a buffer:

    WITH buffer AS (
        SELECT ST_Buffer(ST_GeomFromText('POINT(3056563.45195278804692.195990143)',48402),1000) AS geom
    )    

    SELECT aixspon.geom 
    FROM aixspon, buffer 
    WHERE ST_Intersects(aixspon.geom, buffer.geom)

Now i want to do this for 1,000 different layers whit the same buffer and know wich of them are inside(intersects) that buffer.

Also i have a table with a list of all the layers name:

    id    scientific_name     common_name     season     shape_name
    __________________________________________________________________
    1       Aix sponsa         Wood duck      Winter       aixspon
    2       Anas acuta      Northern pintail  Winter       anaacut
    3     Anas americana     American wigeon  Winter       anaamer
    .           .                    .           .            .
    .           .                    .           .            .
    .           .                    .           .            .
   1000   Trogon violaceus   Guianan trogon    Summer      troviol

I want a single table like this but only with the records that intersect with the buffer (ST_Intersects = TRUE).

How can I achieve this?

2
  • Will your list of 1000 tables change and is there content changing a lot? Also approximate amount of rows and type of geometries
    – MickyT
    Aug 24 '16 at 19:59
  • The list of 1000 tables is not going to change. The rows of each layer (table) is variable, between 300 and 5000 rows for each one. and all are POLYGONS.
    – Hassim
    Aug 24 '16 at 21:26
3

My feeling is that a materialized view over the tables you have will be the best way of achieving what you want. This will allow you to do a query against a single source with it's own index while maintaining the data in it's separate tables.

Once you have one of these in place you can do the query that you have above, but against the view.

You can create the script for the view using a query like the following. This will output the statement to create the view (output it to a file from a pgadmin query)

SELECT STRING_AGG('SELECT id, scientific_name, common_name, season, geom, ''' || table_name || ''' shape_name FROM ' || table_name, ' 
UNION ALL 
') AS "CREATE MATERIALIZED VIEW myviewname AS"
FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema = 'public' -- make this more selective

Create appropriate indexes on the view as required and refresh it when base data is changed or as you please.

1
  • You are a lifesaver! Amazing method! i didn't know anything about "materialized views". Also the speed of query increase in 200%. I'll post the procedure for the record. Many Thanks!
    – Hassim
    Aug 25 '16 at 19:32
1

Like i said 'MickyT' thank you very much for the idea and the example, i changed a little the query to adapt to my needs:

1) I generated the text for create the query of the 'materialized view' with:

SELECT STRING_AGG('SELECT birds.' || table_name || '.geom, ''' || table_name || ''' shape_name FROM birds.' || table_name   , ' 
UNION ALL 
') AS "CREATE MATERIALIZED VIEW birds_materialized_view AS" 
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' AND table_schema = 'birds'

2) With the results i create the 'materialized view' with spatial index:

CREATE MATERIALIZED VIEW birds.birds_materialized_view AS
SELECT birds.vire_flavgw.geom, 'vire_flavgw' shape_name 
FROM birds.vire_flavgw 
UNION ALL 
SELECT birds.abei_abeigw.geom, 'abei_abeigw' shape_name 
FROM birds.abei_abeigw 
UNION ALL 
.
.
.
SELECT birds.vire_brevgw.geom, 'vire_brevgw' shape_name 
FROM birds.vire_brevgw;

CREATE INDEX birds_materialized_view_gix
ON birds_materialized_view USING GIST (geom);

3) I run the original query but using the 'materialized view':

WITH buffer AS
(
    SELECT ST_Buffer(ST_GeomFromText('POINT(2960010.81493434    1538031.4828732684)',48402),500) AS geom
)

SELECT DISTINCT ON (scientific_name) scientific_name, common_name
FROM
(
    SELECT shape_name
    FROM birds.birds_materialized_view,buffer 
    WHERE ST_Intersects(birds.birds_materialized_view.geom,buffer.geom)
    ORDER BY shape_name ASC
)AS birds
INNER JOIN public.bird_list ON birds.shape_name=public.bird_list.nombre_shape
ORDER BY scientific_name ASC

The time of process 1000 layers reduces from 15min to only 40 seconds. Amazing!

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.