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 to
[postgis-users] TIGER 2014 data
Hi Everybody, Running: POSTGIS=2.2.0dev r13208 GEOS=3.4.2-CAPI-1.8.2 r3921 PROJ=Rel. 4.8.0, 6 March 2012 GDAL=GDAL 1.10.1, released 2013/08/26 LIBXML=2.9.1 LIBJSON=0.11.99 TOPOLOGY RASTER In the process of loading 2014 TIGER data but see there are issues with the TABBLOCK table as it is empty. The FACES table contains data but afraid it is missing much due to new naming conventions. I've seen this but uncertain of status:https://trac.osgeo.org/postgis/ticket/2915. 1 - Is there a fix for this? 2 - Does the missing data affect geocoding? If there are any speed/accuracy stories compared to the 2013 data set I would like to hear about them. Thanks! -tom ___ 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
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
