<div dir="ltr">Hey,<div>it seems over complicated if you just want to do snapping.</div><div>(snapping is : ensure that for each edge the first and last point of the edge geom are exactly the same as associated node geom.</div><div>)</div><div>I think I coded it somewhere, can't find it, should be few lines.</div><div><br></div><div>You get error because you can't use naked SELECT in plpgsql.<br>You need either to SELECT .. INTO a_variable, or to use</div><div>PERFORM.</div><div><br>Cheers,</div><div>Rémi-C</div></div><div class="gmail_extra"><br><div class="gmail_quote">2015-02-10 16:17 GMT+01:00 Miller, Stephan <span dir="ltr"><<a href="mailto:smille12@harris.com" target="_blank">smille12@harris.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="EN-US" link="blue" vlink="purple">
<div>
<p class="MsoNormal">I am running the following configuration:<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></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"<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">I am trying to generalize a procedure for checking topological integrity (snapping endpoints of linestrings). I created a function that returns a new table.
<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><span style="color:red">create or replace function fgcm.hc_check_gaps_in_linear_topology(IN tablename text, IN cleantopo TEXT)<u></u><u></u></span></p>
<p class="MsoNormal"><span style="color:red"> returns table (objectid integer, f_code char varying, topo_shape topogeometry) as<u></u><u></u></span></p>
<p class="MsoNormal">$BODY$<u></u><u></u></p>
<p class="MsoNormal">declare<u></u><u></u></p>
<p class="MsoNormal"> updatedtablename text;<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">begin<u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal"> -- SELECT sde_set_current_version(10.2.1);<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"> updatedtablename = 'updated' || tablename;<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"> RAISE NOTICE 'The updated table is %', updatedtablename;<u></u><u></u></p>
<p class="MsoNormal"> raise NOTICE 'The input table name is %', tablename;<u></u><u></u></p>
<p class="MsoNormal"> raise NOTICE 'Cleantopo is %', cleantopo;<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"> --CREATE TABLE updatedtablename AS SELECT objectid, f_code, shape FROM tablename;<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"> --execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, f_code, shape FROM ' || tablename;<u></u><u></u></p>
<p class="MsoNormal"> -- RETURN QUERY per 9.2 Section 39.6.1 Returning from a function<u></u><u></u></p>
<p class="MsoNormal"> --return QUERY <u></u><u></u></p>
<p class="MsoNormal"> --execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, f_code, shape FROM ' || tablename || ' INTO ' || updatedtablename;<u></u><u></u></p>
<p class="MsoNormal"><span style="color:red"> execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, f_code, shape FROM ' || tablename;<u></u><u></u></span></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal"> -- Drop the existing topology<u></u><u></u></p>
<p class="MsoNormal"> SELECT topology.DropTopology('cleantopo');<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"> -- Create a new topology<u></u><u></u></p>
<p class="MsoNormal"> -- Note need to generalize the SRID calculation to select the best fit UTM zone base on longitude extents<u></u><u></u></p>
<p class="MsoNormal"> SELECT topology.CreateTopology('cleantopo',32648, 0.000001, TRUE);<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"> -- Add the new topo_shape column<u></u><u></u></p>
<p class="MsoNormal"> Select topology.AddTopoGeometryColumn('cleantopo', 'fgcm', 'updatedtablename', 'topo_shape', 'LINESTRING');<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"> SELECT topology.TopologySummary('cleantopo');<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"> DO $$DECLARE r record;<u></u><u></u></p>
<p class="MsoNormal"> BEGIN<u></u><u></u></p>
<p class="MsoNormal"> FOR r IN SELECT objectid, f_code, shape, topo_shape FROM fgcm.updatedtablename LOOP<u></u><u></u></p>
<p class="MsoNormal"> BEGIN<u></u><u></u></p>
<p class="MsoNormal"> raise NOTICE 'Loading % attempt with shape = % and topo_shape = %', r.objectid, r.shape, r.topo_shape;
<u></u><u></u></p>
<p class="MsoNormal"> UPDATE fgcm.updatedtablename SET topo_shape = topology.toTopoGeom(ST_Transform(r.shape,32648), 'red_roads_topo', 1, 1.0)<u></u><u></u></p>
<p class="MsoNormal"> where objectid = r.objectid;<u></u><u></u></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));
<u></u><u></u></p>
<p class="MsoNormal"> EXCEPTION<u></u><u></u></p>
<p class="MsoNormal"> WHEN OTHERS THEN<u></u><u></u></p>
<p class="MsoNormal"> RAISE WARNING 'Loading of record % failed: % %', r.objectid, SQLSTATE, SQLERRM;<u></u><u></u></p>
<p class="MsoNormal"> END;<u></u><u></u></p>
<p class="MsoNormal"> end loop;<u></u><u></u></p>
<p class="MsoNormal"> END$$;<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">End<u></u><u></u></p>
<p class="MsoNormal">$BODY$<u></u><u></u></p>
<p class="MsoNormal">language plpgsql volatile<u></u><u></u></p>
<p class="MsoNormal">COST 100<u></u><u></u></p>
<p class="MsoNormal">ROWS 2000;<u></u><u></u></p>
<p class="MsoNormal">-- How do I get the function saved as an fgcm.hc function?<u></u><u></u></p>
<p class="MsoNormal">Alter function fgcm.hc_check_gaps_in_linear_topology(text, text)
<u></u><u></u></p>
<p class="MsoNormal"> owner to fgcm;<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">-- Try as follows:<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">SELECT * FROM fgcm.hc_check_gaps_in_linear_topology('hydrographycrv_evw', 'hydrocleantopo');<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">I am encountering the following error (ERROR: query has no destination for result data) when I try to EXECUTE a CREATE TABLE AS command at line 35. I have tried to add a RETURN QUERY and adding INTO on the end of the command line. Any
other ideas or suggestions would be appreciated.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">NOTICE: The updated table is updatedhydrographycrv_evw<u></u><u></u></p>
<p class="MsoNormal">NOTICE: The input table name is hydrographycrv_evw<u></u><u></u></p>
<p class="MsoNormal">NOTICE: Cleantopo is hydrocleantopo<u></u><u></u></p>
<p class="MsoNormal">ERROR: query has no destination for result data<u></u><u></u></p>
<p class="MsoNormal">HINT: If you want to discard the results of a SELECT, use PERFORM instead.<u></u><u></u></p>
<p class="MsoNormal">CONTEXT: PL/pgSQL function hc_check_gaps_in_linear_topology(text,text) line 24 at SQL statement<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">********** Error **********<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">ERROR: query has no destination for result data<u></u><u></u></p>
<p class="MsoNormal">SQL state: 42601<u></u><u></u></p>
<p class="MsoNormal">Hint: If you want to discard the results of a SELECT, use PERFORM instead.<u></u><u></u></p>
<p class="MsoNormal">Context: PL/pgSQL function hc_check_gaps_in_linear_topology(text,text) line 24 at SQL statement<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Steve<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>