Here is the "cleaned" version, still no good tough. I don't understand what you want to do, I don't have your table structure nor your data, so you will need to work from that.
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; _q text; BEGIN -- SELECT sde_set_current_version(10.2.1); updatedtablename = 'updated' || tablename; _q := format('CREATE TABLE %I AS SELECT objectid, f_code, shape FROM %I ; ' ,updatedtablename,tablename) ; EXECUTE _q ; PERFORM 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 PERFORM topology.CreateTopology(cleantopo ,32648, 0.000001, TRUE); PERFORM topology.AddTopoGeometryColumn( cleantopo , fgcm , updatedtablename ,'topo_shape','LINESTRING'); PERFORM topology.TopologySummary(cleantopo ); _q := format('SELECT objectid, f_code, shape, topo_shape FROM fgcm.%I',updatedtablename); FOR r IN EXECUTE _q LOOP BEGIN RAISE NOTICE 'Loading % attempt with shape = % and topo_shape = %' , r.objectid, r.shape, r.topo_shape; _q := format('UPDATE fgcm.%I SET %I::topogeometry = topology.toTopoGeom(ST_Transform($1::geometry,32648), %I, 1, 1.0) WHERE objectid = $2' ,updatedtablename,topo_shape,cleantopo); EXECUTE _q USING r.shape, 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; Cheers, Rémi-C
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users