<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:"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:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Verdana;
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";}
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;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@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-AU link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='color:#1F497D'>Sorry the third SQL should be “FROM </span>alineunion”, not “FROM alwunionxx”<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 style='margin-bottom:12.0pt'><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#1F497D'>Cheers,<o:p></o:p></span></p><p class=MsoNormal style='margin-bottom:12.0pt'><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'>James Sewell </span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'><br></span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'>Developer </span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'><br></span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'>LISAsoft </span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'><o:p></o:p></span></p><div><div><div><div class=MsoNormal><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'><hr size=2 width=420 style='width:315.0pt' align=left></span></div></div></div></div><p class=MsoNormal><b><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'>Ph: </span></b><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'>+61 3 8680 3250 <b>Fax: </b>+61 3 8680 3299 </span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'><br></span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'>Level 9, 601 Bourke St, Melbourne Vic 3000<o:p></o:p></span></p><div><div><div><div class=MsoNormal><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'><hr size=2 width=420 style='width:315.0pt' align=left></span></div></div></div></div><p class=MsoNormal><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'><br></span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'>LISAsoft is part of the A2end Group of Companies</span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'><br></span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#4F81BD'><a href="http://www.ardec.com.au/"><span style='color:#4F81BD;text-decoration:none'>http://www.ardec.com.au</span></a></span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#4F81BD'><br></span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#4F81BD'><a href="http://www.lisasoft.com/"><span style='color:#4F81BD;text-decoration:none'>http://www.lisasoft.com</span></a></span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#4F81BD'><br></span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#4F81BD'><a href="http://www.terrapages.com/"><span style='color:#4F81BD;text-decoration:none'>http://www.terrapages.com</span></a></span><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 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> geos-devel-bounces@lists.osgeo.org [mailto:geos-devel-bounces@lists.osgeo.org] <b>On Behalf Of </b>James.Sewell@lisasoft.com<br><b>Sent:</b> Tuesday, 1 February 2011 11:11 AM<br><b>To:</b> GEOS Development List<br><b>Subject:</b> [geos-devel] Building polygons from linework to replicate ESRI Union<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Hello All,<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I am encountering problems with my process when I am trying to create drop polygons to linework and rebuild to polygons (as described by Paul Ramsey at <a href="http://s3.opengeo.org/postgis-power.pdf">http://s3.opengeo.org/postgis-power.pdf</a>). I am doing this to replicate the ESRI Union functionality in a timely manner for large datasets. It works for the most part, but the below case if frustrating me.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>The attached zip file contains SQL to insert two polygons to the ageoms table.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>If you run the import SQL then the code below you will see that the bottom geom in the apoly table is joined to the upper geom due to some of the line vanishing (I understand there will be some other small geometries and am fine with this). I am unsure how this is happening from the ST_Polygonize?<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Does anyone have any hints on how I could proceed in this case? If I snap to 10E-6 the problem disappears, but unfortunately 10E-8 is the maximum snap I can use on this data.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>CREATE TABLE aline AS<o:p></o:p></p><p class=MsoNormal style='text-indent:36.0pt'>SELECT a.the_geom AS the_geom FROM <o:p></o:p></p><p class=MsoNormal style='margin-left:36.0pt;text-indent:36.0pt'>(SELECT <o:p></o:p></p><p class=MsoNormal style='margin-left:72.0pt;text-indent:36.0pt'>(ST_Dump<o:p></o:p></p><p class=MsoNormal style='margin-left:72.0pt;text-indent:36.0pt'>(ST_Exteriorring(<o:p></o:p></p><p class=MsoNormal style='margin-left:72.0pt;text-indent:36.0pt'>(ST_Dumprings(<o:p></o:p></p><p class=MsoNormal style='margin-left:72.0pt;text-indent:36.0pt'>(ST_Dump(the_geom)).geom)).geom))).geom <o:p></o:p></p><p class=MsoNormal style='margin-left:36.0pt;text-indent:36.0pt'>AS the_geom FROM ageoms) AS a;<o:p></o:p></p><p class=MsoNormal style='margin-left:36.0pt;text-indent:36.0pt'><o:p> </o:p></p><p class=MsoNormal>CREATE TABLE alineunion AS<o:p></o:p></p><p class=MsoNormal style='text-indent:36.0pt'>SELECT st_union(st_collect(the_geom), ST_SetSRID('LINESTRING EMPTY', 4283)) AS the_geom FROM aline;<o:p></o:p></p><p class=MsoNormal style='text-indent:36.0pt'><o:p> </o:p></p><p class=MsoNormal style='text-indent:36.0pt'><o:p> </o:p></p><p class=MsoNormal>CREATE TABLE apoly AS<o:p></o:p></p><p class=MsoNormal style='text-indent:36.0pt'>SELECT a.the_geom AS the_geom FROM <o:p></o:p></p><p class=MsoNormal style='margin-left:36.0pt;text-indent:36.0pt'>(SELECT (ST_Dump(ST_polygonize(the_geom))).geom AS the_geom FROM alwunionxx) AS a;<o:p></o:p></p><p class=MsoNormal style='text-indent:36.0pt'><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='text-indent:36.0pt'><o:p> </o:p></p><p class=MsoNormal style='margin-bottom:12.0pt'><span style='font-size:9.0pt;font-family:"Verdana","sans-serif"'>Cheers,<o:p></o:p></span></p><p class=MsoNormal style='margin-bottom:12.0pt'><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'>James Sewell <br>Developer <br>LISAsoft <o:p></o:p></span></p><div><div><div><div><div class=MsoNormal><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'><hr size=2 width=420 style='width:315.0pt' align=left></span></div></div></div></div></div><p class=MsoNormal><b><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'>Ph: </span></b><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'>+61 3 8680 3250 <b>Fax: </b>+61 3 8680 3299 <br>Level 9, 601 Bourke St, Melbourne Vic 3000<o:p></o:p></span></p><div><div><div><div><div class=MsoNormal><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'><hr size=2 width=420 style='width:315.0pt' align=left></span></div></div></div></div></div><p class=MsoNormal><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#999999'><br>LISAsoft is part of the A2end Group of Companies<br></span><span style='font-size:9.0pt;font-family:"Verdana","sans-serif";color:#4F81BD'><a href="http://www.ardec.com.au/"><span style='color:#4F81BD;text-decoration:none'>http://www.ardec.com.au</span></a><br><a href="http://www.lisasoft.com/"><span style='color:#4F81BD;text-decoration:none'>http://www.lisasoft.com</span></a><br><a href="http://www.terrapages.com/"><span style='color:#4F81BD;text-decoration:none'>http://www.terrapages.com</span></a></span><o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p></div></body></html>