[postgis-users] EXECUTE CREATE TABLE AS SELECT

[Miller, Stephan]

The input data consists of (millions) of digitized line strings.  Despite efforts to avoid gaps at the endpoints, gaps do occur.  This function is reconcile the slight differences (within a tolerance) and queue the others for review and edit.

The actual conversion from unconnected linestrings into topological edges and nodes occur in the UPADATE function of my function.

Any insights or ideas for code you could offer would be appreciated.  I am obviously quite new to SQL.

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 12:37 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] EXECUTE CREATE TABLE AS SELECT

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 <smille12 at harris.com<mailto:smille12 at 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-bounces at lists.osgeo.org<mailto:postgis-users-bounces at lists.osgeo.org> [mailto: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


_______________________________________________
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/3c40be65/attachment.html>