1-- 2-- From examples in chapter 1.12.1 of 3-- "Spatial Topology and Network Data Models" (Oracle manual) 4-- 5-- Modified to use postgis-based topology model. 6-- Loads the whole topology represented in Figure 1-1 of the 7-- manual, creates TopoGeometry objects and associations. 8-- 9 10--ORA-------------------------------- 11--ORA---- Main steps for using the topology data model with a topology 12--ORA---- built from edge, node, and face data 13--ORA-------------------------------- 14--ORA---- ... 15--ORA---- 3. Create feature tables. 16--ORA---- 4. Associate feature tables with the topology. 17--ORA---- 5. Initialize topology 18--ORA---- 6. Load feature tables using the SDO_TOPO_GEOMETRY constructor. 19 20BEGIN; 21 22-- 3. Create feature tables 23 24CREATE SCHEMA features; 25 26CREATE TABLE features.land_parcels ( -- Land parcels (selected faces) 27 feature_name VARCHAR PRIMARY KEY, fid serial); 28CREATE TABLE features.city_streets ( -- City streets (selected edges) 29 feature_name VARCHAR PRIMARY KEY, fid serial); 30CREATE TABLE features.traffic_signs ( -- Traffic signs (selected nodes) 31 feature_name VARCHAR PRIMARY KEY, fid serial); 32 33-- 4. Associate feature tables with the topology. 34-- Add the three topology geometry layers to the CITY_DATA topology. 35-- Any order is OK. 36SELECT topology.AddTopoGeometryColumn('city_data', 'features', 'land_parcels', 'feature', 'POLYGON'); 37SELECT topology.AddTopoGeometryColumn('city_data', 'features', 'traffic_signs','feature', 'POINT'); 38SELECT topology.AddTopoGeometryColumn('city_data', 'features', 'city_streets','feature', 'LINE'); 39 40-- As a result, Spatial generates a unique TG_LAYER_ID for each layer in 41-- the topology metadata (USER/ALL_SDO_TOPO_METADATA). 42 43--NOTYET---- 5. Initialize topology metadata. 44--NOTYET--EXECUTE topology.INITIALIZE_METADATA('CITY_DATA'); 45 46-- 6. Load feature tables using the CreateTopoGeom constructor. 47-- Each topology feature can consist of one or more objects (face, edge, node) 48-- of an appropriate type. For example, a land parcel can consist of one face, 49-- or two or more faces, as specified in the SDO_TOPO_OBJECT_ARRAY. 50-- There are typically fewer features than there are faces, nodes, and edges. 51-- In this example, the only features are these: 52-- Area features (land parcels): P1, P2, P3, P4, P5 53-- Point features (traffic signs): S1, S2, S3, S4 54-- Linear features (roads/streets): R1, R2, R3, R4 55 56-- 6A. Load LAND_PARCELS table. 57-- P1 58INSERT INTO features.land_parcels(feature_name, feature) VALUES ('P1', -- Feature name 59 topology.CreateTopoGeom( 60 'city_data', -- Topology name 61 3, -- Topology geometry type (polygon/multipolygon) 62 1, -- TG_LAYER_ID for this topology (from topology.layer) 63 '{{3,3},{6,3}}') -- face_id:3 face_id:6 64 ); 65 66-- P2 67INSERT INTO features.land_parcels(feature_name, feature) VALUES ('P2', -- Feature name 68 topology.CreateTopoGeom( 69 'city_data', -- Topology name 70 3, -- Topology geometry type (polygon/multipolygon) 71 1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA) 72 '{{4,3},{7,3}}')); 73-- P3 74INSERT INTO features.land_parcels(feature_name, feature) VALUES ('P3', -- Feature name 75 topology.CreateTopoGeom( 76 'city_data', -- Topology name 77 3, -- Topology geometry type (polygon/multipolygon) 78 1, -- TG_LAYER_ID for this topology (from topology.layer) 79 '{{5,3},{8,3}}')); 80-- P4 81INSERT INTO features.land_parcels(feature_name, feature) VALUES ('P4', -- Feature name 82 topology.CreateTopoGeom( 83 'city_data', -- Topology name 84 3, -- Topology geometry type (polygon/multipolygon) 85 1, -- TG_LAYER_ID for this topology (from topology.layer) 86 '{{2,3}}')); 87-- P5 (Includes F1, but not F9.) 88INSERT INTO features.land_parcels(feature_name, feature) VALUES ('P5', -- Feature name 89 topology.CreateTopoGeom( 90 'city_data', -- Topology name 91 3, -- Topology geometry type (polygon/multipolygon) 92 1, -- TG_LAYER_ID for this topology (from topology.layer) 93 '{{1,3}}')); 94 95-- 6B. Load TRAFFIC_SIGNS table. 96-- S1 97INSERT INTO features.traffic_signs(feature_name, feature) VALUES ('S1', -- Feature name 98 topology.CreateTopoGeom( 99 'city_data', -- Topology name 100 1, -- Topology geometry type (point) 101 2, -- TG_LAYER_ID for this topology (from topology.layer) 102 '{{14,1}}')); 103-- S2 104INSERT INTO features.traffic_signs(feature_name, feature) VALUES ('S2', -- Feature name 105 topology.CreateTopoGeom( 106 'city_data', -- Topology name 107 1, -- Topology geometry type (point) 108 2, -- TG_LAYER_ID for this topology (from topology.layer) 109 '{{13,1}}')); 110-- S3 111INSERT INTO features.traffic_signs(feature_name, feature) VALUES ('S3', -- Feature name 112 topology.CreateTopoGeom( 113 'city_data', -- Topology name 114 1, -- Topology geometry type (point) 115 2, -- TG_LAYER_ID for this topology (from topology.layer) 116 '{{6,1}}')); 117-- S4 118INSERT INTO features.traffic_signs(feature_name, feature) VALUES ('S4', -- Feature name 119 topology.CreateTopoGeom( 120 'city_data', -- Topology name 121 1, -- Topology geometry type (point) 122 2, -- TG_LAYER_ID for this topology (from topology.layer) 123 '{{4,1}}')); 124 125-- 6C. Load CITY_STREETS table. 126-- (Note: "R" in feature names is for "Road", because "S" is used for signs.) 127-- R1 128INSERT INTO features.city_streets(feature_name, feature) VALUES ('R1', -- Feature name 129 topology.CreateTopoGeom( 130 'city_data', -- Topology name 131 2, -- Topology geometry type (line string) 132 3, -- TG_LAYER_ID for this topology (from topology.layer) 133 '{{9,2},{-10,2}}')); -- E9, E10 134-- R2 135INSERT INTO features.city_streets(feature_name, feature) VALUES ('R2', -- Feature name 136 topology.CreateTopoGeom( 137 'city_data', -- Topology name 138 2, -- Topology geometry type (line string) 139 3, -- TG_LAYER_ID for this topology (from topology.layer) 140 '{{4,2},{-5,2}}')); -- E4, E5 141-- R3 142INSERT INTO features.city_streets(feature_name, feature) VALUES ('R3', -- Feature name 143 topology.CreateTopoGeom( 144 'city_data', -- Topology name 145 2, -- Topology geometry type (line string) 146 3, -- TG_LAYER_ID for this topology (from topology.layer) 147 '{{25,2}}')); 148-- R4 149INSERT INTO features.city_streets(feature_name, feature) VALUES ('R4', -- Feature name 150 topology.CreateTopoGeom( 151 'city_data', -- Topology name 152 2, -- Topology geometry type (line string) 153 3, -- TG_LAYER_ID for this topology (from topology.layer) 154 '{{3,2}}')); 155 156END; 157 158