From 966db076327427f90e1e74eb68a20bfc5194914d Mon Sep 17 00:00:00 2001 From: Sandro Santilli Date: Tue, 24 May 2011 06:55:29 +0000 Subject: [PATCH] Do not let ST_AddIsoNode add non-isolated nodes. Allow specifying 0 as the isolated node face and check it is correct. If containing_face is given as null then compute it. Patch by Andrea Peri. [RT-SIGTA] git-svn-id: http://svn.osgeo.org/postgis/trunk@7222 b70326c6-7e19-0410-871a-916f4a2858ee --- topology/sql/sqlmm.sql | 98 ++++++++++++++------ topology/test/Makefile | 1 + topology/test/regress/sqlmm_expected | 2 +- topology/test/regress/st_addisonode.sql | 75 +++++++++++++++ topology/test/regress/st_addisonode_expected | 42 +++++++++ 5 files changed, 190 insertions(+), 28 deletions(-) create mode 100644 topology/test/regress/st_addisonode.sql create mode 100644 topology/test/regress/st_addisonode_expected diff --git a/topology/sql/sqlmm.sql b/topology/sql/sqlmm.sql index 462694c29..cbe8bd687 100644 --- a/topology/sql/sqlmm.sql +++ b/topology/sql/sqlmm.sql @@ -693,6 +693,8 @@ DECLARE apoint ALIAS FOR $3; rec RECORD; nodeid integer; + sql text; + containingface integer; BEGIN -- @@ -733,7 +735,7 @@ BEGIN -- FOR rec IN EXECUTE 'SELECT edge_id FROM ' || quote_ident(atopology) || '.edge ' - || 'WHERE geom && ' || quote_literal(apoint::text) + || 'WHERE (geom && ' || quote_literal(apoint::text) ||'::geometry) ' || ' AND ST_Intersects(geom, ' || quote_literal(apoint::text) || '::geometry)' LOOP @@ -741,29 +743,72 @@ BEGIN 'SQL/MM Spatial exception - edge crosses node.'; END LOOP; + -- retrieve the face that contains (eventually) the point + + -- + -- first test is to check if there is inside an mbr (more fast) + -- + sql := 'SELECT a.face_id FROM ' + || quote_ident(atopology) + || '.face as a WHERE ' + || '(a.mbr && ' || quote_literal(apoint::text)||'::geometry) ' + || 'LIMIT 1;'; +--raise notice ' ==>%',sql; + BEGIN + EXECUTE sql INTO STRICT containingface; + EXCEPTION + WHEN NO_DATA_FOUND THEN + containingface := 0; + END; + + IF containingface > 0 THEN + -- + -- probably there is something so now check the exact test + -- + sql := 'SELECT e.face_id FROM (' + || 'SELECT d.face_id,ST_BuildArea(ST_Union(geom)) as geom FROM (' + || 'SELECT b.edge_id as edge_id,b.left_face as face_id,b.geom as geom FROM ' + || 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' + || ') as c ' + || 'WHERE (b.left_face = c.face_id) ' + || ' UNION ALL ' + || 'SELECT b.edge_id as edge_id, b.right_face as face_id, b.geom as geom FROM ' + || 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' + || ') 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;'; + + --raise notice ' ==> %',sql; + BEGIN + EXECUTE sql INTO STRICT containingface; + EXCEPTION + WHEN NO_DATA_FOUND THEN + containingface = 0; + WHEN TOO_MANY_ROWS THEN + RAISE EXCEPTION 'Two or more faces found'; + END; + END IF; -- -- Verify that aface contains apoint - -- if aface is null no check is done -- - IF aface IS NOT NULL THEN - - FOR rec IN EXECUTE 'SELECT ST_Within(' - || quote_literal(apoint::text) || '::geometry, - topology.ST_GetFaceGeometry(' - || quote_literal(atopology) || ', ' || aface || - ')) As within' - LOOP - IF rec.within = 'f' THEN - RAISE EXCEPTION - 'SQL/MM Spatial exception - not within face'; - ELSIF rec.within IS NULL THEN - RAISE EXCEPTION - 'SQL/MM Spatial exception - non-existent face'; + IF aface IS NOT NULL THEN + -- if aface is 0 (UniverseFace) no check is done + IF (aface <> containingface) THEN + RAISE EXCEPTION + 'SQL/MM Spatial exception - not within face'; END IF; - END LOOP; - END IF; + -- passing last IF mean the containingface is the right value to use. -- -- Get new node id from sequence @@ -777,19 +822,18 @@ BEGIN -- -- Insert the new row -- - IF aface IS NOT NULL THEN - EXECUTE 'INSERT INTO ' || quote_ident(atopology) + sql := 'INSERT INTO ' || quote_ident(atopology) || '.node(node_id, geom, containing_face) VALUES('||nodeid||','||quote_literal(apoint::text)|| - ','||aface||')'; - ELSE - EXECUTE 'INSERT INTO ' || quote_ident(atopology) - || '.node(node_id, geom) - VALUES('||nodeid||','||quote_literal(apoint::text)|| - ')'; - END IF; + ','||containingface||')'; + --raise notice 'insert: %',sql; + + EXECUTE sql; RETURN nodeid; +EXCEPTION + WHEN INVALID_SCHEMA_NAME THEN + RAISE EXCEPTION 'SQL/MM Spatial exception - invalid topology name'; END $$ LANGUAGE 'plpgsql' VOLATILE; diff --git a/topology/test/Makefile b/topology/test/Makefile index 1779ab394..b2cdb72a8 100644 --- a/topology/test/Makefile +++ b/topology/test/Makefile @@ -29,6 +29,7 @@ TESTS = regress/legacy_validate.sql regress/legacy_predicate.sql \ regress/addface2.5d.sql \ regress/polygonize.sql \ regress/st_addisoedge.sql \ + regress/st_addisonode.sql \ regress/st_addedgenewfaces.sql \ regress/st_getfacegeometry.sql \ regress/st_getfaceedges.sql \ diff --git a/topology/test/regress/sqlmm_expected b/topology/test/regress/sqlmm_expected index a3472bc7f..6c13ed379 100644 --- a/topology/test/regress/sqlmm_expected +++ b/topology/test/regress/sqlmm_expected @@ -13,7 +13,7 @@ ERROR: SQL/MM Spatial exception - coincident node ERROR: SQL/MM Spatial exception - coincident node 7 8 -ERROR: SQL/MM Spatial exception - non-existent face +ERROR: SQL/MM Spatial exception - not within face ERROR: SQL/MM Spatial exception - invalid point ERROR: SQL/MM Spatial exception - coincident node ERROR: SQL/MM Spatial exception - coincident node diff --git a/topology/test/regress/st_addisonode.sql b/topology/test/regress/st_addisonode.sql new file mode 100644 index 000000000..f15054c16 --- /dev/null +++ b/topology/test/regress/st_addisonode.sql @@ -0,0 +1,75 @@ +set client_min_messages to WARNING; + +select topology.CreateTopology('tt', -1) > 0; + +select 'ST_AddIsoNode: test NULL exceptions'; +-- +select topology.ST_AddIsoNode(NULL, 0, 'POINT(1 4)'); +select topology.ST_AddIsoNode('tt', 0, NULL); +select topology.ST_AddIsoNode('tt', NULL, NULL); +select topology.ST_AddIsoNode(NULL, NULL, NULL); +-- +select 'ST_AddIsoNode: test wrong topology name'; +-- +select topology.ST_AddIsoNode('wrong_name', 0, 'POINT(1 4)'); +-- +select 'ST_AddIsoNode: test negative idface'; +-- +select topology.ST_AddIsoNode('tt', -1, 'POINT(1 4)'); +-- +select 'ST_AddIsoNode: test wrong idface'; +-- +select topology.ST_AddIsoNode('tt', 1, 'POINT(1 4)'); +-- +select 'ST_AddIsoNode: test smart creation ISO Node (without know idface)'; +-- +select topology.ST_AddIsoNode('tt', NULL, 'POINT(1 4)'); +-- +select 'ST_AddIsoNode: test coincident nodes'; +-- +select topology.ST_AddIsoNode('tt', 0, 'POINT(1 4)'); +select topology.ST_AddIsoNode('tt', NULL, 'POINT(1 4)'); +select topology.ST_AddIsoNode('tt', 1, 'POINT(1 4)'); +-- +select 'ST_AddIsoNode: test add a node in UniverseFace'; +-- +select topology.ST_AddIsoNode('tt', 0, 'POINT(2 2)'); +-- +select 'ST_AddIsoNode - prepare to test the creation inside a face'; +-- +select topology.DropTopology('tt'); +select topology.CreateTopology('tt', -1) > 0; +select topology.ST_AddIsoNode('tt', NULL, 'POINT(1 1)'); +select topology.ST_AddIsoNode('tt', NULL, 'POINT(5 2)'); +select topology.ST_AddIsoNode('tt', NULL, 'POINT(4 6)'); +select topology.ST_AddIsoNode('tt', NULL, 'POINT(0 4)'); +select topology.ST_AddIsoNode('tt', NULL, 'POINT(2 2)'); +select topology.ST_AddIsoNode('tt', NULL, 'POINT(4 3)'); +select topology.ST_AddIsoNode('tt', NULL, 'POINT(3 5)'); +select topology.ST_AddIsoNode('tt', NULL, 'POINT(1 3)'); +select count(*) from tt.node where containing_face<>0; +select count(*) from tt.node where containing_face is null; +INSERT INTO tt.face (face_id, mbr) VALUES (2, '010300000001000000050000000000000000000000000000000000F03F00000000000000000000000000001840000000000000144000000000000018400000000000001440000000000000F03F0000000000000000000000000000F03F'); +INSERT INTO tt.face (face_id, mbr) VALUES (3, '01030000000100000005000000000000000000F03F0000000000000040000000000000F03F00000000000014400000000000001040000000000000144000000000000010400000000000000040000000000000F03F0000000000000040'); +BEGIN; +INSERT INTO tt.edge_data (edge_id, start_node, end_node, next_left_edge, abs_next_left_edge, next_right_edge, abs_next_right_edge, left_face, right_face, geom) VALUES (1, 1, 2, 2, 2, -4, 4, 2, 0, '010200000002000000000000000000F03F000000000000F03F00000000000014400000000000000040'); +INSERT INTO tt.edge_data (edge_id, start_node, end_node, next_left_edge, abs_next_left_edge, next_right_edge, abs_next_right_edge, left_face, right_face, geom) VALUES (2, 2, 3, 3, 3, -1, 1, 2, 0, '0102000000020000000000000000001440000000000000004000000000000010400000000000001840'); +INSERT INTO tt.edge_data (edge_id, start_node, end_node, next_left_edge, abs_next_left_edge, next_right_edge, abs_next_right_edge, left_face, right_face, geom) VALUES (3, 3, 4, 4, 4, -2, 2, 2, 0, '0102000000020000000000000000001040000000000000184000000000000000000000000000001040'); +INSERT INTO tt.edge_data (edge_id, start_node, end_node, next_left_edge, abs_next_left_edge, next_right_edge, abs_next_right_edge, left_face, right_face, geom) VALUES (4, 4, 1, 1, 1, -3, 3, 2, 0, '01020000000200000000000000000000000000000000001040000000000000F03F000000000000F03F'); +INSERT INTO tt.edge_data (edge_id, start_node, end_node, next_left_edge, abs_next_left_edge, next_right_edge, abs_next_right_edge, left_face, right_face, geom) VALUES (5, 5, 6, 6, 6, -8, 8, 3, 2, '0102000000020000000000000000000040000000000000004000000000000010400000000000000840'); +INSERT INTO tt.edge_data (edge_id, start_node, end_node, next_left_edge, abs_next_left_edge, next_right_edge, abs_next_right_edge, left_face, right_face, geom) VALUES (6, 6, 7, 7, 7, -5, 5, 3, 2, '0102000000020000000000000000001040000000000000084000000000000008400000000000001440'); +INSERT INTO tt.edge_data (edge_id, start_node, end_node, next_left_edge, abs_next_left_edge, next_right_edge, abs_next_right_edge, left_face, right_face, geom) VALUES (7, 7, 8, 8, 8, -6, 6, 3, 2, '01020000000200000000000000000008400000000000001440000000000000F03F0000000000000840'); +INSERT INTO tt.edge_data (edge_id, start_node, end_node, next_left_edge, abs_next_left_edge, next_right_edge, abs_next_right_edge, left_face, right_face, geom) VALUES (8, 8, 5, 5, 5, -7, 7, 3, 2, '010200000002000000000000000000F03F000000000000084000000000000000400000000000000040'); +END; +-- +select 'ST_AddIsoNode: test a node inside a hole'; +-- +select topology.ST_AddIsoNode('tt', NULL, 'POINT(3 3)'); +select count(*) from tt.node where node_id=9 AND containing_face=3; +-- +select 'ST_AddIsoNode: test a node inside a face'; +-- +select topology.ST_AddIsoNode('tt', NULL, 'POINT(4 5)'); +select count(*) from tt.node where node_id=10 AND containing_face=2; +-- +select topology.DropTopology('tt'); diff --git a/topology/test/regress/st_addisonode_expected b/topology/test/regress/st_addisonode_expected new file mode 100644 index 000000000..ba6d65bd2 --- /dev/null +++ b/topology/test/regress/st_addisonode_expected @@ -0,0 +1,42 @@ +t +ST_AddIsoNode: test NULL exceptions +ERROR: SQL/MM Spatial exception - null argument +ERROR: SQL/MM Spatial exception - null argument +ERROR: SQL/MM Spatial exception - null argument +ERROR: SQL/MM Spatial exception - null argument +ST_AddIsoNode: test wrong topology name +ERROR: SQL/MM Spatial exception - invalid topology name +ST_AddIsoNode: test negative idface +ERROR: SQL/MM Spatial exception - not within face +ST_AddIsoNode: test wrong idface +ERROR: SQL/MM Spatial exception - not within face +ST_AddIsoNode: test smart creation ISO Node (without know idface) +1 +ST_AddIsoNode: test coincident nodes +ERROR: SQL/MM Spatial exception - coincident node +ERROR: SQL/MM Spatial exception - coincident node +ERROR: SQL/MM Spatial exception - coincident node +ST_AddIsoNode: test add a node in UniverseFace +2 +ST_AddIsoNode - prepare to test the creation inside a face +Topology 'tt' dropped +t +1 +2 +3 +4 +5 +6 +7 +8 +0 +0 +BEGIN +COMMIT +ST_AddIsoNode: test a node inside a hole +9 +1 +ST_AddIsoNode: test a node inside a face +10 +1 +Topology 'tt' dropped -- 2.50.1