[postgis-users] Query performance issue

[Stephen Woodbridge]

Robert Burgholzer wrote:
> So what do queries A-D look like? (or were they in a previous post)?
> 
> r.b.

All the queries were structurally identical to the update below only 
each referenced a separate polygon table called "polygon_layer" below.

The exact queries were also list in previous emails.

-Steve

> On Mon, 2005-10-17 at 00:16, Stephen Woodbridge wrote:
> 
>>I want to thank everyone for their help, suggestions and in general all 
>>the support I got from the list. Here are the results:
>>
>>I had to do 4 updates like the following:
>>
>>update streets set boundary=b.name from polygon_layer b
>>where
>>     streets.the_geom && b.the_geom
>>   and
>>     intersects(streets.the_geom, b.the_geom)
>>
>>streets has just under 2 million records
>>
>>the polygon_layers and run times follow. The size of the polygons in 
>>spatial area decrease from A -> D
>>
>>layer| #poly | time hr | function
>>---------------------------------
>>A    |    14 |    *    | n/a
>>B    |   230 |   30    | intersects()
>>C    |  3991 |   33    | distance() <=0
>>D    |  2432 |    3    | intersects()
>>
>>I tried A first and aborted it after to separate 15 hr runs and 
>>ultimately solve that one a different way by updating the records based 
>>on min and max gid values. After have some success with D, I decided to 
>>run the others and see how long it took.
>>
>>I guess I should look at the GEOS code and see how it works. Does anyone 
>>know if it uses the Sutherland line clipping algorithm which is probably 
>>the fastest way to test if two segments intersect without computing the 
>>intersection.
>>
>>Anyway, Thanks again everyone.
>>   -Steve
>>_______________________________________________
>>postgis-users mailing list
>>postgis-users at postgis.refractions.net
>>http://postgis.refractions.net/mailman/listinfo/postgis-users