1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
|
--
-- From examples in chapter 1.12.1 of
-- "Spatial Topology and Network Data Models" (Oracle manual)
--
-- Modified to use postgis-based topology model.
-- Loads the whole topology represented in Figure 1-1 of the
-- manual
--
--ORA---------------------------------------------------------------------
--ORA---- Main steps for using the topology data model with a topology
--ORA---- built from edge, node, and face data
--ORA---------------------------------------------------------------------
--ORA---- 1. Create a topology.
--ORA---- 2. Load (normally bulk-load) topology data
--ORA---- (node, edge, and face tables).
BEGIN;
-- 1. Create the topology.
--
-- NOTE:
-- Returns topology id... which depend on how many
-- topologies where created in the regress database
-- so we just check it's a number greater than 0
--
SELECT topology.CreateTopology('city_data', @SRID@) > 0;
-- 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>.FACE table.
INSERT INTO city_data.face(face_id) VALUES(1); -- F1
INSERT INTO city_data.face(face_id) VALUES(2); -- F2
INSERT INTO city_data.face(face_id) VALUES(3); -- F3
INSERT INTO city_data.face(face_id) VALUES(4); -- F4
INSERT INTO city_data.face(face_id) VALUES(5); -- F5
INSERT INTO city_data.face(face_id) VALUES(6); -- F6
INSERT INTO city_data.face(face_id) VALUES(7); -- F7
INSERT INTO city_data.face(face_id) VALUES(8); -- F8
INSERT INTO city_data.face(face_id) VALUES(9); -- F9
-- UPDATE Face id sequence
SELECT setval('city_data.face_face_id_seq', 9);
-- 2B. Insert data into <topology_name>.NODE table.
-- N1
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(1, 'SRID=@SRID@;POINT(8 30)', NULL);
-- N2
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(2, 'SRID=@SRID@;POINT(25 30)', NULL);
-- N3
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(3, 'SRID=@SRID@;POINT(25 35)', NULL);
-- N4
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(4, 'SRID=@SRID@;POINT(20 37)', 2);
-- N5
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(5, 'SRID=@SRID@;POINT(36 38)', NULL);
-- N6
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(6, 'SRID=@SRID@;POINT(57 33)', NULL);
-- N7
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(7, 'SRID=@SRID@;POINT(41 40)', NULL);
-- N8
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(8, 'SRID=@SRID@;POINT(9 6)', NULL);
-- N9
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(9, 'SRID=@SRID@;POINT(21 6)', NULL);
-- N10
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(10, 'SRID=@SRID@;POINT(35 6)', NULL);
-- N11
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(11, 'SRID=@SRID@;POINT(47 6)', NULL);
-- N12
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(12, 'SRID=@SRID@;POINT(47 14)', NULL);
-- N13
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(13, 'SRID=@SRID@;POINT(35 14)', NULL);
-- N14
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(14, 'SRID=@SRID@;POINT(21 14)', NULL);
-- N15
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(15, 'SRID=@SRID@;POINT(9 14)', NULL);
-- N16
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(16, 'SRID=@SRID@;POINT(9 22)', NULL);
-- N17
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(17, 'SRID=@SRID@;POINT(21 22)', NULL);
-- N18
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(18, 'SRID=@SRID@;POINT(35 22)', NULL);
-- N19
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(19, 'SRID=@SRID@;POINT(47 22)', NULL);
-- N20
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(20, 'SRID=@SRID@;POINT(4 31)', NULL);
-- N21
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(21, 'SRID=@SRID@;POINT(9 35)', NULL);
-- N22
INSERT INTO city_data.node(node_id, geom, containing_face)
VALUES(22, 'SRID=@SRID@;POINT(13 35)', NULL);
-- UPDATE Node id sequence
SELECT setval('city_data.node_node_id_seq', 22);
-- 2C. Insert data into <topology_name>.EDGE table.
-- E1
INSERT INTO city_data.edge VALUES(1, 1, 1, 1, -1, 1, 0,
'SRID=@SRID@;LINESTRING(8 30, 16 30, 16 38, 3 38, 3 30, 8 30)');
-- E2
INSERT INTO city_data.edge VALUES(2, 2, 2, 3, -2, 2, 0,
'SRID=@SRID@;LINESTRING(25 30, 31 30, 31 40, 17 40, 17 30, 25 30)');
-- E3
INSERT INTO city_data.edge VALUES(3, 2, 3, -3, 2, 2, 2,
'SRID=@SRID@;LINESTRING(25 30, 25 35)');
-- E4
INSERT INTO city_data.edge VALUES(4, 5, 6, -5, 4, 0, 0,
'SRID=@SRID@;LINESTRING(36 38, 38 35, 41 34, 42 33, 45 32, 47 28, 50 28, 52 32, 57 33)');
-- E5
INSERT INTO city_data.edge VALUES(5, 7, 6, -4, 5, 0, 0,
'SRID=@SRID@;LINESTRING(41 40, 45 40, 47 42, 62 41, 61 38, 59 39, 57 36, 57 33)');
-- E6
INSERT INTO city_data.edge VALUES(6, 16, 17, 7, -21, 0, 3,
'SRID=@SRID@;LINESTRING(9 22, 21 22)');
-- E7
INSERT INTO city_data.edge VALUES(7, 17, 18, 8, -19, 0, 4,
'SRID=@SRID@;LINESTRING(21 22, 35 22)');
-- E8
INSERT INTO city_data.edge VALUES(8, 18, 19, -15, -17, 0, 5,
'SRID=@SRID@;LINESTRING(35 22, 47 22)');
-- E9
INSERT INTO city_data.edge VALUES(9, 15, 14, 19, -22, 3, 6,
'SRID=@SRID@;LINESTRING(9 14, 21 14)');
-- E10
INSERT INTO city_data.edge VALUES(10, 13, 14, -20, 17, 7, 4,
'SRID=@SRID@;LINESTRING(35 14, 21 14)');
-- E11
INSERT INTO city_data.edge VALUES(11, 13, 12, 15, -18, 5, 8,
'SRID=@SRID@;LINESTRING(35 14, 47 14)');
-- E12
INSERT INTO city_data.edge VALUES(12, 8, 9, 20, 22, 6, 0,
'SRID=@SRID@;LINESTRING(9 6, 21 6)');
-- E13
INSERT INTO city_data.edge VALUES(13, 9, 10, 18, -12, 7, 0,
'SRID=@SRID@;LINESTRING(21 6, 35 6)');
-- E14
INSERT INTO city_data.edge VALUES(14, 10, 11, 16, -13, 8, 0,
'SRID=@SRID@;LINESTRING(35 6, 47 6)');
-- E15
INSERT INTO city_data.edge VALUES(15, 12, 19, -8, -16, 5, 0,
'SRID=@SRID@;LINESTRING(47 14, 47 22)');
-- E16
INSERT INTO city_data.edge VALUES(16, 11, 12, -11, -14, 8, 0,
'SRID=@SRID@;LINESTRING(47 6, 47 14)');
-- E17
INSERT INTO city_data.edge VALUES(17, 13, 18, -7, 11, 4, 5,
'SRID=@SRID@;LINESTRING(35 14, 35 22)');
-- E18
INSERT INTO city_data.edge VALUES(18, 10, 13, 10, 14, 7, 8,
'SRID=@SRID@;LINESTRING(35 6, 35 14)');
-- E19
INSERT INTO city_data.edge VALUES(19, 14, 17, -6, -10, 3, 4,
'SRID=@SRID@;LINESTRING(21 14, 21 22)');
-- E20
INSERT INTO city_data.edge VALUES(20, 9, 14, -9, 13, 6, 7,
'SRID=@SRID@;LINESTRING(21 6, 21 14)');
-- E21
INSERT INTO city_data.edge VALUES(21, 15, 16, 6, 9, 0, 3,
'SRID=@SRID@;LINESTRING(9 14, 9 22)');
-- E22
INSERT INTO city_data.edge VALUES(22, 8, 15, 21, 12, 0, 6,
'SRID=@SRID@;LINESTRING(9 6, 9 14)');
-- E25
INSERT INTO city_data.edge VALUES(25, 21, 22, -25, 25, 1, 1,
'SRID=@SRID@;LINESTRING(9 35, 13 35)');
-- E26
INSERT INTO city_data.edge VALUES(26, 20, 20, 26, -26, 9, 1,
'SRID=@SRID@;LINESTRING(4 31, 7 31, 7 34, 4 34, 4 31)');
-- UPDATE Edge id sequence
SELECT setval('city_data.edge_data_edge_id_seq', 26);
-- Set face minimum bounding rectangle
UPDATE city_data.face set mbr = ST_SetSRID( ( select st_extent(geom) from city_data.edge where left_face = face_id or right_face = face_id ), @SRID@ ) where face_id != 0;
END;
|
