[postgis-users] EXECUTE CREATE TABLE AS SELECT
Miller, Stephan
smille12 at harris.com
Tue Feb 10 09:20:25 PST 2015
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-bounces at lists.osgeo.org [mailto:postgis-users-bounces at 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 <smille12 at harris.com<mailto:smille12 at 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 at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150210/a400d774/attachment.html>
More information about the postgis-users
mailing list