Sheara, a couple of comments.<br><br>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.<br>
<br>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.<br>
<br><div class="gmail_quote">2011/8/24 Sheara Cohen <span dir="ltr"><<a href="mailto:Sheara@calthorpe.com">Sheara@calthorpe.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div link="blue" vlink="purple" lang="EN-US"><div><p class="MsoNormal">Hi all – <u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">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.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">Below are details for two issues I have run into.<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p style="margin-left:.25in"><u></u><span>1.<span style="font:7.0pt "Times New Roman""> </span></span><u></u>“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.<u></u><u></u></p>
<p style="margin-left:.25in"><u></u> <u></u></p><p class="MsoNormal"> CREATE TABLE public.fresno_parcels_lt_intersect as <u></u><u></u></p><p class="MsoNormal" style="text-indent:.5in">SELECT<u></u><u></u></p>
<p class="MsoNormal"> ST_Intersection(p.wkb_geometry, lt.wkb_geometry) as wkb_geometry,<u></u><u></u></p><p class="MsoNormal"> id_parcel,<u></u><u></u></p><p class="MsoNormal">
(st_area(ST_Intersection(p.wkb_geometry, lt.wkb_geometry))) * 0.000247105381 as acres_lt_parcel,<u></u><u></u></p><p class="MsoNormal"> Landtype<u></u><u></u></p>
<p class="MsoNormal" style="text-indent:.5in">FROM fresno_parcels_unique_id as p, ca_landtypes_010211 as lt<u></u><u></u></p><p class="MsoNormal"> WHERE ST_Intersects(p.wkb_geometry, lt.wkb_geometry);<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p><p style="margin-left:.25in"><u></u><span>2.<span style="font:7.0pt "Times New Roman""> </span></span><u></u>“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.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"> CREATE TABLE fresno_parcels_minus_ca_water as<u></u><u></u></p><p class="MsoNormal" style="text-indent:.5in">SELECT ST_GeomFromWKB (ST_Difference (wkb_geometry (ca_water_final_082211), wkb_geometry (fresno_parcels_clean)))<u></u><u></u></p>
<p class="MsoNormal" style="text-indent:.5in">FROM ca_water_final_082211, fresno_parcels_clean;<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">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?<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">Thanks so much,<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">~ Sheara<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;color:gray;letter-spacing:1.0pt">Sheara Cohen</span></b><span style="font-size:9.0pt"><br></span><span style="font-size:7.5pt;font-variant:small-caps;color:gray;letter-spacing:1.4pt">Planner</span><span style="letter-spacing:1.0pt"><u></u><u></u></span></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;color:#E9935F">C A L T H O R P E</span></b><b><span style="font-size:10.0pt;color:gray"> A S S O C I A T E S</span></b><span style="font-size:10.0pt;color:gray"><br></span><span style="font-size:7.5pt;color:gray">2095 ROSE STREET, SUITE 201, BERKELEY, CALIFORNIA, 94709 USA</span><span style="font-size:8.0pt"><br>
</span><span style="font-size:7.5pt;color:gray"><a href="tel:510%20809-1165" value="+15108091165" target="_blank">510 809-1165</a> (direct) | <a href="tel:510-548-6800%20x35" value="+15105486800" target="_blank">510-548-6800 x35</a> (main) | <a href="tel:510%20548-6848" value="+15105486848" target="_blank">510 548-6848</a> (fax)<br>
<a href="mailto:sheara@calthorpe.com" target="_blank"><span style="color:blue">sheara@calthorpe.com</span></a> | <a href="http://www.calthorpe.com" target="_blank"><span style="color:blue">www.calthorpe.com</span></a><u></u><u></u></span></p>
<p class="MsoNormal"><u></u> <u></u></p></div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br><br clear="all"><br>-- <br><font face="SANS" size="3"><span style="border-collapse:collapse;color:rgb(32, 32, 32);font-family:'Droid Sans', arial, sans-serif;font-size:13px"><font style="font-family:arial,helvetica,sans-serif" face="SANS" size="3">Chris Hermansen</font><br style="font-family:arial,helvetica,sans-serif">
<i style="font-family:arial,helvetica,sans-serif"><font size="2">Vice President</font></i><br style="font-family:arial,helvetica,sans-serif"><img style="font-family:arial,helvetica,sans-serif" src="cid:part1.07020105.06030603@tecogroup.ca" align="bottom" border="0"><br style="font-family:arial,helvetica,sans-serif">
<font style="font-family:arial,helvetica,sans-serif" face="SANS" size="2">TECO Natural Resource Group Limited</font><br style="font-family:arial,helvetica,sans-serif"><font style="font-family:arial,helvetica,sans-serif" face="SANS" size="2">301 · 958 West 8th Avenue</font><br style="font-family:arial,helvetica,sans-serif">
<font style="font-family:arial,helvetica,sans-serif" face="SANS" size="2">Vancouver BC CANADA · V5Z 1E5</font><br style="font-family:arial,helvetica,sans-serif"><font face="SANS" size="2"><span style="font-family:arial,helvetica,sans-serif">Tel +1.604.714.2878 · Cel +1.778.840.46</span>25</font></span></font><br>