1--
2-- Define some hierarchical layers
3--
4
5--
6-- Parcels
7--
8
9CREATE TABLE features.big_parcels (
10	feature_name varchar primary key, fid serial
11) ;
12
13SELECT topology.AddTopoGeometryColumn('city_data', 'features',
14	'big_parcels', 'feature', 'POLYGON',
15	1 -- the land_parcles
16);
17
18SELECT AddGeometryColumn('features','big_parcels','the_geom',-1,'MULTIPOLYGON',2);
19
20INSERT INTO features.big_parcels(feature_name, feature) VALUES ('P1P2', -- Feature name
21  topology.CreateTopoGeom(
22    'city_data', -- Topology name
23    3, -- Topology geometry type (polygon/multipolygon)
24    (SELECT layer_id FROM topology.layer WHERE table_name = 'big_parcels'),
25    '{{1,1},{2,1}}')); -- P1 and P2
26
27INSERT INTO features.big_parcels(feature_name, feature) VALUES ('P3P4', -- Feature name
28  topology.CreateTopoGeom(
29    'city_data', -- Topology name
30    3, -- Topology geometry type (polygon/multipolygon)
31    (SELECT layer_id FROM topology.layer WHERE table_name = 'big_parcels'),
32    '{{3,1},{4,1}}')); -- P3 and P4
33
34INSERT INTO features.big_parcels(feature_name, feature) VALUES ('F3F6', -- Feature name
35  topology.CreateTopoGeom(
36    'city_data', -- Topology name
37    3, -- Topology geometry type (polygon/multipolygon)
38    (SELECT layer_id FROM topology.layer WHERE table_name = 'big_parcels'),
39    (SELECT topoelementarray_agg(ARRAY[id(feature), 1])
40     FROM features.land_parcels
41     WHERE feature_name in ('F3','F6'))
42  ));
43
44--
45-- Streets
46--
47
48CREATE TABLE features.big_streets (
49	feature_name varchar primary key, fid serial
50) ;
51
52SELECT topology.AddTopoGeometryColumn('city_data', 'features',
53	'big_streets', 'feature', 'LINE',
54	3 -- the city_streets layer id
55);
56
57INSERT INTO features.big_streets(feature_name, feature)VALUES ('R1R2', -- Feature name
58  topology.CreateTopoGeom(
59    'city_data', -- Topology name
60    2, -- Topology geometry type (lineal)
61    (SELECT layer_id FROM topology.layer WHERE table_name = 'big_streets'),
62    (SELECT topoelementarray_agg(ARRAY[id(feature), 3])
63     FROM features.city_streets
64     WHERE feature_name in ('R1','R2')) -- R1 and R2
65  ));
66
67INSERT INTO features.big_streets(feature_name, feature) VALUES ('R4', -- Feature name
68  topology.CreateTopoGeom(
69    'city_data', -- Topology name
70    2, -- Topology geometry type (lineal)
71    (SELECT layer_id FROM topology.layer WHERE table_name = 'big_streets'),
72    (SELECT topoelementarray_agg(ARRAY[id(feature), 3])
73     FROM features.city_streets
74     WHERE feature_name in ('R4'))
75  ));
76
77--
78-- Signs
79--
80
81CREATE TABLE features.big_signs (
82	feature_name varchar primary key, fid serial
83) ;
84
85SELECT topology.AddTopoGeometryColumn('city_data', 'features',
86	'big_signs', 'feature', 'POINT',
87	2 -- the traffic_signs
88);
89
90SELECT AddGeometryColumn('features','big_signs','the_geom',0,'MULTIPOINT',2);
91
92INSERT INTO features.big_signs(feature_name, feature) VALUES ('S1S2', -- Feature name
93  topology.CreateTopoGeom(
94    'city_data', -- Topology name
95    1, -- Topology geometry type (point/multipoint)
96    (SELECT layer_id FROM topology.layer WHERE table_name = 'big_signs'),
97    '{{1,2},{2,2}}')); -- S1 and S2
98
99