[postgis-users] ST_Intersects of a diagonal buffer on a polygon grid
pramsey at opengeo.org
Thu Jan 29 18:38:57 PST 2009
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.
On Thu, Jan 29, 2009 at 5:38 PM, Michael Toews <mwtoews at sfu.ca> wrote:
> 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.
> postgis-users mailing list
> postgis-users at postgis.refractions.net
More information about the postgis-users