<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)">
<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: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 WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
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-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal">All –<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I am running the following PostGIS configuration on PostgreSQL 9.2<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">"POSTGIS="2.0.6 r12554" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6" LIBJSON="UNKNOWN" TOPOLOGY RASTER"<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I have successfully validated topology for a single feature class of 1.2 million road features with hard-coded names and have tried to generalize that into a function with parameters that will let me check for dangling edges in any linear
feature class. Here is a listing of the function. I highlighted where I think the error is occurring in red below.<o:p></o:p></p>
<p class="MsoNormal">----------------------------------------------------------------------------------------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoNormal">CREATE OR REPLACE FUNCTION hc_check_gaps_in_linear_topology(IN tablename text, IN cleantopo text)<o:p></o:p></p>
<p class="MsoNormal"> RETURNS TABLE(objectid integer, f_code character varying, topo_shape topogeometry) AS<o:p></o:p></p>
<p class="MsoNormal">$BODY$<o:p></o:p></p>
<p class="MsoNormal">declare<o:p></o:p></p>
<p class="MsoNormal"> updatedtablename text;<o:p></o:p></p>
<p class="MsoNormal">DECLARE<o:p></o:p></p>
<p class="MsoNormal"> r record;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">BEGIN<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"> -- SELECT sde_set_current_version(10.2.1);<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> updatedtablename = 'updated' || tablename;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> RAISE NOTICE 'The updated table is %', updatedtablename;<o:p></o:p></p>
<p class="MsoNormal"> raise NOTICE 'The input table name is %', tablename;<o:p></o:p></p>
<p class="MsoNormal"> raise NOTICE 'Cleantopo is %', cleantopo;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> --CREATE TABLE updatedtablename AS SELECT objectid, f_code, shape FROM tablename;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> --execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, f_code, shape FROM ' || tablename;<o:p></o:p></p>
<p class="MsoNormal"> -- RETURN QUERY per 9.2 Section 39.6.1 Returning from a function. See also the link to PostGISBlockFunction under pgTopology folder.<o:p></o:p></p>
<p class="MsoNormal"> --return QUERY <o:p></o:p></p>
<p class="MsoNormal"> execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, f_code, shape FROM ' || tablename;<o:p></o:p></p>
<p class="MsoNormal"> --return QUERY <o:p></o:p></p>
<p class="MsoNormal"> --execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, f_code, shape FROM ' || tablename;<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"> -- Drop the existing topology<o:p></o:p></p>
<p class="MsoNormal"> EXECUTE 'SELECT topology.DropTopology('''|| cleantopo ||''');';<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> -- Create a new topology<o:p></o:p></p>
<p class="MsoNormal"> -- Note need to generalize the SRID calculation to select the best fit UTM zone based on longitude extents<o:p></o:p></p>
<p class="MsoNormal"> execute 'SELECT topology.CreateTopology(''' || cleantopo || ''',32648, 0.000001, TRUE);';<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> EXECUTE 'Select topology.AddTopoGeometryColumn('''|| cleantopo ||''',''fgcm'','''|| updatedtablename ||''',''topo_shape'',''LINESTRING'');';<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> EXECUTE 'SELECT topology.TopologySummary(''' || cleantopo || ''');';<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"> FOR r IN <o:p></o:p></p>
<p class="MsoNormal"> EXECUTE 'SELECT objectid, f_code, shape, topo_shape FROM fgcm.' || updatedtablename
<o:p></o:p></p>
<p class="MsoNormal"> LOOP <o:p></o:p></p>
<p class="MsoNormal"> BEGIN<o:p></o:p></p>
<p class="MsoNormal"> RAISE NOTICE 'Loading % attempt with shape = % and topo_shape = %' , r.objectid, r.shape, r.topo_shape;<o:p></o:p></p>
<p class="MsoNormal"> RAISE NOTICE <o:p></o:p></p>
<p class="MsoNormal"> <span style="color:red">EXECUTE 'UPDATE fgcm.'||updatedtablename||' SET '||topo_shape||'::topogeometry<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:red"> = topology.toTopoGeom(ST_Transform('||r.shape||'::geometry,32648), '||cleantopo||', 1, 1.0)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:red"> WHERE '||objectid||' = '||r.objectid||';';<o:p></o:p></span></p>
<p class="MsoNormal"> RAISE NOTICE 'Object % after conversion from shape = % to topo_shape = %', r.objectid, (ST_AsText(r.shape)), (ST_AsText(r.topo_shape));
<o:p></o:p></p>
<p class="MsoNormal"> EXCEPTION<o:p></o:p></p>
<p class="MsoNormal"> WHEN OTHERS THEN<o:p></o:p></p>
<p class="MsoNormal"> RAISE WARNING 'Loading of record % failed: % %', r.objectid, SQLSTATE, SQLERRM;<o:p></o:p></p>
<p class="MsoNormal"> END;<o:p></o:p></p>
<p class="MsoNormal"> END LOOP;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> RETURN;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">END<o:p></o:p></p>
<p class="MsoNormal">$BODY$<o:p></o:p></p>
<p class="MsoNormal"> LANGUAGE plpgsql VOLATILE<o:p></o:p></p>
<p class="MsoNormal"> COST 100<o:p></o:p></p>
<p class="MsoNormal"> ROWS 2000;<o:p></o:p></p>
<p class="MsoNormal">ALTER FUNCTION hc_check_gaps_in_linear_topology(text, text)<o:p></o:p></p>
<p class="MsoNormal"> OWNER TO fgcm;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">SELECT * FROM fgcm.hc_check_gaps_in_linear_topology('hydrographycrv_evw', 'hydrocleantopo');<o:p></o:p></p>
<div style="mso-element:para-border-div;border:none;border-bottom:solid windowtext 1.5pt;padding:0in 0in 1.0pt 0in">
<p class="MsoNormal" style="border:none;padding:0in"><o:p> </o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I am successfully starting to execute but then failing on each feature with the following SQLERRM message:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">NOTICE: Loading 243748 attempt with shape = 01020000A0E61000002A00000054D3E418FF3B5B40E0ED7AFCED7827400000000000000000A82E2AF3003C5B40002FB6C70D7927400000000000000000B4CF3DCD043C5B408076BC99367927400000000000000000908D4575083C5B40E09A1255677927400000000000000000ECC893CF0B3C5B4020DF11278D792740000000000000000028ABADCE0D3C5B40C0C35F269979274000000000000000004CFACA6F103C5B40002F743E9F792740000000000000000040123150123C5B4040D80E4BA57927400000000000000000B47ECD4E133C5B40407C7D40AF7927400000000000000000D465366A133C5B400005D70FC379274000000000000000002063EBA2133C5B4060B3FAC2E27927400000000000000000001DEE3C143C5B40C0B79285FE79274000000000000000007CE5B486163C5B40E01C184D227A274000000000000000009C7651241A3C5B40403CF0433B7A27400000000000000000283BBB841C3C5B40205D0853437A27400000000000000000DCD71C48203C5B4040D72288447A27400000000000000000C4028F0A243C5B40209A0DB3497A2740000000000000000000594BAB283C5B40A03B54CF597A274000000000000000007CBCEDC82C3C5B4040EC53C8747A27400000000000000000C88FDABF303C5B4080C5C778AB7A27400000000000000000E04AC4FB343C5B40E003745FCE7A27400000000000000000703A4065393C5B40C060512DFC7A2740000000000000000018A0A9E13F3C5B40E02AAB23267B274000000000000000000C940F9F463C5B4080642A254D7B274000000000000000003455111D4B3C5B40208DEF23687B2740000000000000000050314D5F4F3C5B40203F7A4E707B27400000000000000000C8010528573C5B404095EACD6B7B27400000000000000000208471515C3C5B402079D949597B2740000000000000000014B344B8603C5B4060E433A64E7B27400000000000000000A83A89AD653C5B40E02A94F44C7B27400000000000000000AC4B604F683C5B40A0DEA413507B27400000000000000000041247306B3C5B40A09A0D2A587B27400000000000000000441157706D3C5B40E00FBD34617B2740000000000000000000A51B0D723C5B40A0F6D625827B27400000000000000000BCFBD94B773C5B40A07D612F9D7B27400000000000000000943C8B697C3C5B4060FC1F2DBC7B27400000000000000000C80B0546803C5B40206D6118DB7B2740000000000000000068C92A3F853C5B4020B5F1E30C7C27400000000000000000B8DA02C38B3C5B400018D57D5B7C274000000000000000003427905B913C5B40E0903546927C2740000000000000000060DCE891963C5B404065E9F4D07C27400000000000000000280C1B7C993C5B4080CC18C3F57C27400000000000000000
and topo_shape = <NULL><o:p></o:p></p>
<p class="MsoNormal">WARNING: Loading of record 243748 failed: 42725 <span style="color:red">
operator is not unique: text || geometry<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:red"><o:p> </o:p></span></p>
<p class="MsoNormal">I tried to explicitly type the variables involved in the UPDATE highlighted in the code above but to no avail.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Any observations on this would be appreciated.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Steve<o:p></o:p></p>
</div>
</body>
</html>