[postgis-users] "Erase" and "Intersect" performance questions - PostGIS way slower than ArcGIS

[Chris Hermansen]

No problem.

With respect to st_difference(), one of the recommended alternatives is to
st_union() all your polygons, then re-attribute them, then delete the ones
you don't want.  It's my impression that st_union() is pretty fast these
days so this might be a good approach for you.  I have also seen it
suggested that one st_union() all the polygon linework, then
re-polygonalize.  This gets to a fair bit of SQL especially if the polygons
have interior rings.

With respect to your performance issue, your spatial indexes only really
help in the case of relatively compact polygons.  If your land use polygons
include thousand-kilometer-long rights of way or double-line rivers, those
will drastically reduce the performance of your indexing because each of
them will && with many hundreds or thousands of other polygons and therefore
the actual intersection between them must be computed.  Offhand I don't know
what ArcGIS does to be faster at this.  Maybe someone else on this list has
some ideas.

If you can tile your big ugly polygons for sure you will dramatically
improve your performance.

2011/8/25 Sheara Cohen <Sheara at calthorpe.com>

> Hey Chris – Thanks for your response.****
>
> ** **
>
> With regard to ST_Difference, what I was trying to accomplish is a
> “deletion” or  “clipping out” of rivers and lakes from a parcel file. Both
> are vector files. Based on your description, it sounds like I’m using the
> wrong query. Do you know which one I should be using?****
>
> ** **
>
> And with regard to both the ST_Intersection and ST_Difference… yes, I am
> using vector files with HUGE, complex polygons that spread over very large
> areas (hundreds of miles). I can dice these in ArcGIS or something like that
> before throwing the files over the fence into Postgres. But I’m still
> confused by the fact that similar operations run so quickly on the exact
> same files in ArcGIS. I feel like there is something I’m totally missing
> about how to set up these queries correctly in PostGIS.****
>
> ** **
>
> If you or anyone have any last thoughts, I’d be really interested.****
>
> ** **
>
> Thanks again,****
>
> ** **
>
> ~ Sheara****
>
> ** **
>
> *Sheara Cohen*
> Planner****
>
> *C A L T H O R P E**  A S S O C I A T E S*
> 2095 ROSE STREET, SUITE 201, BERKELEY, CALIFORNIA, 94709 USA
> 510 809-1165 (direct) | 510-548-6800 x35 (main) | 510 548-6848 (fax)
> sheara at calthorpe.com | www.calthorpe.com****
>
> ** **
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>


-- 
Chris Hermansen
*Vice President*

TECO Natural Resource Group Limited
301 · 958 West 8th Avenue
Vancouver BC CANADA · V5Z 1E5
Tel +1.604.714.2878 · Cel +1.778.840.4625
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110825/6a6b99bb/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: teco_sig.jpg
Type: image/jpeg
Size: 4928 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110825/6a6b99bb/attachment.jpg>