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 <[email protected]> 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
