<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 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;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 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";
color:black;}
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;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New","serif";
color:black;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;
color:black;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@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]-->
</head>
<body bgcolor=white lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='color:#1F497D'>:) It’s never so
easy, is it.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>I had reason to kill the ST_MakeValid
at about 10:1, so I have no idea what the completion time is except > 10,000
seconds. I’m rerunning now.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>I can play with finding a subset
of the data that replicates the behavior.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Best,<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Steve<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<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_s1027" 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:2;
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="cid:image001.png@01CD1C85.276DFC80" o:href="http://www.clemetparks.com/images/esig/cmp-ms-90x122.png" />
<w:wrap type="square"/>
</v:shape><![endif]--><![if !vml]><img width=90 height=122
src="cid:image001.png@01CD1C85.276DFC80" align=left hspace=12
alt="http://www.clemetparks.com/images/esig/cmp-ms-90x122.png" v:shapes="cmp-ms-90x122.png"><![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'>svm@clevelandmetroparks.com<br>
</span><span style='color:#1F497D'><a href="http://www.clemetparks.com/"><span
style='font-size:9.0pt;font-family:"Arial","sans-serif";color:blue'>clevelandmetroparks.com</span></a><o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
</div>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif";
color:windowtext'>From:</span></b><span style='font-size:10.0pt;font-family:
"Tahoma","sans-serif";color:windowtext'>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Martin
Davis<br>
<b>Sent:</b> Tuesday, April 17, 2012 12:11 AM<br>
<b>To:</b> postgis-users@postgis.refractions.net<br>
<b>Subject:</b> Re: [postgis-users] ST_MakeValid<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>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: <o:p></o:p></p>
<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
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","serif"'>psql -U
postgres -d test -f "C:\Program
Files\PostgreSQL\9.1\share\contrib\postgis-2.0\legacy.sql"</span></b><o:p></o:p></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","serif"'>CREATE
TABLE tpi_clean AS</span></b><o:p></o:p></p>
<p class=MsoNormal><b><span style='font-family:"Courier New","serif"'>
SELECT gid, id, gridcode, "class name", cleanGeometry(geom)</span></b><o:p></o:p></p>
<p class=MsoNormal><b><span style='font-family:"Courier New","serif"'>
FROM tpi;</span></b><o:p></o:p></p>
<p class=MsoNormal><span style='font-family:"Courier New","serif"'> </span><o:p></o:p></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","serif"'>Query
returned successfully: 1004952 rows affected, 955444 ms execution time.</span></b><o:p></o:p></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","serif"'>CREATE
TABLE tpi_valid AS</span></b><o:p></o:p></p>
<p class=MsoNormal><b><span style='font-family:"Courier New","serif"'>
SELECT gid, id, gridcode, "class name", ST_MakeValid(geom)</span></b><o:p></o:p></p>
<p class=MsoNormal><b><span style='font-family:"Courier New","serif"'>
FROM tpi;</span></b><o:p></o:p></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:shape id="_x0000_s1026" type="#_x0000_t75"
alt="http://www.clemetparks.com/images/esig/cmp-ms-90x122.png" style='position:absolute;
margin-left:0;margin-top:0;width:67.5pt;height:91.5pt;z-index:1;
mso-position-horizontal:left;mso-position-vertical-relative:line'
o:allowoverlap="f">
<v:imagedata src="cid:image001.png@01CD1C85.276DFC80" o:title="part2" />
<w:wrap type="square"/>
</v:shape><![endif]--><![if !vml]><img width=90 height=122
src="cid:image001.png@01CD1C85.276DFC80" align=left hspace=12
alt="http://www.clemetparks.com/images/esig/cmp-ms-90x122.png" v:shapes="_x0000_s1026"><![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</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:9.0pt;font-family:"Arial","sans-serif";
color:#006C56'><a href="mailto:svm@clevelandmetroparks.com">svm@clevelandmetroparks.com</a><br>
</span><a 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>
<p class=MsoNormal><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'><br>
<br>
<br>
<o:p></o:p></span></p>
<pre>_______________________________________________<o:p></o:p></pre><pre>postgis-users mailing list<o:p></o:p></pre><pre><a
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><o:p></o:p></pre><pre><a
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><o:p></o:p></pre>
<p class=MsoNormal><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'><br>
<br>
<br>
<o:p></o:p></span></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span
style='font-size:12.0pt;font-family:"Times New Roman","serif"'>No virus found
in this message.<br>
Checked by AVG - <a href="http://www.avg.com">www.avg.com</a><br>
Version: 2012.0.1913 / Virus Database: 2411/4940 - Release Date: 04/16/12<o:p></o:p></span></p>
</div>
</body>
</html>