<div dir="ltr">Sorry I can't understand what you are trying to do !<div><br></div><div>what is your data :</div><div>line, poly?</div><div>is it topological (if yes how did you build it?).</div><div><br></div><div>What is the problem you are trying to solve ? (snapping in the topology model, or snapping of your non topological data?).</div><div><br></div><div>You seems to begin in plpgsql, may I suggest you first try to write small test function to test all commande you want to execute?</div><div><br></div><div>For me the whole function looks hilgy unusual both in syntax and meaning.<br><br></div><div>I could rewrite it if I undertsood the point !</div><div>Cheers,</div><div>Rémi-C</div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">2015-02-10 18:20 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"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Remi –<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Thanks. I am trying to generate nodes I think based on endpoints. A separate list of nodes does not exist at the onset of this procedure. At least, that
is my perception.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">I included the following at the bottom of the function:<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> END$$;'<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> EXECUTE 'return ' || updatedtablename';'<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">End<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">execute 'INTO '|| updatedtablename ||';'<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">$BODY$<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Is this what you had in mind?<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Thanks,<br>
<br>
Steve<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<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"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>]
<b>On Behalf Of </b>Rémi Cura<br>
<b>Sent:</b> Tuesday, February 10, 2015 10:47 AM<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> Re: [postgis-users] EXECUTE CREATE TABLE AS SELECT<u></u><u></u></span></p>
</div><div><div class="h5">
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">Hey,<u></u><u></u></p>
<div>
<p class="MsoNormal">it seems over complicated if you just want to do snapping.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">(snapping is : ensure that for each edge the first and last point of the edge geom are exactly the same as associated node geom.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">)<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">I think I coded it somewhere, can't find it, should be few lines.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">You get error because you can't use naked SELECT in plpgsql.<br>
You need either to SELECT .. INTO a_variable, or to use<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">PERFORM.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><br>
Cheers,<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">Rémi-C<u></u><u></u></p>
</div>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">2015-02-10 16:17 GMT+01:00 Miller, Stephan <<a href="mailto:smille12@harris.com" target="_blank">smille12@harris.com</a>>:<u></u><u></u></p>
<div>
<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)</span><u></u><u></u></p>
<p class="MsoNormal"><span style="color:red"> returns table (objectid integer, f_code char varying, topo_shape topogeometry) as</span><u></u><u></u></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;</span><u></u><u></u></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>
<p class="MsoNormal"><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">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><u></u><u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div></div></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>