<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
For your query #1, it looks like you are computing the
ST_Intersection twice. Does Postgres optimize this away? IF not,
you might want to use a subquery to avoid this expensive second
computation.<br>
<br>
I also agree with Chris, that query #2 is probably not doing what
you want it to. What you need to do is for each parcel, subtract
the union of the water features covered by it. This is still likely
to be slow, however.<br>
<br>
A general comment is that ArcGIS is using a very different approach
to compute erase and intersect (aka overlay). It evaluates the
entire set of geometries together, rather than piece-wise like the
SQL query is doing. This generally results in much better
performance for large datasets, since there is less I/O and more
efficient algorithms available. <br>
<br>
By it's nature, using SQL for spatial computation is most efficient
for operations which can be carried out in a feature-wise manner.
Unfortunately, overlay does not fall into this category (since there
is a large amount of interaction between features.<br>
<br>
Implementing a more efficient overlay algorithm in PostGIS is a nice
challenge for the future...<br>
<br>
On 8/24/2011 8:18 PM, Sheara Cohen wrote:
<blockquote
cite="mid:0F1E926DC29BA24D93D12E01314D6FC5014550E9@GOTHAM.calthorpe.local"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=ISO-8859-1">
<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]-->
<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>
</div>
</blockquote>
</body>
</html>