[postgis-users] Query performance issue

Stephen Woodbridge woodbri at swoodbridge.com
Sun Oct 16 21:16:01 PDT 2005

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
     streets.the_geom && b.the_geom
     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 

Anyway, Thanks again everyone.

More information about the postgis-users mailing list