[postgis-users] operator is not unique: text || geometry

[Miller, Stephan]

All -

I am running the following PostGIS configuration on PostgreSQL 9.2

"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 have successfully validated topology for a single feature class of 1.2 million road features with hard-coded names and have tried to generalize that into a function with parameters that will let me check for dangling edges in any linear feature class. Here is a listing of the function.  I highlighted where I think the error is occurring in red below.
----------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION hc_check_gaps_in_linear_topology(IN tablename text, IN cleantopo text)
  RETURNS TABLE(objectid integer, f_code character varying, topo_shape topogeometry) AS
$BODY$
declare
    updatedtablename text;
DECLARE
    r record;

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.  See also the link to PostGISBlockFunction under pgTopology folder.
    --return QUERY
    execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, f_code, shape  FROM ' || tablename;
    --return QUERY
    --execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, f_code, shape  FROM ' || tablename;

    -- Drop the existing topology
    EXECUTE  'SELECT topology.DropTopology('''|| cleantopo ||''');';

    -- Create a new topology
    -- Note need to generalize the SRID calculation to select the best fit UTM zone based on longitude extents
    execute 'SELECT topology.CreateTopology(''' || cleantopo || ''',32648, 0.000001, TRUE);';

    EXECUTE  'Select topology.AddTopoGeometryColumn('''|| cleantopo ||''',''fgcm'','''|| updatedtablename ||''',''topo_shape'',''LINESTRING'');';

    EXECUTE  'SELECT topology.TopologySummary(''' || cleantopo || ''');';


    FOR r IN
    EXECUTE '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;
            RAISE NOTICE
            EXECUTE  'UPDATE fgcm.'||updatedtablename||' SET '||topo_shape||'::topogeometry
             = topology.toTopoGeom(ST_Transform('||r.shape||'::geometry,32648), '||cleantopo||', 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;

    RETURN;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 2000;
ALTER FUNCTION hc_check_gaps_in_linear_topology(text, text)
  OWNER TO fgcm;

SELECT * FROM fgcm.hc_check_gaps_in_linear_topology('hydrographycrv_evw', 'hydrocleantopo');


I am successfully starting to execute but then failing on each feature with the following SQLERRM message:

NOTICE:  Loading 243748 attempt with shape = 01020000A0E61000002A00000054D3E418FF3B5B40E0ED7AFCED7827400000000000000000A82E2AF3003C5B40002FB6C70D7927400000000000000000B4CF3DCD043C5B408076BC99367927400000000000000000908D4575083C5B40E09A1255677927400000000000000000ECC893CF0B3C5B4020DF11278D792740000000000000000028ABADCE0D3C5B40C0C35F269979274000000000000000004CFACA6F103C5B40002F743E9F792740000000000000000040123150123C5B4040D80E4BA57927400000000000000000B47ECD4E133C5B40407C7D40AF7927400000000000000000D465366A133C5B400005D70FC379274000000000000000002063EBA2133C5B4060B3FAC2E27927400000000000000000001DEE3C143C5B40C0B79285FE79274000000000000000007CE5B486163C5B40E01C184D227A274000000000000000009C7651241A3C5B40403CF0433B7A27400000000000000000283BBB841C3C5B40205D0853437A27400000000000000000DCD71C48203C5B4040D72288447A27400000000000000000C4028F0A243C5B40209A0DB3497A2740000000000000000000594BAB283C5B40A03B54CF597A274000000000000000007CBCEDC82C3C5B4040EC53C8747A27400000000000000000C88FDABF303C5B4080C5C778AB7A27400000000000000000E04AC4FB343C5B40E003745FCE7A27400000000000000000703A4065393C5B40C060512DFC7A2740000000000000000018A0A9E13F3C5B40E02AAB23267B274000000000000000000C940F9F463C5B4080642A254D7B274000000000000000003455111D4B3C5B40208DEF23687B2740000000000000000050314D5F4F3C5B40203F7A4E707B27400000000000000000C8010528573C5B404095EACD6B7B27400000000000000000208471515C3C5B402079D949597B2740000000000000000014B344B8603C5B4060E433A64E7B27400000000000000000A83A89AD653C5B40E02A94F44C7B27400000000000000000AC4B604F683C5B40A0DEA413507B27400000000000000000041247306B3C5B40A09A0D2A587B27400000000000000000441157706D3C5B40E00FBD34617B2740000000000000000000A51B0D723C5B40A0F6D625827B27400000000000000000BCFBD94B773C5B40A07D612F9D7B27400000000000000000943C8B697C3C5B4060FC1F2DBC7B27400000000000000000C80B0546803C5B40206D6118DB7B2740000000000000000068C92A3F853C5B4020B5F1E30C7C27400000000000000000B8DA02C38B3C5B400018D57D5B7C274000000000000000003427905B913C5B40E0903546927C2740000000000000000060DCE891963C5B404065E9F4D07C27400000000000000000280C1B7C993C5B4080CC18C3F57C27400000000000000000 and topo_shape = <NULL>
WARNING:  Loading of record 243748 failed: 42725 operator is not unique: text || geometry

I tried to explicitly type the variables involved in the UPDATE highlighted in the code above but to no avail.

Any observations on this would be appreciated.

Thanks,

Steve
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150216/0d685bbd/attachment.html>