Sorry I can't understand what you are trying to do ! what is your data : line, poly? is it topological (if yes how did you build it?).
What is the problem you are trying to solve ? (snapping in the topology model, or snapping of your non topological data?). 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? For me the whole function looks hilgy unusual both in syntax and meaning. I could rewrite it if I undertsood the point ! Cheers, Rémi-C 2015-02-10 18:20 GMT+01:00 Miller, Stephan <smill...@harris.com>: > Remi – > > > > 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. > > > > I included the following at the bottom of the function: > > > > END$$;' > > > > EXECUTE 'return ' || updatedtablename';' > > End > > execute 'INTO '|| updatedtablename ||';' > > $BODY$ > > > > Is this what you had in mind? > > > > Thanks, > > Steve > > > > *From:* postgis-users-boun...@lists.osgeo.org [mailto: > postgis-users-boun...@lists.osgeo.org] *On Behalf Of *Rémi Cura > *Sent:* Tuesday, February 10, 2015 10:47 AM > *To:* PostGIS Users Discussion > *Subject:* Re: [postgis-users] EXECUTE CREATE TABLE AS SELECT > > > > Hey, > > it seems over complicated if you just want to do snapping. > > (snapping is : ensure that for each edge the first and last point of the > edge geom are exactly the same as associated node geom. > > ) > > I think I coded it somewhere, can't find it, should be few lines. > > > > You get error because you can't use naked SELECT in plpgsql. > You need either to SELECT .. INTO a_variable, or to use > > PERFORM. > > > Cheers, > > Rémi-C > > > > 2015-02-10 16:17 GMT+01:00 Miller, Stephan <smill...@harris.com>: > > I am running the following configuration: > > > > "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" > > > > I am trying to generalize a procedure for checking topological integrity > (snapping endpoints of linestrings). I created a function that returns a > new table. > > > > create or replace function fgcm.hc_check_gaps_in_linear_topology(IN > tablename text, IN cleantopo TEXT) > > returns table (objectid integer, f_code char varying, topo_shape > topogeometry) as > > $BODY$ > > declare > > updatedtablename text; > > > > begin > > > > -- SELECT sde_set_current_version(10.2.1); > > > > updatedtablename = 'updated' || tablename; > > > > RAISE NOTICE 'The updated table is %', updatedtablename; > > raise NOTICE 'The input table name is %', tablename; > > raise NOTICE 'Cleantopo is %', cleantopo; > > > > --CREATE TABLE updatedtablename AS SELECT objectid, f_code, shape FROM > tablename; > > > > --execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, > f_code, shape FROM ' || tablename; > > -- RETURN QUERY per 9.2 Section 39.6.1 Returning from a function > > --return QUERY > > --execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, > f_code, shape FROM ' || tablename || ' INTO ' || updatedtablename; > > execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, > f_code, shape FROM ' || tablename; > > > > -- Drop the existing topology > > SELECT topology.DropTopology('cleantopo'); > > > > -- Create a new topology > > -- Note need to generalize the SRID calculation to select the best fit > UTM zone base on longitude extents > > SELECT topology.CreateTopology('cleantopo',32648, 0.000001, TRUE); > > > > -- Add the new topo_shape column > > Select topology.AddTopoGeometryColumn('cleantopo', 'fgcm', > 'updatedtablename', 'topo_shape', 'LINESTRING'); > > > > SELECT topology.TopologySummary('cleantopo'); > > > > DO $$DECLARE r record; > > BEGIN > > FOR r IN SELECT objectid, f_code, shape, topo_shape FROM > fgcm.updatedtablename LOOP > > BEGIN > > raise NOTICE 'Loading % attempt with shape = % and > topo_shape = %', r.objectid, r.shape, r.topo_shape; > > UPDATE fgcm.updatedtablename SET topo_shape = > topology.toTopoGeom(ST_Transform(r.shape,32648), 'red_roads_topo', 1, 1.0) > > where objectid = r.objectid; > > raise NOTICE 'Object % after conversion from shape = % to > topo_shape = %', r.objectid, (ST_AsText(r.shape)), > (ST_AsText(r.topo_shape)); > > EXCEPTION > > WHEN OTHERS THEN > > RAISE WARNING 'Loading of record % failed: % %', > r.objectid, SQLSTATE, SQLERRM; > > END; > > end loop; > > END$$; > > > > End > > $BODY$ > > language plpgsql volatile > > COST 100 > > ROWS 2000; > > -- How do I get the function saved as an fgcm.hc function? > > Alter function fgcm.hc_check_gaps_in_linear_topology(text, text) > > owner to fgcm; > > > > -- Try as follows: > > > > SELECT * FROM fgcm.hc_check_gaps_in_linear_topology('hydrographycrv_evw', > 'hydrocleantopo'); > > > > > > 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. > > > > NOTICE: The updated table is updatedhydrographycrv_evw > > NOTICE: The input table name is hydrographycrv_evw > > NOTICE: Cleantopo is hydrocleantopo > > ERROR: query has no destination for result data > > HINT: If you want to discard the results of a SELECT, use PERFORM instead. > > CONTEXT: PL/pgSQL function hc_check_gaps_in_linear_topology(text,text) > line 24 at SQL statement > > > > ********** Error ********** > > > > ERROR: query has no destination for result data > > SQL state: 42601 > > Hint: If you want to discard the results of a SELECT, use PERFORM instead. > > Context: PL/pgSQL function hc_check_gaps_in_linear_topology(text,text) > line 24 at SQL statement > > > > Steve > > > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users