<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:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
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.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";
mso-fareast-language:EN-US;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
.MsoChpDefault
{mso-style-type:export-only;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
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-NZ" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal">Hi PostGIS Devs,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I have a daily process that I would like to optimise. The process includes a spatial intersects join between a small administration boundaries table for New Zealand that contains large geometries (100,000+ points per polygon) and a large
dataset with small line strings.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">The first iteration of SQL I developed for the process I used standard join (WHERE ST_Interesects(admin.geom, other.geom)) and it took 4 hours to complete. No spatial index was used. Not acceptable.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Based on a recommendation from the PostGIS manual (<a href="http://postgis.net/docs/manual-1.5/ch06.html#id368055">http://postgis.net/docs/manual-1.5/ch06.html#id368055</a>) I then created a bounding box column for each of the admin boundaries,
built an index for this column and managed to reduce the processing time to 40mins. Spatial index was used. This was still too slow.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I then found an article here <span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:black;mso-fareast-language:EN-NZ">
<a href="http://www.kindle-maps.com/blog/splitting-a-large-polygon-into-a-grid.html">http://www.kindle-maps.com/blog/splitting-a-large-polygon-into-a-grid.html</a></span><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";mso-fareast-language:EN-NZ">
and <span style="color:black">split the large admin </span></span>boundaries<span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:black;mso-fareast-language:EN-NZ"> into grid cells (5kmx5km) then created a spatial join query using the new gridded
boundaries. This resulted in a query time 20secs. Much better! One of the side effects was the gridding process took about 10mins to generate. However the admin boundaries only change once a year, so it’s not a large problem.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:black;mso-fareast-language:EN-NZ"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:black;mso-fareast-language:EN-NZ">On reflection this gridding processing seems like a replication of a core database feature – indexing. Would it not be best to implement
a new index method to do this type of spatial grid indexing? This would allow for simplified creation, maintenance, size and access(?). It would also provide a great core feature for PostGIS. If
</span>PostGIS Devs think this is a good idea I have funding to make this happen.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Cheers,<o:p></o:p></p>
<p class="MsoNormal">Jeremy<o:p></o:p></p>
</div>
<br>
<hr>
<font face="Verdana" color="Black" size="2">This message contains information, which may be in confidence and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If
you have received this message in error, please notify us immediately (Phone 0800 665 463 or info@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from
LINZ. Thank You.<br>
</font>
</body>
</html>