[postgis-users] operator is not unique: text || geometry
Rémi Cura
remi.cura at gmail.com
Wed Feb 18 11:56:31 PST 2015
(better to stay on list )
I meant
---
RAISE EXCEPTION '%',_q ;
---
You must understand that plpgsql function fabricate on the fly SQL
statement (meaning, at execution time).
That means that without actually executing the function, there is no way to
know exactly what it does.
Now i I __*can't*__ execute your function, not having your table
Now at execution,
it will stop you function there, and print the UPDATE query that should
have been executed.
Then you can analyse the UPDATE query that have been printed, and test it
to see why it doesn't work and how you could make it work (how which I have
no idea without the query).
You should see something like (I put xxx because I don' have the value.)
---------
sql NOTICE :
UPDATE fgcm.xxxx SET (x,x,x,x)::topogeometry
= topology.toTopoGeom(ST_Transform(xxxxx::geometry,32648),'xxxx', 1, 1.0)
WHERE objectid = xxxx'
---------
Maybe you need to replace the
-------
= topology.toTopoGeom(ST_Transform($1::geometry,32648), %I, 1, 1.0)
-------
with
---------
= topology.toTopoGeom(ST_Transform($1::geometry,32648), %L, 1, 1.0)
---------
I can't know.
Cheers,
Rémi-C
2015-02-18 20:43 GMT+01:00 Miller, Stephan <smille12 at harris.com>:
> Remi –
>
>
>
> I didn’t understand.
>
>
>
> Adding RAISE EXCEPTION '%',-q ; before the EXECUTE generates a syntax
> error. Did you mean perhaps
>
>
>
> RAISE EXCEPTION '%',_q ;
>
> Instead of
>
> EXECUTE _q USING r.shape, cleantopo;
>
>
>
> How do I specify the r.shape and cleantopo parameters?
>
>
>
> Sorry to be so dense.
>
>
>
> Thanks,
>
>
>
> Steve
>
>
>
> *From:* Rémi Cura [mailto:remi.cura at gmail.com]
> *Sent:* Wednesday, February 18, 2015 1:59 PM
>
> *To:* Miller, Stephan
> *Subject:* Re: [postgis-users] operator is not unique: text || geometry
>
>
>
> As I wrote before,
>
> simply print the update query (and don't execute it)
>
> You can do this by adding a RAISE EXCEPTION '%',-q ; before the EXECUTE
>
> then test it !
>
> Cheers,
> Rémi-C
>
>
>
> 2015-02-18 19:57 GMT+01:00 Miller, Stephan <smille12 at harris.com>:
>
> Remi –
>
>
>
> I forced the transform as you suggested using SetSRID. Now I am failing
> the UPDATE query somehow.
> _________________________________________________________________________________________
>
> _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;
>
> RAISE NOTICE 'Table % Shape %',
> updatedtablename, r.topo_shape;
>
> _q :=
>
> format('UPDATE fgcm.%I SET
> %I = topology.toTopoGeom(ST_Transform(ST_SetSRID($1, 4326),32648), $2, 1,
> 1.0)
>
>
> WHERE objectid = r.objectid' ,updatedtablename, r.topo_shape);
>
> EXECUTE _q USING r.shape, cleantopo;
>
> raise NOTICE 'After % Shape
> %',updatedtablename,r.topo_shape;
>
> 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;
>
>
>
> SELECT * FROM fgcm.hc_check_gaps_in_linear_topology('vnroadsclipped',
> 'VNclippedroadscleantopo');
>
>
> ______________________________________________________________________________________
>
> The results for the first feature is shown below.
>
>
>
> NOTICE: Loading 1 attempt with shape =
> 01020000E0E610000002000000380952E7B97B5A40F074DD1774CD344000000000006AE8C0000000000000F87FE825AB94B17B5A40F013885085CD344000000000006AE8C0000000000000F87F
> and topo_shape = <NULL>
>
> NOTICE: Table updatedvnroadsclipped Shape <NULL>
>
> WARNING: Loading of record 1 failed: 22004 null values cannot be
> formatted as an SQL identifier
>
>
>
> The absence of the two RAISE NOTICE prints means the UPDATE is failing
> somehow. Any suggestions?
>
>
>
> Thanks,
>
>
>
> Steve
>
>
>
> *From:* Rémi Cura [mailto:remi.cura at gmail.com]
> *Sent:* Wednesday, February 18, 2015 4:47 AM
> *To:* Miller, Stephan
>
>
> *Subject:* Re: [postgis-users] operator is not unique: text || geometry
>
>
>
> Good,
>
> maybe the srid of "$1::geometry" is not what it should be, you could try
> to force it (ST_SetSRID($1::geometry,your_srid)
> ST_Transform($1::geometry, 32468) ---> ST_Transform(ST_SetSRID($1::geometry,your_srid),
> 32468)
> Cheers,
> Rémi-C
>
>
>
> 2015-02-17 20:52 GMT+01:00 Miller, Stephan <smille12 at harris.com>:
>
> Remi –
>
>
>
> I have it working with one exception: my embedded
> ST_Transform($1::geometry, 32468) has stopped working. It is not
> transforming lat/lon to a local UTM coordinate.
>
>
>
> Thanks for your help!
>
>
>
> Steve
>
>
>
> *From:* Rémi Cura [mailto:remi.cura at gmail.com]
> *Sent:* Tuesday, February 17, 2015 11:37 AM
> *To:* Miller, Stephan
> *Cc:* PostGIS Users Discussion
> *Subject:* Re: [postgis-users] operator is not unique: text || geometry
>
>
>
> 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
>
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150218/21d948f2/attachment.html>
More information about the postgis-users
mailing list