<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Century Gothic";
panose-1:2 11 5 2 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:797190362;
mso-list-type:hybrid;
mso-list-template-ids:1083196332 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1
{mso-list-id:1093747831;
mso-list-type:hybrid;
mso-list-template-ids:-1976113766 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l1:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:.25in;
text-indent:-.25in;}
@list l1:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:.75in;
text-indent:-.25in;}
@list l1:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
margin-left:1.25in;
text-indent:-9.0pt;}
@list l1:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:1.75in;
text-indent:-.25in;}
@list l1:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:2.25in;
text-indent:-.25in;}
@list l1:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
margin-left:2.75in;
text-indent:-9.0pt;}
@list l1:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:3.25in;
text-indent:-.25in;}
@list l1:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:3.75in;
text-indent:-.25in;}
@list l1:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
margin-left:4.25in;
text-indent:-9.0pt;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal>Hi all – <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></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.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Below are details for two issues I have run into.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoListParagraph style='margin-left:.25in;text-indent:-.25in;mso-list:l1 level1 lfo2'><![if !supportLists]><span style='mso-list:Ignore'>1.<span style='font:7.0pt "Times New Roman"'> </span></span><![endif]>“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.<o:p></o:p></p><p class=MsoListParagraph style='margin-left:.25in'><o:p> </o:p></p><p class=MsoNormal> CREATE TABLE public.fresno_parcels_lt_intersect as <o:p></o:p></p><p class=MsoNormal style='text-indent:.5in'>SELECT<o:p></o:p></p><p class=MsoNormal> ST_Intersection(p.wkb_geometry, lt.wkb_geometry) as wkb_geometry,<o:p></o:p></p><p class=MsoNormal> id_parcel,<o:p></o:p></p><p class=MsoNormal> (st_area(ST_Intersection(p.wkb_geometry, lt.wkb_geometry))) * 0.000247105381 as acres_lt_parcel,<o:p></o:p></p><p class=MsoNormal> Landtype<o:p></o:p></p><p class=MsoNormal style='text-indent:.5in'>FROM fresno_parcels_unique_id as p, ca_landtypes_010211 as lt<o:p></o:p></p><p class=MsoNormal> WHERE ST_Intersects(p.wkb_geometry, lt.wkb_geometry);<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoListParagraph style='margin-left:.25in;text-indent:-.25in;mso-list:l1 level1 lfo2'><![if !supportLists]><span style='mso-list:Ignore'>2.<span style='font:7.0pt "Times New Roman"'> </span></span><![endif]>“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.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> CREATE TABLE fresno_parcels_minus_ca_water as<o:p></o:p></p><p class=MsoNormal style='text-indent:.5in'>SELECT ST_GeomFromWKB (ST_Difference (wkb_geometry (ca_water_final_082211), wkb_geometry (fresno_parcels_clean)))<o:p></o:p></p><p class=MsoNormal style='text-indent:.5in'>FROM ca_water_final_082211, fresno_parcels_clean;<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></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?<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Thanks so much,<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>~ Sheara<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Century Gothic","sans-serif";color:gray;letter-spacing:1.0pt'>Sheara Cohen</span></b><span style='font-size:9.0pt;font-family:"Century Gothic","sans-serif"'><br></span><span style='font-size:7.5pt;font-family:"Century Gothic","sans-serif";font-variant:small-caps;color:gray;letter-spacing:1.4pt'>Planner</span><span style='letter-spacing:1.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span style='font-size:10.0pt;font-family:"Century Gothic","sans-serif";color:#E9935F'>C A L T H O R P E</span></b><b><span style='font-size:10.0pt;font-family:"Century Gothic","sans-serif";color:gray'> A S S O C I A T E S</span></b><span style='font-size:10.0pt;font-family:"Century Gothic","sans-serif";color:gray'><br></span><span style='font-size:7.5pt;font-family:"Century Gothic","sans-serif";color:gray'>2095 ROSE STREET, SUITE 201, BERKELEY, CALIFORNIA, 94709 USA</span><span style='font-size:8.0pt;font-family:"Century Gothic","sans-serif"'><br></span><span style='font-size:7.5pt;font-family:"Century Gothic","sans-serif";color:gray'>510 809-1165 (direct) | 510-548-6800 x35 (main) | 510 548-6848 (fax)<br><a href="mailto:sheara@calthorpe.com"><span style='color:blue'>sheara@calthorpe.com</span></a> | <a href="www.calthorpe.com"><span style='color:blue'>www.calthorpe.com</span></a><o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p></div></body></html>