[postgis-users] Query performance issue

Robert Burgholzer rburghol at chesapeakebay.net
Mon Oct 17 07:05:41 PDT 2005


So what do queries A-D look like? (or were they in a previous post)?

r.b.

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
-- 
Non-point Source Data Analyst
University of Maryland, College Park
Chesapeake Bay Program Office
410 Severn Avenue, Suite 305B
Annapolis, MD, 21403
Phone: (410) 267-5779

rburghol at chesapeakebay.net




More information about the postgis-users mailing list