[postgis-users] ST_Intersects of a diagonal buffer on a polygon grid

[Michael Toews]

Hi,

I'm having some difficulties with a query that selects 1km×1km grid cells from a large polygon grid 494km×421km (207974 cells) that intersect a thin polygon that covers diagonally across the region:

+-------------+
|\            |
| \_____      |
|       |_    |
|         |   |
|          \__|
+-------------+

The query takes forever (+1 hour) to do on a modern computer, and I have noticed significant differences in time depending on how many points the diagonal polygon has. (The example below has few points for this feature, so it takes about 60 seconds).

Here is the code to recreate my problem:

-- The Grid --

CREATE TABLE throwaway_grid(gid SERIAL PRIMARY KEY);
SELECT AddGeometryColumn('public', 'throwaway_grid', 'the_geom', -1, 'POLYGON', 2);

INSERT INTO throwaway_grid(the_geom)
SELECT ST_Translate(ref.boxrep, hor.n*width, ver.n*height) As slice
FROM (SELECT ST_SetSRID(CAST('BOX(0 0,1000 1000)' as box2d), -1) as boxrep, 1000 as width, 1000 as height) As ref, generate_series(0,493) as hor(n), generate_series(0,420) as ver(n);

CREATE INDEX idx_throwaway_grid_the_geom ON throwaway_grid USING gist(the_geom);

-- The Diagonal Polygon --

CREATE TABLE throwaway_polybuff(gid SERIAL PRIMARY KEY);
SELECT AddGeometryColumn('public', 'throwaway_polybuff', 'the_geom', -1, 'POLYGON', 2);

INSERT INTO throwaway_polybuff(the_geom)
SELECT ST_Segmentize(ST_Buffer(GeomFromText('LINESTRING(200 403000,186000 367000,254000 134000,424000 23000,424000 23000)', -1), 1000), 5000);
-- No need for an index on a 1 row table

-- The Query --
-- takes about 60 seconds on my computer

SELECT g.*
FROM throwaway_grid g, throwaway_polybuff p
WHERE ST_Intersects(g.the_geom,p.the_geom)


The timing of the query is dependant on the parameter used in ST_Segmentize (I'm using 5km segments here). It seems to me that many of the bounding boxes intersect due to the geometries, however it also seems that the outline of "throwaway_polybuff" is traced and checked for each of the bbox intersects. Is there any reasonable way to do this query more efficiently, such as tracing the "throwaway_polybuff" object once?

Thanks in advance.
-Mike