<html 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=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;}
/* 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:#954F72;
text-decoration:underline;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style></head><body lang=EN-ZA link=blue vlink="#954F72"><div class=WordSection1><p class=MsoNormal>Hi,</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Thank you so much for the help. </p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I have tried to view and clean the data in QGIS but the file size is too large, the file size is 25GB which makes it difficult to view. </p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Currently, I am only looking for a tool to only identify all the errors, once I have identified the errors, then my next step will be to fix them. </p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Kind regards, </p><p class=MsoNormal>Cameron Green<br>President - CGIS YouthMappers<br>BSc(Hons) Geoinformatics, University of Pretoria<br>+2783 301 3201<o:p></o:p></p><p class=MsoNormal>+2712 420 2016<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div style='mso-element:para-border-div;border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal style='border:none;padding:0cm'><b>From: </b><a href="mailto:nicolas.ribot@gmail.com">Nicolas Ribot</a><br><b>Sent: </b>Wednesday, 04 July 2018 2:56 PM<br><b>To: </b><a href="mailto:postgis-users@lists.osgeo.org">PostGIS Users Discussion</a><br><b>Subject: </b>Re: [postgis-users] PostGIS Topology Error</p></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>Hi,</p><div><p class=MsoNormal>Indeed, Postgis Topology works well to build topo from clean geometries, but can fail if input geometries have topological errors.</p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>It could be tricky to clean data with just postGIS and sql queries.</p></div><div><p class=MsoNormal>Topological GIS, like GRASS, can load PostGIS data and clean them at a given precision</p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>HTH</p></div><div><p class=MsoNormal>Nicolas</p></div></div><div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>On 4 July 2018 at 14:01, Cameron Green <<a href="mailto:u15055452@tuks.co.za" target="_blank">u15055452@tuks.co.za</a>> wrote:</p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'>Good day,</p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'> </p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>My name is Cameron Green and I am an honours (4<sup>th</sup> year) student at the University of Pretoria in South Africa. I am currently working on a project that requires me to assess the quality of data that was downloaded from OpenStreetMap (OSM), specifically, various topological errors. I have decided to use PostGIS as it does error checks and tells me what the error is and where (or at least that is how I understand it). </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>Firstly, here is my installation information (Ubuntu 16.04) with PostgreSQL 10:</span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>POSTGIS="2.4.4 r16526" PGSQL="100" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.2" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" TOPOLOGY RASTER</span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>Using the textbook, Mastering PostGIS chapter 8, where it deals with PostGIS Topology. Below is the SQL commands I used. When I used SRID as 4026 and 0,00028 as the snap threshold, the commands execute without any issues. However, when I change the SRID to 2049 and decrease the snap tolerance in the topology.toTopoGeom, I get the following error: </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='font-size:9.5pt;font-family:"Arial",sans-serif;color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='font-family:"Times New Roman",serif;color:#333333;background:white'>ERROR: SQL/MM Spatial exception - geometry crosses edge 4469 CONTEXT: PL/pgSQL function totopogeom(geometry,topogeometry,double precision) line 114 at FOR over SELECT rows PL/pgSQL function totopogeom(geometry,character varying,integer,double precision) line 89 at assignment SQL state: XX000</span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='font-size:9.5pt;font-family:"Arial",sans-serif;color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>The line and type of error changes as I increase the snap tolerance to any value between 5m and 100m.</span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>From my understanding, once the topology is created and the geometries are loaded into the topology, I should be able to run the topology.ValidateTopology function to get a list of errors. Is this correct? </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>At the moment it seems like the topology errors is what preventing me from creating the topology. </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>If anyone could please help me or direct me to where I have made an error in my SQL commands or there are some settings I must change. Any help regarding this would be greatly appreciated. I have added the commands at the end of this email. </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'>Kind regards,</p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'>Cameron Green<br>President - CGIS YouthMappers<br>BSc(Hons) Geoinformatics, University of Pretoria<br>+2783 301 3201</p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'>+2712 420 2016</p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'> </p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'> </p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'> </p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>SQL Commands: </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>topology.CreateTopology ('my_topology', 2049, 5, false) </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='font-size:9.5pt;font-family:"Arial",sans-serif;color:#222222'>// Checked the validity of the input geometries </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>select ST_IsValidReason(way) </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>from osm_lines</span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>where ST_IsValid(way) = false</span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='font-size:9.5pt;font-family:"Arial",sans-serif;color:#222222'>// Create the TopoGeometry column </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>select topology.AddTopoGeometryColumn('my_topology', 'public', osm_lines, 'topogeom', 'Linestring')</span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='font-size:9.5pt;font-family:"Arial",sans-serif;color:#222222'>// Populate the Topogeometry column from an existing geometry. </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='font-size:9.5pt;font-family:"Arial",sans-serif;color:#222222'>// This is where my problem starts</span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>update osm_lines SET topogeom = topology.toTopoGeom(way,'my_topology',1,5) </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>select topology.TopologySummary('my_topology')</span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'> </span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'><span style='color:#222222'>select * from topology.ValidateTopology('my_topology')</span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:4.8pt'> </p></div></div><p class=MsoNormal style='margin-left:4.8pt'><br><span style='font-size:12.0pt;font-family:"Arial",sans-serif'>This message and attachments are subject to a disclaimer. Please refer to </span><span style='font-size:12.0pt'><a href="http://www.it.up.ac.za/documentation/governance/disclaimer/" target="_blank"><span style='font-family:"Arial",sans-serif'>http://www.it.up.ac.za/documentation/governance/disclaimer/</span></a></span><span style='font-size:12.0pt;font-family:"Arial",sans-serif'> for full details.</span><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></p></blockquote></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p></div></body></html>
<br>
<font face="Arial" size="3">This message and attachments are subject to a disclaimer. Please refer to
</font><font size="3"><a href="http://www.it.up.ac.za/documentation/governance/disclaimer/" target="_blank"><font face="Arial">http://www.it.up.ac.za/<WBR>documentation/governance/<WBR>disclaimer/</font></a></font><font face="Arial" size="3"> for full details.</font>