1.12 Topology Examples (PL/SQL)
This topic presents simplified PL/SQL examples that perform Topology Data Model operations.
The examples refer to concepts that are explained in this chapter. They use SDO_TOPO and SDO_TOPO_MAP functions and procedures, which are documented in SDO_TOPO Package Subprograms and SDO_TOPO_MAP Package Subprograms, and the SDO_ANYINTERACT topology operator (see Topology Operators).
Both examples are based on the "city data" topology shown in Figure 1-1 in Topology Data Model Concepts, and the features shown in Figure 1-3 in Features. However, the topologies created are not identical, because the topology built from Spatial geometries (Example 1-13) does not contain all the edges, nodes, and faces that are defined for the topology build from topology data (Example 1-12).
Parent topic: Topology Data Model Overview
1.12.1 Topology Built from Topology Data
Example 1-12 uses a topology built from edge, node, and face data.
Example 1-12 Topology Built from Topology Data
------------------------------ -- Main steps for using the Topology Data Model with a topology -- built from edge, node, and face data ------------------------------ -- 1. Create a topology. -- 2. Load (normally bulk-load) topology data (node, edge, and face tables). -- 3. Create feature tables. -- 4. Associate feature tables with the topology. -- 5. Initialize topology metadata. -- 6. Load feature tables using the SDO_TOPO_GEOMETRY constructor. -- 7. Query the data. -- 8. Optionally, edit data using the PL/SQL or Java API. -- 1. Create the topology. (Null SRID in this example.) EXECUTE SDO_TOPO.CREATE_TOPOLOGY('CITY_DATA', 0.00005); -- 2. Load topology data (node, edge, and face tables). -- Use INSERT statements here instead of a bulk-load utility. -- 2A. Insert data into <topology_name>_EDGE$ table. -- E1 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(1, 1, 1, 1, 1, -1, -1, 1, -1, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(8,30, 16,30, 16,38, 3,38, 3,30, 8,30))); -- E2 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(2, 2, 2, 3, -3, -2, -2, 2, -1, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(25,30, 31,30, 31,40, 17,40, 17,30, 25,30))); -- E3 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(3, 2, 3, -3, 2, 2, 3, 2, 2, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(25,30, 25,35))); -- E4 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(4, 5, 6, -5, -4, 4, 5, -1, -1, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(36,38, 38,35, 41,34, 42,33, 45,32, 47,28, 50,28, 52,32, 57,33))); -- E5 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(5, 7, 6, -4, -5, 5, 4, -1, -1, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(41,40, 45,40, 47,42, 62,41, 61,38, 59,39, 57,36, 57,33))); -- E6 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(6, 16, 17, 7, 21, -21, 19, -1, 3, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(9,22, 21,22))); -- E7 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(7, 17, 18, 8, 6, -19, 17, -1, 4, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(21,22, 35,22))); -- E8 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(8, 18, 19, -15, 7, -17, 15, -1, 5, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(35,22, 47,22))); -- E9 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(9, 15, 14, 19, -21, -22, 20, 3, 6, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(9,14, 21,14))); -- E10 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(10, 13, 14, -20, 18, 17, -19, 7, 4, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(35,14, 21,14))); -- E11 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(11, 13, 12, 15, -17, -18, 16, 5, 8, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(35,14, 47,14))); -- E12 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(12, 8, 9, 20, -22, 22, -13, 6, -1, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(9,6, 21,6))); -- E13 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(13, 9, 10, 18, -20, -12, -14, 7, -1, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(21,6, 35,6))); -- E14 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(14, 10, 11, 16, -18, -13, -16, 8, -1, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(35,6, 47,6))); -- E15 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(15, 12, 19, -8, 11, -16, 8, 5, -1, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(47,14, 47,22))); -- E16 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(16, 11, 12, -11, 14, -14, -15, 8, -1, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(47,6, 47,14))); -- E17 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(17, 13, 18, -7, -10, 11, -8, 4, 5, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(35,14, 35,22))); -- E18 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(18, 10, 13, 10, 13, 14, -11, 7, 8, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(35,6, 35,14))); -- E19 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(19, 14, 17, -6, 9, -10, -7, 3, 4, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(21,14, 21,22))); -- E20 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(20, 9, 14, -9, 12, 13, 10, 6, 7, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(21,6, 21,14))); -- E21 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(21, 15, 16, 6, 22, 9, -6, -1, 3, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(9,14, 9,22))); -- E22 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(22, 8, 15, 21, -12, 12, -9, -1, 6, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(9,6, 9,14))); -- E25 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(25, 21, 22, -25, -25, 25, 25, 1, 1, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(9,35, 13,35))); -- E26 INSERT INTO city_data_edge$ (edge_id, start_node_id, end_node_id, next_left_edge_id, prev_left_edge_id, next_right_edge_id, prev_right_edge_id, left_face_id, right_face_id, geometry) VALUES(26, 20, 20, 26, 26, -26, -26, 9, 1, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(4,31, 7,31, 7,34, 4,34, 4,31))); -- 2B. Insert data into <topology_name>_NODE$ table. -- N1 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(1, 1, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(8,30,NULL), NULL, NULL)); -- N2 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(2, 2, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(25,30,NULL), NULL, NULL)); -- N3 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(3, -3, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(25,35,NULL), NULL, NULL)); -- N4 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(4, NULL, 2, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(20,37,NULL), NULL, NULL)); -- N5 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(5, 4, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(36,38,NULL), NULL, NULL)); -- N6 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(6, -4, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(57,33,NULL), NULL, NULL)); -- N7 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(7, 5, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(41,40,NULL), NULL, NULL)); -- N8 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(8, 12, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(9,6,NULL), NULL, NULL)); -- N9 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(9, 20, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(21,6,NULL), NULL, NULL)); -- N10 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(10, 18, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(35,6,NULL), NULL, NULL)); -- N11 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(11, -14, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(47,6,NULL), NULL, NULL)); -- N12 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(12, 15, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(47,14,NULL), NULL, NULL)); -- N13 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(13, 17, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(35,14,NULL), NULL, NULL)); -- N14 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(14, 19, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(21,14,NULL), NULL, NULL)); -- N15 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(15, 21, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(9,14,NULL), NULL, NULL)); -- N16 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(16, 6, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(9,22,NULL), NULL, NULL)); -- N17 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(17, 7, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(21,22,NULL), NULL, NULL)); -- N18 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(18, 8, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(35,22,NULL), NULL, NULL)); -- N19 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(19, -15, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(47,22,NULL), NULL, NULL)); -- N20 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(20, 26, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(4,31,NULL), NULL, NULL)); -- N21 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(21, 25, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(9,35,NULL), NULL, NULL)); -- N22 INSERT INTO city_data_node$ (node_id, edge_id, face_id, geometry) VALUES(22, -25, NULL, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(13,35,NULL), NULL, NULL)); -- 2C. Insert data into <topology_name>_FACE$ table. -- F0 (id = -1, not 0) INSERT INTO city_data_face$ (face_id, boundary_edge_id, island_edge_id_list, island_node_id_list, mbr_geometry) VALUES(-1, NULL, SDO_LIST_TYPE(-1, -2, 4, 6), SDO_LIST_TYPE(), NULL); -- F1 INSERT INTO city_data_face$ (face_id, boundary_edge_id, island_edge_id_list, island_node_id_list, mbr_geometry) VALUES(1, 1, SDO_LIST_TYPE(25, -26), SDO_LIST_TYPE(), SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(3,30, 15,38))); -- F2 INSERT INTO city_data_face$ (face_id, boundary_edge_id, island_edge_id_list, island_node_id_list, mbr_geometry) VALUES(2, 2, SDO_LIST_TYPE(), SDO_LIST_TYPE(4), SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(17,30, 31,40))); -- F3 INSERT INTO city_data_face$ (face_id, boundary_edge_id, island_edge_id_list, island_node_id_list, mbr_geometry) VALUES(3, 19, SDO_LIST_TYPE(), SDO_LIST_TYPE(), SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(9,14, 21,22))); -- F4 INSERT INTO city_data_face$ (face_id, boundary_edge_id, island_edge_id_list, island_node_id_list, mbr_geometry) VALUES(4, 17, SDO_LIST_TYPE(), SDO_LIST_TYPE(), SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(21,14, 35,22))); -- F5 INSERT INTO city_data_face$ (face_id, boundary_edge_id, island_edge_id_list, island_node_id_list, mbr_geometry) VALUES(5, 15, SDO_LIST_TYPE(), SDO_LIST_TYPE(), SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(35,14, 47,22))); -- F6 INSERT INTO city_data_face$ (face_id, boundary_edge_id, island_edge_id_list, island_node_id_list, mbr_geometry) VALUES(6, 20, SDO_LIST_TYPE(), SDO_LIST_TYPE(), SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(9,6, 21,14))); -- F7 INSERT INTO city_data_face$ (face_id, boundary_edge_id, island_edge_id_list, island_node_id_list, mbr_geometry) VALUES(7, 10, SDO_LIST_TYPE(), SDO_LIST_TYPE(), SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(21,6, 35,14))); -- F8 INSERT INTO city_data_face$ (face_id, boundary_edge_id, island_edge_id_list, island_node_id_list, mbr_geometry) VALUES(8, 16, SDO_LIST_TYPE(), SDO_LIST_TYPE(), SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(35,6, 47,14))); -- F9 INSERT INTO city_data_face$ (face_id, boundary_edge_id, island_edge_id_list, island_node_id_list, mbr_geometry) VALUES(9,26,SDO_LIST_TYPE(), SDO_LIST_TYPE(), SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(4,31, 7,34))); -- 3. Create feature tables. CREATE TABLE land_parcels ( -- Land parcels (selected faces) feature_name VARCHAR2(30) PRIMARY KEY, feature SDO_TOPO_GEOMETRY); CREATE TABLE city_streets ( -- City streets (selected edges) feature_name VARCHAR2(30) PRIMARY KEY, feature SDO_TOPO_GEOMETRY); CREATE TABLE traffic_signs ( -- Traffic signs (selected nodes) feature_name VARCHAR2(30) PRIMARY KEY, feature SDO_TOPO_GEOMETRY); -- 4. Associate feature tables with the topology. -- Add the three topology geometry layers to the CITY_DATA topology. -- Any order is OK. EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('CITY_DATA', 'LAND_PARCELS','FEATURE', 'POLYGON'); EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('CITY_DATA', 'TRAFFIC_SIGNS','FEATURE', 'POINT'); EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('CITY_DATA', 'CITY_STREETS', 'FEATURE','LINE'); -- As a result, Spatial generates a unique TG_LAYER_ID for each layer in -- the topology metadata (USER/ALL_SDO_TOPO_METADATA). -- 5. Initialize topology metadata. EXECUTE SDO_TOPO.INITIALIZE_METADATA('CITY_DATA'); -- 6. Load feature tables using the SDO_TOPO_GEOMETRY constructor. -- Each topology feature can consist of one or more objects (face, edge, node) -- of an appropriate type. For example, a land parcel can consist of one face, -- or two or more faces, as specified in the SDO_TOPO_OBJECT_ARRAY. -- There are typically fewer features than there are faces, nodes, and edges. -- In this example, the only features are these: -- Area features (land parcels): P1, P2, P3, P4, P5 -- Point features (traffic signs): S1, S2, S3, S4 -- Linear features (roads/streets): R1, R2, R3, R4 -- 6A. Load LAND_PARCELS table. -- P1 INSERT INTO land_parcels VALUES ('P1', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 3, -- Topology geometry type (polygon/multipolygon) 1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (3, 3), -- face_id = 3 SDO_TOPO_OBJECT (6, 3))) -- face_id = 6 ); -- P2 INSERT INTO land_parcels VALUES ('P2', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 3, -- Topology geometry type (polygon/multipolygon) 1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (4, 3), -- face_id = 4 SDO_TOPO_OBJECT (7, 3))) -- face_id = 7 ); -- P3 INSERT INTO land_parcels VALUES ('P3', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 3, -- Topology geometry type (polygon/multipolygon) 1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (5, 3), -- face_id = 5 SDO_TOPO_OBJECT (8, 3))) -- face_id = 8 ); -- P4 INSERT INTO land_parcels VALUES ('P4', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 3, -- Topology geometry type (polygon/multipolygon) 1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (2, 3))) -- face_id = 2 ); -- P5 (Includes F1, but not F9.) INSERT INTO land_parcels VALUES ('P5', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 3, -- Topology geometry type (polygon/multipolygon) 1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (1, 3))) -- face_id = 1 ); -- 6B. Load TRAFFIC_SIGNS table. -- S1 INSERT INTO traffic_signs VALUES ('S1', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 1, -- Topology geometry type (point) 2, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (14, 1))) -- node_id = 14 ); -- S2 INSERT INTO traffic_signs VALUES ('S2', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 1, -- Topology geometry type (point) 2, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (13, 1))) -- node_id = 13 ); -- S3 INSERT INTO traffic_signs VALUES ('S3', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 1, -- Topology geometry type (point) 2, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (6, 1))) -- node_id = 6 ); -- S4 INSERT INTO traffic_signs VALUES ('S4', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 1, -- Topology geometry type (point) 2, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (4, 1))) -- node_id = 4 ); -- 6C. Load CITY_STREETS table. -- (Note: "R" in feature names is for "Road", because "S" is used for signs.) -- R1 INSERT INTO city_streets VALUES ('R1', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 2, -- Topology geometry type (line string) 3, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (9, 2), SDO_TOPO_OBJECT (-10, 2), SDO_TOPO_OBJECT (11, 2))) -- edge_ids = 9, -10, 11 ); -- R2 INSERT INTO city_streets VALUES ('R2', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 2, -- Topology geometry type (line string) 3, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (4, 2), SDO_TOPO_OBJECT (-5, 2))) -- edge_ids = 4, -5 ); -- R3 INSERT INTO city_streets VALUES ('R3', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 2, -- Topology geometry type (line string) 3, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (25, 2))) -- edge_id = 25 ); -- R4 INSERT INTO city_streets VALUES ('R4', -- Feature name SDO_TOPO_GEOMETRY( 'CITY_DATA', -- Topology name 2, -- Topology geometry type (line string) 3, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) SDO_TOPO_OBJECT_ARRAY ( SDO_TOPO_OBJECT (3, 2))) -- edge_id = 3 ); -- 7. Query the data. SELECT a.feature_name, a.feature.tg_id, a.feature.get_geometry() FROM land_parcels a; /* Window is city_streets */ SELECT a.feature_name, b.feature_name FROM city_streets b, land_parcels a WHERE b.feature_name like 'R%' AND sdo_anyinteract(a.feature, b.feature) = 'TRUE' ORDER BY b.feature_name, a.feature_name; -- Find all streets that have any interaction with land parcel P3. -- (Should return only R1.) SELECT c.feature_name FROM city_streets c, land_parcels l WHERE l.feature_name = 'P3' AND SDO_ANYINTERACT (c.feature, l.feature) = 'TRUE'; -- Find all land parcels that have any interaction with traffic sign S1. -- (Should return P1 and P2.) SELECT l.feature_name FROM land_parcels l, traffic_signs t WHERE t.feature_name = 'S1' AND SDO_ANYINTERACT (l.feature, t.feature) = 'TRUE'; -- Get the geometry for land parcel P1. SELECT l.feature_name, l.feature.get_geometry() FROM land_parcels l WHERE l.feature_name = 'P1'; -- Get the boundary of face with face_id 3. SELECT SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA', 3) FROM DUAL; -- Get the topological elements for land parcel P2. -- CITY_DATA layer, land parcels (tg_ layer_id = 1), parcel P2 (tg_id = 2) SELECT SDO_TOPO.GET_TOPO_OBJECTS('CITY_DATA', 1, 2) FROM DUAL;
Parent topic: Topology Examples (PL/SQL)
1.12.2 Topology Built from Spatial Geometries
Example 1-13 uses a topology built from Oracle Spatial geometry data.
Example 1-13 Topology Built from Spatial Geometries
------------------------------ -- Main steps for using the Topology Data Model with a topology -- built from Spatial geometry data ------------------------------ -- 1. Create the topology. -- 2. Insert the universe face (F0). (id = -1, not 0) -- 3. Create feature tables. -- 4. Associate feature tables with the topology. -- 5. Initialize topology metadata. -- 6. Create a TopoMap object and load the whole topology into -- cache for updating. -- 7. Load feature tables, inserting data from the spatial tables and -- using SDO_TOPO_MAP.CREATE_FEATURE. -- 8. Query the data. -- 9. Optionally, edit the data using the PL/SQL or Java API. -- Preliminary work for this example (things normally done to use -- data with Oracle Spatial): -- * Create the spatial tables. -- * Update the spatial metadata (USER_SDO_GEOM_METADATA). -- * Load data into the spatial tables. -- * Validate the spatial data (validate the layers). -- * Create the spatial indexes. -- Create spatial tables of geometry features: names and geometries. CREATE TABLE city_streets_geom ( -- City streets/roads name VARCHAR2(30) PRIMARY KEY, geometry SDO_GEOMETRY); CREATE TABLE traffic_signs_geom ( -- Traffic signs name VARCHAR2(30) PRIMARY KEY, geometry SDO_GEOMETRY); CREATE TABLE land_parcels_geom ( -- Land parcels name VARCHAR2(30) PRIMARY KEY, geometry SDO_GEOMETRY); INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( 'CITY_STREETS_GEOM', 'GEOMETRY', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 0, 65, 0.005), SDO_DIM_ELEMENT('Y', 0, 45, 0.005) ), NULL -- SRID ); INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( 'TRAFFIC_SIGNS_GEOM', 'GEOMETRY', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 0, 65, 0.005), SDO_DIM_ELEMENT('Y', 0, 45, 0.005) ), NULL -- SRID ); INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( 'LAND_PARCELS_GEOM', 'GEOMETRY', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 0, 65, 0.005), SDO_DIM_ELEMENT('Y', 0, 45, 0.005) ), NULL -- SRID ); -- Load these tables (names and geometries for city streets/roads, -- traffic signs, and land parcels). -- Insert data into city street line geometries. -- R1 INSERT INTO city_streets_geom VALUES('R1', SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(9,14, 21,14, 35,14, 47,14))); -- R2 INSERT INTO city_streets_geom VALUES('R2', SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(36,38, 38,35, 41,34, 42,33, 45,32, 47,28, 50,28, 52,32, 57,33, 57,36, 59,39, 61,38, 62,41, 47,42, 45,40, 41,40))); -- R3 INSERT INTO city_streets_geom VALUES('R3', SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(9,35, 13,35))); -- R4 INSERT INTO city_streets_geom VALUES('R4', SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(25,30, 25,35))); -- Insert data into traffic sign point geometries. -- S1 INSERT INTO traffic_signs_geom VALUES('S1', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(21,14,NULL), NULL, NULL)); -- S2 INSERT INTO traffic_signs_geom VALUES('S2', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(35,14,NULL), NULL, NULL)); -- S3 INSERT INTO traffic_signs_geom VALUES('S3', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(57,33,NULL), NULL, NULL)); -- S4 INSERT INTO traffic_signs_geom VALUES('S4', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(20,37,NULL), NULL, NULL)); -- Insert data into land parcel polygon geometries. -- P1 INSERT INTO land_parcels_geom VALUES('P1', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(9,6, 21,6, 21,14, 21,22, 9,22, 9,14, 9,6))); -- P2 INSERT INTO land_parcels_geom VALUES('P2', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(21,6, 35,6, 35,14, 35,22, 21,22, 21,14, 21,6))); -- P3 INSERT INTO land_parcels_geom VALUES('P3', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(35,6, 47,6, 47,14, 47,22, 35,22, 35,14, 35,6))); -- P4 INSERT INTO land_parcels_geom VALUES('P4', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(17,30, 31,30, 31,40, 17,40, 17,30))); -- P5 (polygon with a hole; exterior ring and one interior ring) INSERT INTO land_parcels_geom VALUES('P5', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1, 11,2003,1), SDO_ORDINATE_ARRAY(3,30, 16,30, 16,38, 3,38, 3,30, 4,31, 4,34, 7,34, 7,31, 4,31))); -- Validate the layers. create table val_results (sdo_rowid ROWID, result VARCHAR2(2000)); call SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('CITY_STREETS_GEOM','GEOMETRY','VAL_RESULTS'); SELECT * from val_results; truncate table val_results; call SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('TRAFFIC_SIGNS_GEOM','GEOMETRY','VAL_RESULTS'); SELECT * from val_results; truncate table val_results; call SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('LAND_PARCELS_GEOM','GEOMETRY','VAL_RESULTS'); SELECT * from val_results; drop table val_results; -- Create the spatial indexes. CREATE INDEX city_streets_geom_idx ON city_streets_geom(geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX; CREATE INDEX traffic_signs_geom_idx ON traffic_signs_geom(geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX; CREATE INDEX land_parcels_geom_idx ON land_parcels_geom(geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX; -- Start the main steps for using the Topology Data Model with a -- topology built from spatial geometry data. -- 1. Create the topology. (Null SRID in this example.) EXECUTE SDO_TOPO.CREATE_TOPOLOGY('CITY_DATA', 0.00005); -- 2. Insert the universe face (F0). (id = -1, not 0) INSERT INTO CITY_DATA_FACE$ values ( -1, NULL, SDO_LIST_TYPE(), SDO_LIST_TYPE(), NULL); COMMIT; -- 3. Create feature tables. CREATE TABLE city_streets ( -- City streets/roads feature_name VARCHAR2(30) PRIMARY KEY, feature SDO_TOPO_GEOMETRY); CREATE TABLE traffic_signs ( -- Traffic signs feature_name VARCHAR2(30) PRIMARY KEY, feature SDO_TOPO_GEOMETRY); CREATE TABLE land_parcels ( -- Land parcels feature_name VARCHAR2(30) PRIMARY KEY, feature SDO_TOPO_GEOMETRY); -- 4. Associate feature tables with the topology. -- Add the three topology geometry layers to the CITY_DATA topology. -- Any order is OK. EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('CITY_DATA', 'CITY_STREETS', 'FEATURE','LINE'); EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('CITY_DATA', 'TRAFFIC_SIGNS','FEATURE', 'POINT'); EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('CITY_DATA', 'LAND_PARCELS','FEATURE', 'POLYGON'); -- As a result, Spatial generates a unique TG_LAYER_ID for each layer in -- the topology metadata (USER/ALL_SDO_TOPO_METADATA). -- 5. Initialize topology metadata. EXECUTE SDO_TOPO.INITIALIZE_METADATA('CITY_DATA'); -- 6. Create a TopoMap object and load the whole topology into cache for updating. EXECUTE SDO_TOPO_MAP.CREATE_TOPO_MAP('CITY_DATA', 'CITY_DATA_TOPOMAP'); EXECUTE SDO_TOPO_MAP.LOAD_TOPO_MAP('CITY_DATA_TOPOMAP', 'true'); -- 7. Load feature tables, inserting data from the spatial tables and -- using SDO_TOPO_MAP.CREATE_FEATURE. BEGIN FOR street_rec IN (SELECT name, geometry FROM city_streets_geom) LOOP INSERT INTO city_streets VALUES(street_rec.name, SDO_TOPO_MAP.CREATE_FEATURE('CITY_DATA', 'CITY_STREETS', 'FEATURE', street_rec.geometry)); END LOOP; FOR sign_rec IN (SELECT name, geometry FROM traffic_signs_geom) LOOP INSERT INTO traffic_signs VALUES(sign_rec.name, SDO_TOPO_MAP.CREATE_FEATURE('CITY_DATA', 'TRAFFIC_SIGNS', 'FEATURE', sign_rec.geometry)); END LOOP; FOR parcel_rec IN (SELECT name, geometry FROM land_parcels_geom) LOOP INSERT INTO land_parcels VALUES(parcel_rec.name, SDO_TOPO_MAP.CREATE_FEATURE('CITY_DATA', 'LAND_PARCELS', 'FEATURE', parcel_rec.geometry)); END LOOP; END; / CALL SDO_TOPO_MAP.COMMIT_TOPO_MAP(); CALL SDO_TOPO_MAP.DROP_TOPO_MAP('CITY_DATA_TOPOMAP'); -- 8. Query the data. SELECT a.feature_name, a.feature.tg_id, a.feature.get_geometry() FROM land_parcels a; SELECT a.feature_name, a.feature.tg_id, a.feature.get_geometry() FROM city_streets a; SELECT a.feature_name, a.feature.tg_id, a.feature.get_geometry() FROM traffic_signs a; SELECT sdo_topo.get_face_boundary('CITY_DATA', face_id), face_id FROM city_data_face$; SELECT sdo_topo.get_face_boundary('CITY_DATA', face_id), face_id FROM city_data_face$; SELECT sdo_topo.get_face_boundary('CITY_DATA', face_id, 'TRUE'), face_id FROM city_data_face$; -- Get topological elements. SELECT a.FEATURE_NAME, sdo_topo.get_topo_objects('CITY_DATA', a.feature.TG_LAYER_ID, a.feature.TG_ID) FROM land_parcels a; SELECT a.FEATURE_NAME, sdo_topo.get_topo_objects('CITY_DATA', a.feature.TG_LAYER_ID, a.feature.TG_ID) FROM city_streets a; SELECT a.FEATURE_NAME, sdo_topo.get_topo_objects('CITY_DATA', a.feature.TG_LAYER_ID, a.feature.TG_ID) FROM traffic_signs a; SELECT sdo_topo.get_topo_objects('CITY_DATA', sdo_geometry(2003,null, null, sdo_elem_info_array(1,1003,3), sdo_ordinate_array(1,1, 20,20))) FROM DUAL; SELECT sdo_topo.get_topo_objects('CITY_DATA', sdo_geometry(2003,null, null, sdo_elem_info_array(1,1003,3), sdo_ordinate_array(17,30, 31,40))) FROM DUAL; -- Find all city streets interacting with a query window. SELECT c.feature_name FROM city_streets c WHERE SDO_ANYINTERACT( c.feature, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(5,5, 30,40))) = 'TRUE'; -- Find all streets that have any interaction with land parcel P3. -- (Should return only R1.) SELECT c.feature_name FROM city_streets c, land_parcels l WHERE l.feature_name = 'P3' AND SDO_ANYINTERACT (c.feature, l.feature) = 'TRUE'; -- Find all land parcels that have any interaction with traffic sign S1. -- (Should return P1 and P2.) SELECT l.feature_name FROM land_parcels l, traffic_signs t WHERE t.feature_name = 'S1' AND SDO_ANYINTERACT (l.feature, t.feature) = 'TRUE'; -- Get the geometry for land parcel P1. SELECT l.feature_name, l.feature.get_geometry() FROM land_parcels l WHERE l.feature_name = 'P1'; -- Query SDO_TOPO_GEOMETRY attributes, SELECT s.feature.tg_type FROM city_streets s; SELECT s.feature.tg_id FROM city_streets s; SELECT s.feature.tg_layer_id FROM city_streets s; SELECT s.feature.topology_id FROM city_streets s; -- Topology-specific functions -- Get the boundary of face with face_id 3. SELECT SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA', 3) FROM DUAL; -- Try 'TRUE' as third parameter. SELECT SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA', 3, 'TRUE') FROM DUAL; -- Get the boundary of face with face_id 2. SELECT SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA', 2) FROM DUAL; -- Try 'TRUE' as third parameter. SELECT SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA', 2, 'TRUE') FROM DUAL; -- Get the boundary of face with face_id 1. SELECT SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA', 1) FROM DUAL; -- Specify 'TRUE' for the all_edges parameter. SELECT SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA', 1, 'TRUE') FROM DUAL; -- CITY_DATA layer, land parcels (tg_ layer_id = 1), parcel P2 (tg_id = 2) SELECT SDO_TOPO.GET_TOPO_OBJECTS('CITY_DATA', 1, 2) FROM DUAL; -- 10. Optionally, edit the data using the PL/SQL or Java API.
Parent topic: Topology Examples (PL/SQL)