<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:x="urn:schemas-microsoft-com:office:excel" 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=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Yu Mincho";
panose-1:2 2 4 0 0 0 0 0 0 0;}
@font-face
{font-family:"\@Yu Mincho";}
/* 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.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
font-weight:normal;
font-style:normal;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></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"><span style="font-size:12.0pt">In our use of PostGIS on nationwide datasets, we too struggled with similar performance issues. In the end, we largely fell back upon the tricks mentioned by Paul (along with some pre-processing using the
ArcGIS Dice tool to limit the number of vertices for any polygon).<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt">Perhaps there's a really obvious explanation, but is there a reason that functions like ST_Intersection are not optimized on the backend to, e.g., test the relation and return geometry A without further processing
when geometry A falls entirely within geometry B? It seems like it would be pretty easy and would tend to speed up most real-world queries.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt"><o:p> </o:p></span></p>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;color:black">Jonathan McCormack<o:p></o:p></span></p>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;color:black">Attorney Advisor & Information Systems Specialist<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span style="font-size:10.0pt;color:black">Auctions and Spectrum Access Division, WTB<br>
Federal Communications Commission</span><span style="font-size:12.0pt"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt"><o:p> </o:p></span></p>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:12.0pt;color:black">From: </span></b><span style="font-size:12.0pt;color:black">postgis-users <postgis-users-bounces@lists.osgeo.org> on behalf of Nicolas Ribot <nicolas.ribot@gmail.com><br>
<b>Reply-To: </b>PostGIS Users Discussion <postgis-users@lists.osgeo.org><br>
<b>Date: </b>Monday, December 3, 2018 at 2:51 PM<br>
<b>To: </b>PostGIS Users Discussion <postgis-users@lists.osgeo.org><br>
<b>Subject: </b>Re: [postgis-users] Improvement suggestion<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">Hi,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Did you cut your big polygons with st_subdivide before intersecting them ?<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">It usually speeds up queries by several orders of magnitude.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Nicolas<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On Mon, 3 Dec 2018 at 18:41, Paul van der Linden <<a href="mailto:paul.doskabouter@gmail.com">paul.doskabouter@gmail.com</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in">
<div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt">I indeed know that trick to eliminate an st_intersection, but that comes at the cost of a st_within.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt">In the test-case I'm examing now, the st_intersects and st_within both take about 40 seconds and the st_intersection takes about a minute.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt">So to eliminate one of the 40 seconds (and in this case even the intersection because st_within is true), a function returning the relation could speed up things<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal">_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.osgeo.org_mailman_listinfo_postgis-2Dusers&d=DwMFaQ&c=y0h0omCe0jAUGr4gAQ02Fw&r=ttSzwKHPIaN3Ue2Op6mH8FkPyFpiuuXAybXGNkPA8Zw&m=1FyqHI-zvQdeWurRnP6ebNq1GOYr3W03RbRg9a1tAxc&s=5Odg0h7eiL4iybklq5xzLe801SZYbxMgl62YD8Ph0_8&e=" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p>
</blockquote>
</div>
</div>
</body>
</html>