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