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

[Chris Hermansen]

Sheara, a couple of comments.

With respect to your st_intersection, my experience with land cover and
administrative polygons is that the speeds of PostGIS and ArcGIS are
relatively similar (within the same order of magnitude).  EXPLAIN ANALYZE
might help.  Also, if you have polygons that defeat the indexing you could
be in trouble; for example, complex road or water buffers can have huge
bounding boxes...  You might want to try with a small randomly chosen subset
of your data.

With respect to st_difference, my experience tells me that what you want to
do is not so straightforward.  The difference between two polygons is what
you expect; the differences between two sets of polygons is not.  If you
think of a "left side" and "right side" of your join, you are asking for the
difference between each polygon on the left side and ALL of the polygons on
the right.  This is sure to take a long time and generate many many
overlapping polygons in the output dataset.  To get this straight, I suggest
you create a sample data set with two tables each with say three polygons,
run your query, and see what happens.

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

> Hi all – ****
>
> ** **
>
> I have what is likely to sound like the newbie question it is. I am in the
> process of shifting some of our modeling workload from ArcGIS to PostGIS.
> While PostGIS seems much faster for most non-spatial operations, I’m finding
> the exact opposite for spatial operations like “erase,” “intersect,” etc.
> And I’m sure there is some basic thing I just don’t know about how to write
> these scripts to get fast performance.****
>
> ** **
>
> Below are details for two issues I have run into.****
>
> ** **
>
> **1.       **“Intersect”:  In ArcGIS, I used the intersect tool to return
> a polygon file from the intersection of two different polygon files. They
> were large input files – one the size of the state of California and one the
> size of a county in California, both with between 200-300 thousand records.
> In ArcGIS, this took 43 minutes. In PostGIS, I used the script below, and it
> took over 17 hours.****
>
> ** **
>
>                 CREATE TABLE public.fresno_parcels_lt_intersect as ****
>
> SELECT****
>
>                                 ST_Intersection(p.wkb_geometry,
> lt.wkb_geometry) as wkb_geometry,****
>
>                                 id_parcel,****
>
>                                 (st_area(ST_Intersection(p.wkb_geometry,
> lt.wkb_geometry))) * 0.000247105381 as acres_lt_parcel,****
>
>                                 Landtype****
>
> FROM fresno_parcels_unique_id as p, ca_landtypes_010211 as lt****
>
>                 WHERE ST_Intersects(p.wkb_geometry, lt.wkb_geometry);****
>
> ** **
>
> **2.       **“Erase”:  In ArcGIS, I used the erase tool to remove water
> features (polygons) from a county parcel file. Both files were large. The
> water features covered the state of California with 100K records, and the
> parcel file had almost 300K records. In ArcGIS, this took 17 minutes. In
> PostGIS, I had to cancel the run after 16 hours. I used the script below.*
> ***
>
> ** **
>
>                 CREATE TABLE fresno_parcels_minus_ca_water as****
>
> SELECT ST_GeomFromWKB (ST_Difference (wkb_geometry (ca_water_final_082211),
> wkb_geometry (fresno_parcels_clean)))****
>
> FROM ca_water_final_082211, fresno_parcels_clean;****
>
> ** **
>
> I added a spatial index to all of the input files in PostGIS (CREATE INDEX
> ____ ON ____ USING gist (wkb_geometry)). Do any of you all have suggestions
> as to how to make these sorts of operations run more quickly?****
>
> ** **
>
> Thanks so much,****
>
> ** **
>
> ~ 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/20110824/172bdd1d/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/20110824/172bdd1d/attachment.jpg>