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

[Paul Ramsey]

Bruteforce-wise, upgrading to 1.4 and GEOS 3.1 will bring you prepared
geometries which will make things faster.

However, given your basic apparent question "what cells are within N
distance of this line?" a much faster approach, which doesn't require
upgrading, is as follows:

First, don't use just one big diagonal line. Break it down into 10 or
20 or more. Why? Because one diagonal line has a *huge* bounding box
(which is what the index works on) while 20 equivalent shorter lines
have a much smaller combined box area. So the index will be more
selective and things will be faster.

Second, don't use this construction: ST_Intersects(Buffer(GeomA, D), GeomB)
Use this one: ST_DWithin(GeomA, GeomB, D)
It should be massively more efficient.

Paul

On Thu, Jan 29, 2009 at 5:38 PM, Michael Toews <mwtoews at sfu.ca> wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>