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