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

[Sheara Cohen]

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 <mailto:sheara at calthorpe.com>  | www.calthorpe.com

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110824/f4bde719/attachment.html>