[postgis-users] operator is not unique: text || geometry
Rémi Cura
remi.cura at gmail.com
Fri Feb 20 05:30:57 PST 2015
Sorry this it out of question.
You must be aware that I help people on the mailing list on my free time.
(I'm a researcher)
In my opinion, rewriting your function, then debugging it is awfully close
to proper work and not help.
This is particularly a problem because your question is not related to
postgis improvement or other open source project.
I consider I already did more than help by giving you tools to understand
your problem and solve it, then even rewrite your function.
There are many people on this mailing list that do this kind of function
writing for a living (not my case).
If you have a specific question I may answer it.
Cheers,
RémiC
2015-02-18 23:29 GMT+01:00 Miller, Stephan <smille12 at harris.com>:
> Here is the SQL.
>
>
>
> Thanks.
>
>
>
> Steve
>
>
>
> *From:* Rémi Cura [mailto:remi.cura at gmail.com]
> *Sent:* Wednesday, February 18, 2015 2:57 PM
> *To:* Miller, Stephan; PostGIS Users Discussion
>
> *Subject:* Re: [postgis-users] operator is not unique: text || geometry
>
>
>
> (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/20150220/24b919fc/attachment.html>
More information about the postgis-users
mailing list