Re: [postgis-users] operator is not unique: text || geometry
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 smill...@harris.com:
Here is the SQL.
Thanks.
Steve
*From:* Rémi Cura [mailto:remi.c...@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. SET (x,x,x,x)::topogeometry
= topology.toTopoGeom(ST_Transform(x::geometry,32648),'', 1, 1.0)
WHERE objectid = '
-
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 smill...@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.c...@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 smill...@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
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. SET (x,x,x,x)::topogeometry
= topology.toTopoGeom(ST_Transform(x::geometry,32648),'', 1, 1.0)
WHERE objectid = '
-
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 smill...@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.c...@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 smill...@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 =
0102E0E6100200380952E7B97B5A40F074DD1774CD3440006AE8C0F87FE825AB94B17B5A40F013885085CD3440006AE8C0F87F
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.c...@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 smill...@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
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.01, 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
Re: [postgis-users] operator is not unique: text || geometry
Remi –
Thanks for your response.
I tried to use “execute format('CREATE TABLE %I AS SELECT objectid, f_code,
shape FROM %I ; ', updatedtablename, tablename) ;” as you suggested. I got
the following error
ERROR: prepared statement format does not exist
** Error **
ERROR: prepared statement format does not exist
SQL state: 26000
Do I need to use a PREPARE statement to define “format” to be able to execute
this syntax?
Also, when I try to use
“ EXECUTE 'SELECT topology.CreateTopology(%s,32648, 0.001, TRUE) ; ',clean_topo;
I get :
”
ERROR: syntax error at or near 'SELECT topology.CreateTopology(%s,32648,
0.001, TRUE) ; '
LINE 1: EXECUTE 'SELECT topology.CreateTopology(%s,32648, 0.001,...
^
** Error **
ERROR: syntax error at or near 'SELECT topology.CreateTopology(%s,32648,
0.001, TRUE) ; '
SQL state: 42601
Character: 13
Finally, when I try to perform the UPDATE using _q (declared as text) as
follows:
_q:=
'UPDATE fgcm.'||updatedtablename||' SET
'||topo_shape||'::topogeometry
= topology.toTopoGeom(ST_Transform('||r.shape||'::geometry,32648),
'||cleantopo||', 1, 1.0)
WHERE '||objectid||' = '||r.objectid||';';
RAISE EXCEPTION 'here is the query to manually test : %',_q ;
I get:
ERROR: syntax error at or near _q
LINE 1: _q:=
^
** Error **
ERROR: syntax error at or near _q
SQL state: 42601
Character: 13
I also tried formulating it like this:
_q:=
'UPDATE fgcm.%s SET %s::topogeometry
= topology.toTopoGeom(ST_Transform(%s,32648), %s, 1, 1.0)
WHERE %s = %s,' updatedtablename, topo_shape, r.shape, cleantopo,
objectid, r.objectid;
I left the explicit typecast to topogeometry in place, but tried to replace all
others with simple strings.
The query executed 21 seconds then failed with:
** Error **
ERROR: syntax error at or near _q
SQL state: 42601
Character: 13
I am executing this through pgAdmin.
Any other suggestions would be appreciated.
Steve
From: Rémi Cura [mailto:remi.c...@gmail.com]
Sent: Tuesday, February 17, 2015 4:56 AM
To: PostGIS Users Discussion; Miller, Stephan
Subject: Re: [postgis-users] operator is not unique: text || geometry
Hey Stephan,
I'm afraid I must say the coding style is not good and dangerous.
And I know that the very nature of plpgsql langage (and doc) make it difficult
to produce a nice function.
AS it is, it is very hard to read it, and any user of your function could
potentially inject SQL via your function.
For instance,
--
execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid, f_code,
shape FROM ' || tablename;
should become (safe, easier to read, easier to port)
---
execute format('CREATE TABLE %I AS SELECT objectid, f_code, shape FROM %I ; ',
updatedtablename,tablename) ;
---
AS a rule of thumb, you should start asking yourself question when you abuse
quoting, like ~'''~
For instance
-
execute 'SELECT topology.CreateTopology(''' || cleantopo || ''',32648,
0.01, TRUE);';
-
could simply be replaced by
-
PERFORM topology.CreateTopology(clean_topo,32648, 0.01, TRUE) ;
-
or, if you really really want to use an EXECUTE :
-
EXECUTE 'SELECT topology.CreateTopology(%s,32648, 0.01, TRUE) ;
',clean_topo ) ;
-
You might also use the type 'regclass' instead of the type text when the text
shall always represent a table.
This would automatically raise error if the table doesn't exist, and would be
schema-qualification safe.
Now to the supposed-to-be-faulty part, you have a sql problem on top of plpgsql
problem.
I would recommend to always fabricate your sql statement, then test it
manually, then execute it.
for instance, you could declare
_q text;
then you fabricate your query :
---
_q :=
'UPDATE fgcm.'||updatedtablename||' SET '||topo_shape||'::topogeometry
= topology.toTopoGeom(ST_Transform('||r.shape||'::geometry,32648),
'||cleantopo||', 1, 1.0)
WHERE '||objectid||' = '||r.objectid||';';
RAISE EXCEPTION 'here is the query to manually test : %',_q ;
---
then you print _q and execute it manually to see if the sql syntax is correct :
(copy past the query given at execution time, then try to execute it in pgadmin
or psql to check that syntax is correct).
The problem in this query is that it doesn't respect the SQL UPDATE syntax :
you should do something like :
UPDATE your_table_name SET (-list_of_columns_to_update-) =
(expression_matching_list_of_columns) WHERE ...
So you can see that SET '||topo_shape||'::topogeometry is not correct.
It is the same for your WHERE part :
WHERE '||objectid||' = '||r.objectid||';';
Now it could be a feature of your code (like storing the name of columns
Re: [postgis-users] operator is not unique: text || geometry
On Mon, Feb 16, 2015 at 10:02:14PM +, Miller, Stephan wrote: I highlighted where I think the error is occurring in red below. Please, *please*, don't use colors to highlight texts. I know we're in 2015 and the internet is full of colorful moving puppets, but if we stick to low tech it's easier for everyone to partecipate. Personally, I don't see colors in emails. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] operator is not unique: text || geometry
Hey Stephan,
I'm afraid I must say the coding style is not good and dangerous.
And I know that the very nature of plpgsql langage (and doc) make it
difficult to produce a nice function.
AS it is, it is very hard to read it, and any user of your function could
potentially inject SQL via your function.
For instance,
--
execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid,
f_code, shape FROM ' || tablename;
should become (safe, easier to read, easier to port)
---
execute format('CREATE TABLE %I AS SELECT objectid, f_code, shape FROM %I
; ', updatedtablename,tablename) ;
---
AS a rule of thumb, you should start asking yourself question when you
abuse quoting, like ~'''~
For instance
-
execute 'SELECT topology.CreateTopology(''' || cleantopo || ''',32648,
0.01, TRUE);';
-
could simply be replaced by
-
PERFORM topology.CreateTopology(clean_topo,32648, 0.01, TRUE) ;
-
or, if you really really want to use an EXECUTE :
-
EXECUTE 'SELECT topology.CreateTopology(%s,32648, 0.01, TRUE) ;
',clean_topo ) ;
-
You might also use the type 'regclass' instead of the type text when the
text shall always represent a table.
This would automatically raise error if the table doesn't exist, and would
be schema-qualification safe.
Now to the supposed-to-be-faulty part, you have a sql problem on top of
plpgsql problem.
I would recommend to always fabricate your sql statement, then test it
manually, then execute it.
for instance, you could declare
_q text;
then you fabricate your query :
---
_q :=
'UPDATE fgcm.'||updatedtablename||' SET '||topo_shape||'::topogeometry
=
topology.toTopoGeom(ST_Transform('||r.shape||'::geometry,32648),
'||cleantopo||', 1, 1.0)
WHERE '||objectid||' = '||r.objectid||';';
RAISE EXCEPTION 'here is the query to manually test : %',_q ;
---
then you print _q and execute it manually to see if the sql syntax is
correct :
(copy past the query given at execution time, then try to execute it in
pgadmin or psql to check that syntax is correct).
The problem in this query is that it doesn't respect the SQL UPDATE syntax :
you should do something like :
UPDATE your_table_name SET (-*list_of_columns_to_update*-) =
(expression_matching_list_of_columns) WHERE ...
So you can see that SET '||topo_shape||'::topogeometry is not correct.
It is the same for your WHERE part :
WHERE '||objectid||' = '||r.objectid||';';
Now it could be a feature of your code (like storing the name of columns to
use in another table), I don't understand it sufficiently to say so.
Cheers,
Rémi-C
2015-02-17 10:04 GMT+01:00 Sandro Santilli s...@keybit.net:
On Mon, Feb 16, 2015 at 10:02:14PM +, Miller, Stephan wrote:
I highlighted where I think the error is occurring in red below.
Please, *please*, don't use colors to highlight texts.
I know we're in 2015 and the internet is full of colorful moving puppets,
but if we stick to low tech it's easier for everyone to partecipate.
Personally, I don't see colors in emails.
--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] operator is not unique: text || geometry
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.01, 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 =
