ST_Intersects takes too long time

[gpetit]

I'm using the last realease of OrbisGIS.

I'm trying to select buildings from the departement 56 (table "BATI_INDIFFERENCIE") which intersects the agglomeration of Vannes.

So I created a unique polygon that represent my study area (table STUDY).
Then I created spatial indexes on both tables.

Now, I execute the following SQL instruction

DROP TABLE IF EXISTS TOTO;
CREATE TABLE TOTO AS SELECT a.* 
    FROM BATI_INDIFFERENCIE a, STUDY b 
WHERE a.THE_GEOM && b.THE_GEOM and ST_INTERSECTS(a.THE_GEOM, b.THE_GEOM);

The process is really too long (no result after 5mn).

I tried with or without spatial index on both tables, but the result is the same

@ebocher @nicolas-f

[nicolas-f]

Can you post the WKT of STUDY.the_geom ?

[gpetit]

STUDY.zip

I upload the shape file instead, because the WKT is too long for GitHub

[nicolas-f]

You test geometry is composed by 31789 points. That explains the query time.

[nicolas-f]

In order to optimize this, I would go for a st_polygonize.

[gpetit]

Yes this geometry is complex (even if it's a simple geometry and there is no hole), but this kind of process used to works well in the past.

I'll try ST_Polygonize

[nicolas-f]

Sorry I was talking about tessellate

create table studypol as select * from st_explode('select ST_TESSELLATE(the_geom) the_geom from study');

[nicolas-f]

then use spatial index on studypol

[gpetit]

Thanks @nicolas-f it works. The process takes 47s but I have a result !

[nicolas-f]

I'm curious to know how many times PostGIS require to do intersection with a 31789 points geometry and thousands of buildings.

[gpetit]

No result with QGis after 10mn (QGis freezed).
... but there is no spatial indexes

[ebocher]

Tested on a local postgis server, this is the same pb

[gpetit]

... and same with spatial indexes on QGis --> freeze

[ebocher]

Solved ?

[gpetit]

Yes we can close