<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Stephen, <br>
<br>
Do you have a final timing from your ST_MakeValid test? The numbers
you show indicate about a 2.5:1 decrease in performance. This could
easily be explained by an extra overlay or validity test or two. <br>
<br>
This isn't to say that there wouldn't be a possibility to improve
the performance of the ST_MakeValid code, but (speaking as someone
who has spent way too much time tweaking spatial algorithms for
performance) this may or may not be an easy/obvious fix.<br>
<br>
Also, it would be helpful to know if this is a systematic difference
in performance, or whether there are big differences for specific
geometry cases. This unfortunately will take a bit of effort to
isolate - you'd probably have to run many tests on single geometries
to capture the performance difference.<br>
<br>
And sad to say, even if there is an obvious large difference only in
specific cases, this doesn't necessarily mean that it's easy to
improve the performance in just those cases. One of the annoying
things about spatial algorithms is that it's often very difficult to
detect particular simple cases which can be computed quickly - at
least, not any faster than the full, slower algorithm would run
anyway!<br>
<br>
On 4/16/2012 9:33 AM, Stephen V. Mather wrote:
<blockquote cite="mid:000601cd1bee$9f99de00$decd9a00$@com"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=ISO-8859-1">
<meta name="Generator" content="Microsoft Word 12 (filtered
medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 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;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="2050" />
</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="Section1">
<p class="MsoNormal">Hi All,<o:p></o:p></p>
<p class="MsoNormal"> Has
anyone done any performance tests on ST_MakeValid? I had been
using Tim
Sutton’s cleanGeometry function from circa 2010 (<a
moz-do-not-send="true"
href="http://linfiniti.com/2010/07/cleaning-geometries-inside-postgis/">http://linfiniti.com/2010/07/cleaning-geometries-inside-postgis/</a>)
to clean up geometries. Works most of the time—every now and
then I
throw it something too big and harry and it runs for weeks and
weeks and never (AFAI
have the patience for) completes. I’ve been looking forward
to
ST_MakeValid.<o:p></o:p></p>
<p class="MsoNormal"> Today,
a new Postgis 2.0 instance in hand, I was playing with a
dataset with 9106
invalid geometries—all self intersecting geometries from
raster-->vector conversion in an un-named proprietary
package. On a
whim, I decided to compare the speed of ST_MakeValid to Tim’s
code, so
first I needed the correct functions:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b><span style="font-family:"Courier
New"">psql -U postgres
-d test -f "C:\Program
Files\PostgreSQL\9.1\share\contrib\postgis-2.0\legacy.sql"<o:p></o:p></span></b></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Then a quick run through cleanGeometry:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b><span style="font-family:"Courier
New"">CREATE TABLE
tpi_clean AS<o:p></o:p></span></b></p>
<p class="MsoNormal"><b><span style="font-family:"Courier
New""> SELECT
gid, id, gridcode, "class name", cleanGeometry(geom)<o:p></o:p></span></b></p>
<p class="MsoNormal"><b><span style="font-family:"Courier
New""> FROM
tpi;<o:p></o:p></span></b></p>
<p class="MsoNormal"><span style="font-family:"Courier
New""><o:p> </o:p></span></p>
<p class="MsoNormal">And I get back:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b><span style="font-family:"Courier
New"">Query returned
successfully: 1004952 rows affected, 955444 ms execution
time.<o:p></o:p></span></b></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">If I run as ST_MakeValid,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b><span style="font-family:"Courier
New"">CREATE TABLE
tpi_valid AS<o:p></o:p></span></b></p>
<p class="MsoNormal"><b><span style="font-family:"Courier
New""> SELECT
gid, id, gridcode, "class name", ST_MakeValid(geom)<o:p></o:p></span></b></p>
<p class="MsoNormal"><b><span style="font-family:"Courier
New""> FROM
tpi;<o:p></o:p></span></b></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">At 2300000 milliseconds, it is still
running… .<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">This is PostgreSQL 9.1, PostGIS 2.0
USE_GEOS=1 USE_PROJ=1
USE_STATS=1, on Windows XP 32-bit. I have not yet tested on
my Ubuntu box…
.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks,<br>
Best,<o:p></o:p></p>
<p class="MsoNormal">Steve<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600"
o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f"
stroked="f">
<v:stroke joinstyle="miter" />
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0" />
<v:f eqn="sum @0 1 0" />
<v:f eqn="sum 0 0 @1" />
<v:f eqn="prod @2 1 2" />
<v:f eqn="prod @3 21600 pixelWidth" />
<v:f eqn="prod @3 21600 pixelHeight" />
<v:f eqn="sum @0 0 1" />
<v:f eqn="prod @6 1 2" />
<v:f eqn="prod @7 21600 pixelWidth" />
<v:f eqn="sum @8 21600 0" />
<v:f eqn="prod @7 21600 pixelHeight" />
<v:f eqn="sum @10 21600 0" />
</v:formulas>
<v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect" />
<o:lock v:ext="edit" aspectratio="t" />
</v:shapetype><v:shape id="cmp-ms-90x122.png" o:spid="_x0000_s1026" type="#_x0000_t75"
alt="http://www.clemetparks.com/images/esig/cmp-ms-90x122.png" style='position:absolute;
margin-left:1.5pt;margin-top:0;width:67.5pt;height:91.5pt;z-index:1;
visibility:visible;mso-wrap-style:square;mso-wrap-distance-left:9pt;
mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;
mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;
mso-position-horizontal-relative:text;mso-position-vertical:absolute;
mso-position-vertical-relative:text'>
<v:imagedata src="mailbox:///C:/Documents%20and%20Settings/Owner/Application%20Data/Thunderbird/Profiles/3ljwk8nh.default/Mail/Local%20Folders/Lists.sbd/PostGIS?number=38004066&header=quotebody&part=1.1.2&filename=image001.png" o:href="http://www.clemetparks.com/images/esig/cmp-ms-90x122.png" />
<w:wrap type="square"/>
</v:shape><![endif]--><!--[if !vml]--><img
src="cid:part2.08040804.09060103@telus.net"
alt="http://www.clemetparks.com/images/esig/cmp-ms-90x122.png"
v:shapes="cmp-ms-90x122.png" align="left" height="122"
hspace="12" width="90"><!--[endif]--><span
style="font-size:14.0pt;font-family:"Arial","sans-serif";color:#006C56">Stephen
Mather<br>
</span><span
style="font-family:"Arial","sans-serif";color:#006C56">Geographic
Information
Systems (GIS) Manager<br>
</span><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:#006C56">(216)
635-3243<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:#006C56"><a class="moz-txt-link-abbreviated" href="mailto:svm@clevelandmetroparks.com">svm@clevelandmetroparks.com</a><br>
</span><a moz-do-not-send="true"
href="http://www.clemetparks.com/"><span
style="font-size:9.0pt;
font-family:"Arial","sans-serif"">clevelandmetroparks.com</span></a><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<p class="" avgcert""="" color="#000000" align="left">No virus
found in this message.<br>
Checked by AVG - <a moz-do-not-send="true"
href="http://www.avg.com">www.avg.com</a><br>
Version: 2012.0.1913 / Virus Database: 2411/4940 - Release Date:
04/16/12</p>
</blockquote>
</body>
</html>