From 3fd9f1e4ea5c0eac87cc69b9c17a1de985c7c62d Mon Sep 17 00:00:00 2001 From: Sandro Santilli Date: Tue, 4 Feb 2014 08:39:05 +0000 Subject: [PATCH] Drop more geometry::text casts (#2616) Drop them from get*bypoint functions and from TopoJSON and GML export functions git-svn-id: http://svn.osgeo.org/postgis/trunk@12217 b70326c6-7e19-0410-871a-916f4a2858ee --- NEWS | 2 +- topology/sql/export/TopoJSON.sql.in | 27 ++++++++++------------- topology/sql/export/gml.sql.in | 24 +++++++++----------- topology/sql/query/getedgebypoint.sql.in | 13 +++++------ topology/sql/query/getfacebypoint.sql.in | 28 ++++++++++-------------- topology/sql/query/getnodebypoint.sql.in | 13 +++++------ 6 files changed, 45 insertions(+), 62 deletions(-) diff --git a/NEWS b/NEWS index 6858413c1..8136d9d61 100644 --- a/NEWS +++ b/NEWS @@ -29,7 +29,7 @@ PostGIS 2.2.0 - #2390, Testsuite for pgsql2shp - #2527, Added -k flag to raster2pgsql to skip checking that band is NODATA - - #2616, Reduce text casts during topology building + - #2616, Reduce text casts during topology building and export * Bug Fixes * diff --git a/topology/sql/export/TopoJSON.sql.in b/topology/sql/export/TopoJSON.sql.in index f27ad6c2b..2a2ab6a4f 100644 --- a/topology/sql/export/TopoJSON.sql.in +++ b/topology/sql/export/TopoJSON.sql.in @@ -67,27 +67,24 @@ BEGIN FOR rec IN SELECT (ST_Dump(topology.Geometry(tg))).geom LOOP -- { - sql := 'SELECT e.*, ST_LineLocatePoint(' - || quote_literal(rec.geom::text) + sql := 'SELECT e.*, ST_LineLocatePoint($1' || ', ST_LineInterpolatePoint(e.geom, 0.2)) as pos' - || ', ST_LineLocatePoint(' - || quote_literal(rec.geom::text) + || ', ST_LineLocatePoint($1' || ', ST_LineInterpolatePoint(e.geom, 0.8)) as pos2 FROM ' || quote_ident(toponame) - || '.edge e WHERE ST_Covers(' - || quote_literal(rec.geom::text) + || '.edge e WHERE ST_Covers($1' || ', e.geom) ORDER BY pos'; -- TODO: add relation to the conditional, to reduce load ? - FOR rec2 IN EXECUTE sql + FOR rec2 IN EXECUTE sql USING rec.geom LOOP -- { IF edgeMapTable IS NOT NULL THEN - sql := 'SELECT arc_id-1 FROM ' || edgeMapTable::text || ' WHERE edge_id = ' || rec2.edge_id; - EXECUTE sql INTO arcid; + sql := 'SELECT arc_id-1 FROM ' || edgeMapTable::text || ' WHERE edge_id = $1'; + EXECUTE sql INTO arcid USING rec2.edge_id; IF arcid IS NULL THEN EXECUTE 'INSERT INTO ' || edgeMapTable::text - || '(edge_id) VALUES (' || rec2.edge_id || ') RETURNING arc_id-1' - INTO arcid; + || '(edge_id) VALUES ($1) RETURNING arc_id-1' + INTO arcid USING rec2.edge_id; END IF; ELSE arcid := rec2.edge_id; @@ -230,12 +227,12 @@ FROM _edgepath END IF; IF edgeMapTable IS NOT NULL THEN - sql := 'SELECT arc_id-1 FROM ' || edgeMapTable::text || ' WHERE edge_id = ' || rec.edge_id; - EXECUTE sql INTO arcid; + sql := 'SELECT arc_id-1 FROM ' || edgeMapTable::text || ' WHERE edge_id = $1'; + EXECUTE sql INTO arcid USING rec.edge_id; IF arcid IS NULL THEN EXECUTE 'INSERT INTO ' || edgeMapTable::text - || '(edge_id) VALUES (' || rec.edge_id || ') RETURNING arc_id-1' - INTO arcid; + || '(edge_id) VALUES ($1) RETURNING arc_id-1' + INTO arcid USING rec.edge_id; END IF; ELSE arcid := rec.edge_id-1; diff --git a/topology/sql/export/gml.sql.in b/topology/sql/export/gml.sql.in index 7306507ba..17314d91a 100644 --- a/topology/sql/export/gml.sql.in +++ b/topology/sql/export/gml.sql.in @@ -199,18 +199,16 @@ BEGIN bounds = ST_Boundary(rec.geom); FOR rec2 IN EXECUTE - 'SELECT e.*, ST_LineLocatePoint(' - || quote_literal(bounds::text) + 'SELECT e.*, ST_LineLocatePoint($1' || ', ST_LineInterpolatePoint(e.geom, 0.2)) as pos' - || ', ST_LineLocatePoint(' - || quote_literal(bounds::text) + || ', ST_LineLocatePoint($1' || ', ST_LineInterpolatePoint(e.geom, 0.8)) as pos2 FROM ' || quote_ident(toponame) - || '.edge e WHERE ( e.left_face = ' || face_id - || ' OR e.right_face = ' || face_id - || ') AND ST_Covers(' - || quote_literal(bounds::text) + || '.edge e WHERE ( e.left_face = $2' + || ' OR e.right_face = $2' + || ') AND ST_Covers($1' || ', e.geom) ORDER BY pos' + USING bounds, face_id LOOP gml = gml || '<' || nsprefix || 'directedEdge'; @@ -347,17 +345,15 @@ BEGIN FOR rec IN SELECT (ST_Dump(topology.Geometry(tg))).geom LOOP FOR rec2 IN EXECUTE - 'SELECT e.*, ST_LineLocatePoint(' - || quote_literal(rec.geom::text) + 'SELECT e.*, ST_LineLocatePoint($1' || ', ST_LineInterpolatePoint(e.geom, 0.2)) as pos' - || ', ST_LineLocatePoint(' - || quote_literal(rec.geom::text) + || ', ST_LineLocatePoint($1' || ', ST_LineInterpolatePoint(e.geom, 0.8)) as pos2 FROM ' || quote_ident(toponame) - || '.edge e WHERE ST_Covers(' - || quote_literal(rec.geom::text) + || '.edge e WHERE ST_Covers($1' || ', e.geom) ORDER BY pos' -- TODO: add relation to the conditional, to reduce load ? + USING rec.geom LOOP gml = gml || '<' || nsprefix || 'directedEdge'; diff --git a/topology/sql/query/getedgebypoint.sql.in b/topology/sql/query/getedgebypoint.sql.in index 2ac7064e2..7d525b24e 100644 --- a/topology/sql/query/getedgebypoint.sql.in +++ b/topology/sql/query/getedgebypoint.sql.in @@ -62,20 +62,17 @@ BEGIN if tol1 = 0 then - sql := 'SELECT a.edge_id FROM ' + sql := 'SELECT edge_id FROM ' || quote_ident(atopology) - || '.edge_data as a WHERE ' - || '(a.geom && ' || quote_literal(apoint::text)||'::geometry) ' - || ' AND (ST_Intersects(a.geom,' || quote_literal(apoint::text)||'::geometry) );'; + || '.edge_data WHERE ST_Intersects(geom, $1)'; else - sql := 'SELECT a.edge_id FROM ' + sql := 'SELECT edge_id FROM ' || quote_ident(atopology) - || '.edge_data as a WHERE ' - || '(ST_DWithin(a.geom,' || quote_literal(apoint::text)||'::geometry,' || tol1::text || ') );'; + || '.edge_data WHERE ST_DWithin(geom, $1, $2)'; end if; BEGIN - EXECUTE sql INTO STRICT idedge; + EXECUTE sql INTO STRICT idedge USING apoint, tol1; EXCEPTION WHEN NO_DATA_FOUND THEN idedge = 0; diff --git a/topology/sql/query/getfacebypoint.sql.in b/topology/sql/query/getfacebypoint.sql.in index fec500237..463611f53 100644 --- a/topology/sql/query/getfacebypoint.sql.in +++ b/topology/sql/query/getfacebypoint.sql.in @@ -65,21 +65,17 @@ BEGIN -- first test is to check if there is inside an mbr -- if tol1 = 0 then - sql := 'SELECT a.face_id FROM ' + sql := 'SELECT face_id FROM ' || quote_ident(atopology) - || '.face as a WHERE ' - || '(a.mbr && ' || quote_literal(apoint::text)||'::geometry) ' - || 'LIMIT 1;'; + || '.face WHERE mbr && $1 LIMIT 1'; else - sql := 'SELECT a.face_id FROM ' + sql := 'SELECT face_id FROM ' || quote_ident(atopology) - || '.face as a WHERE ' - || '(ST_DWithin(a.mbr,' || quote_literal(apoint::text)||'::geometry,' || tol1::text || ') ) ' - || 'LIMIT 1;'; + || '.face as a WHERE ST_DWithin(mbr, $1, $2) LIMIT 1'; end if; BEGIN - EXECUTE sql INTO STRICT idface; + EXECUTE sql INTO STRICT idface USING apoint, tol1; EXCEPTION WHEN NO_DATA_FOUND THEN idface = 0; @@ -97,7 +93,7 @@ BEGIN || quote_ident(atopology) || '.edge_data as b,' || '(SELECT a.face_id FROM ' || quote_ident(atopology) || '.face as a ' - || 'WHERE ST_Intersects(a.mbr,' || quote_literal(apoint::text)||'::geometry)=true' + || 'WHERE ST_Intersects(a.mbr,$1)=true' || ') as c ' || 'WHERE (b.left_face = c.face_id) ' || ' UNION ALL ' @@ -105,13 +101,13 @@ BEGIN || quote_ident(atopology) || '.edge_data as b,' || '(SELECT a.face_id FROM ' || quote_ident(atopology) || '.face as a ' - || 'WHERE ST_Intersects(a.mbr,' || quote_literal(apoint::text)||'::geometry)=true' + || 'WHERE ST_Intersects(a.mbr,$1)=true' || ') as c ' || 'WHERE (b.right_face = c.face_id) ' || ') as d ' || 'GROUP BY face_id ' || ') as e ' - || 'WHERE ST_Intersects(e.geom, ' || quote_literal(apoint::text)||'::geometry)=true;'; + || 'WHERE ST_Intersects(e.geom, $1)'; else sql := 'SELECT e.face_id FROM (' || 'SELECT d.face_id,ST_BuildArea(ST_Union(geom)) as geom FROM (' @@ -119,7 +115,7 @@ BEGIN || quote_ident(atopology) || '.edge_data as b,' || '(SELECT a.face_id FROM ' || quote_ident(atopology) || '.face as a ' - || 'WHERE ST_DWithin(a.mbr,' || quote_literal(apoint::text)||'::geometry,' || tol1::text || ')=true' + || 'WHERE ST_DWithin(a.mbr,$1,$2)' || ') as c ' || 'WHERE (b.left_face = c.face_id) ' || ' UNION ALL ' @@ -127,19 +123,19 @@ BEGIN || quote_ident(atopology) || '.edge_data as b,' || '(SELECT a.face_id FROM ' || quote_ident(atopology) || '.face as a ' - || 'WHERE ST_DWithin(a.mbr,' || quote_literal(apoint::text)||'::geometry,' || tol1::text || ')=true' + || 'WHERE ST_DWithin(a.mbr,$1,$2)' || ') as c ' || 'WHERE (b.right_face = c.face_id) ' || ') as d ' || 'GROUP BY face_id ' || ') as e ' - || 'WHERE ST_DWithin(e.geom, ' || quote_literal(apoint::text)||'::geometry,' || tol1::text || ')=true;'; + || 'WHERE ST_DWithin(e.geom, $1, $2)'; end if; RAISE DEBUG ' ==> %',sql; BEGIN - EXECUTE sql INTO STRICT idface; + EXECUTE sql INTO STRICT idface USING apoint, tol1; EXCEPTION WHEN NO_DATA_FOUND THEN idface = 0; diff --git a/topology/sql/query/getnodebypoint.sql.in b/topology/sql/query/getnodebypoint.sql.in index f2091032a..db007afa7 100644 --- a/topology/sql/query/getnodebypoint.sql.in +++ b/topology/sql/query/getnodebypoint.sql.in @@ -59,20 +59,17 @@ BEGIN if tol1 = 0 then - sql := 'SELECT a.node_id FROM ' + sql := 'SELECT node_id FROM ' || quote_ident(atopology) - || '.node as a WHERE ' - || '(a.geom && ' || quote_literal(apoint::text)||'::geometry) ' - || ' AND (ST_Intersects(a.geom,' || quote_literal(apoint::text)||'::geometry) );'; + || '.node WHERE ST_Intersects(geom, $1)'; else - sql := 'SELECT a.node_id FROM ' + sql := 'SELECT node_id FROM ' || quote_ident(atopology) - || '.node as a WHERE ' - || '(ST_DWithin(a.geom,' || quote_literal(apoint::text)||'::geometry,' || tol1::text || ') );'; + || '.node WHERE ST_DWithin(geom, $1, $2)'; end if; BEGIN - EXECUTE sql INTO STRICT idnode; + EXECUTE sql INTO STRICT idnode USING apoint, tol1; EXCEPTION WHEN NO_DATA_FOUND THEN idnode = 0; -- 2.40.0