mopublic-views

1 MOPublic / AV-WMS 2 Annahmen 3 Views 3.1 Bemerkungen 3.2 CP Control_points 3.3 LC LCSurface 3.4 LCSurface_PosText 3.5 LC LCSurface_Proj 3.6 SO Surface_element 3.7 SO Surface_element_PosText 3.8 SO Linear_element 3.9 SO Linear_elemen_PosText 3.10 SO Point_element 3.11 SO Point_element_PosText 3.12 LN Names 3.13 LN Names_PosName 3.14 OS Boundary_point 3.15 OS RealEstate 3.16 OS RealEstate_PosNumer 3.17 OS DPR_Mine 3.18 OS DPR_Mine_PosNumber 3.19 OS RealEstateProj 3.20 OS DPR_MineProj 3.21 OS DPR_MineProj_PosNumber 3.22 PI Linear_element 3.23 PI Linear_element_PosName 3.24 TB Boundary_Terr_Point 3.25 TB Municipal_Boundary 3.26 TB Municipal_BoundProj 3.27 TB Other_territ_boundary 3.28 HOU Street_name 3.29 HOU Street_name_Pos 3.30 HOU Building_entrance 3.31 HOU Building_entrance_Pos

MOPublic / AV-WMS

Für PostgreSQL/Postgis werden Views formuliert, die als Grundlage für das MOPublic und den AV-WMS dienen. Die ITFs können z.B. mit ogr2ogr oder iox-ili (wurde hier verwendet) in die Datenbank importiert werden.

Annahmen

• Sämtliche Tabellen eines AV-Operates liegen DM01-konform in der Datenbank vor.
• Jedem Objekt ist die BfS-Nr. zugewiesen.
• Jedes Objekt hat eine eindeutige (pro Tabelle) ID (ogc_fid).
• Der TID ist systemweit eindeutig.
• Ist der TID systemweit nicht eindeutig, muss die jeweilige where-Bedingung angepasst werden.
• Ist der TID systemweit nicht eindeutig und liegen pro Gemeinde mehrere Operate (z.B. verschiedene Lose) vor, muss ein zusätzliches Attribut eingeführt werden und die jeweilige where-Bedingung angepasst werden.

Views

Bemerkungen

• Es wird jeweils eine über die Tabelle eindeutige ID (ogc_fid) eingeführt.
• Die Tabellennamen müssen womöglich angepasst werden (je nach verwendeter Software beim Importieren).
• Der View-Name wird aus Topic- und Tabellenname zusammengesetzt.

CP Control_points

CREATE SEQUENCE mopublic.control_points_control_point_ogc_fid_seq 
  MINVALUE 1
  MAXVALUE 2147483646;

CREATE OR REPLACE VIEW mopublic.control_points_control_point as
  SELECT nextval('mopublic.control_points_control_point_ogc_fid_seq')::integer as ogc_fid, tid, 0 as category, nbident as identnd, nummer as number, geometrie as geometry, lagegen as plan_accuracy, hoehegeom as geom_alt, hoehegen as alt_accuracy, 8 as mark, gem_bfs as fosnr
  FROM avdpool.fixpunktekategorie1_lfp1
UNION
  SELECT nextval('mopublic.control_points_control_point_ogc_fid_seq')::integer as ogc_fid, tid, 1 as category, nbident as identnd, nummer as number, geometrie as geometry, lagegen as plan_accuracy, hoehegeom as geom_alt, hoehegen as alt_accuracy, 8 as mark, gem_bfs as fosnr
  FROM avdpool.fixpunktekategorie1_hfp1
UNION
  SELECT nextval('mopublic.control_points_control_point_ogc_fid_seq')::integer as ogc_fid, tid, 2 as category, nbident as identnd, nummer as number, geometrie as geometry, lagegen as plan_accuracy, hoehegeom as geom_alt, hoehegen as alt_accuracy, 8 as mark, gem_bfs as fosnr
  FROM avdpool.fixpunktekategorie2_lfp2
UNION
  SELECT nextval('mopublic.control_points_control_point_ogc_fid_seq')::integer as ogc_fid, tid, 3 as category, nbident as identnd, nummer as number, geometrie as geometry, lagegen as plan_accuracy, hoehegeom as geom_alt, hoehegen as alt_accuracy, 8 as mark, gem_bfs as fosnr
  FROM avdpool.fixpunktekategorie2_hfp2
UNION
  SELECT nextval('mopublic.control_points_control_point_ogc_fid_seq')::integer as ogc_fid, tid, 4 as category, nbident as identnd, nummer as number, geometrie as geometry, lagegen as plan_accuracy, hoehegeom as geom_alt, hoehegen as alt_accuracy, punktzeichen as mark, gem_bfs as fosnr
  FROM avdpool.fixpunktekategorie3_lfp3
UNION
  SELECT nextval('mopublic.control_points_control_point_ogc_fid_seq')::integer as ogc_fid, tid, 5 as category, nbident as identnd, nummer as number, geometrie as geometry, lagegen as plan_accuracy, hoehegeom as geom_alt, hoehegen as alt_accuracy, 8 as mark, gem_bfs as fosnr
  FROM avdpool.fixpunktekategorie3_hfp3;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'control_points_control_point', 'geometry', 2, 21781, 'POINT')

LC LCSurface

CREATE OR REPLACE VIEW mopublic.land_cover_lcsurface AS
SELECT a.ogc_fid as ogc_fid, a.tid as tid, a.geometrie as geometry, a.qualitaet as quality, c.ch as type, b.gwr_egid::INTEGER as regbl_egid, a.gem_bfs as fosnr
FROM mopublic.bbso2ch as c, avdpool.bodenbedeckung_boflaeche as a LEFT OUTER JOIN avdpool.bodenbedeckung_gebaeudenummer as b
ON (a.tid = b.gebaeudenummer_von)
WHERE a.art = c.so;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'land_cover_lcsurface', 'geometry', 2, 21781, 'POLYGON')

Falls das kantonale Datenmodell weitere Bodenbedeckungsarten vorsieht, müssen diese in das CH-Modell gemappt werden. Dazu ist eine zusätzliche Tabelle notwendig (hier mopublic.bbso2ch).

LCSurface_PosText

CREATE OR REPLACE VIEW mopublic.land_cover_lcsurface_postext AS
SELECT a.ogc_fid as ogc_fid, a.tid as tid, a.objektname_von as postext_of, 1 as type, a.name as number_name, b.pos as pos, b.ori as ori, a.gem_bfs as fosnr 
FROM avdpool.bodenbedeckung_objektname as a, avdpool.bodenbedeckung_objektnamepos as b
WHERE a.tid = b.objektnamepos_von;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'land_cover_lcsurface_postext', 'pos', 2, 21781, 'POINT')

Im Kanton SO werden keine Gebäudenummern verwaltet (type = 2).

LC LCSurface_Proj

CREATE OR REPLACE VIEW mopublic.land_cover_lcsurfaceproj AS
SELECT a.ogc_fid as ogc_fid, a.tid, a.geometrie as geometry, 4 as quality, 0 as type, b.gwr_egid::INTEGER as regbl_egid, a.gem_bfs as fosnr
FROM avdpool.bodenbedeckung_projboflaeche as a LEFT OUTER JOIN avdpool.bodenbedeckung_projgebaeudenummer as b
ON (a.tid = b.projgebaeudenummer_von)
WHERE a.art = 0;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'land_cover_lcsurfaceproj', 'geometry', 2, 21781, 'POLYGON')

Momentan werden nur projektierte Gebäude erfasst. Um andere Objekte auszuschliessen, die fälschlicherweise in der Tabelle vorhanden sind, wird eine where-Bedingung eingefügt.

SO Surface_element

CREATE OR REPLACE VIEW mopublic.single_objects_surface_element AS
SELECT b.ogc_fid as ogc_fid, b.tid as tid, 1 as validity, c.ch as type, b.geometrie as geometry, 0 as quality, d.gwr_egid::INTEGER as regbl_egid, a.gem_bfs as fosnr
FROM avdpool.einzelobjekte_flaechenelement as b, mopublic.eoso2ch as c, avdpool.einzelobjekte_einzelobjekt as a
  LEFT OUTER JOIN avdpool.einzelobjekte_objektnummer as d ON (a.tid = d.objektnummer_von)
WHERE a.tid = b.flaechenelement_von
AND a.art = c.so;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'single_objects_surface_element', 'geometry', 2, 21781, 'POLYGON')

Kantonale Mehranforderungen müssen wiederum gemappt werden. Projektierte Einzelobjekte sind nicht vorhanden und das Attribut validity wird auf gültig (1) gesetzt.

SO Surface_element_PosText

CREATE OR REPLACE VIEW mopublic.single_objects_surface_element_postext AS
SELECT DISTINCT b.ogc_fid as ogc_fid, a.tid as tid, a.objektname_von as postext_of, 1 as type, a.name as number_name, b.pos as pos, b.ori as ori, a.gem_bfs as fosnr
FROM avdpool.einzelobjekte_objektname as a, avdpool.einzelobjekte_objektnamepos as b, avdpool.einzelobjekte_einzelobjekt as c, avdpool.einzelobjekte_flaechenelement as d
WHERE b.objektnamepos_von = a.tid
AND a.objektname_von = c.tid
AND d.flaechenelement_von = c.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'single_objects_surface_element_postext', 'pos', 2, 21781, 'POINT')

Keine Nummern vorhanden, nur Objektnamen (type = 1).

SO Linear_element

CREATE OR REPLACE VIEW mopublic.single_objects_linear_element AS
SELECT b.ogc_fid as ogc_fid, b.tid as tid, 1 as validity, c.ch as type, b.geometrie as geometry, 0 as quality, a.gem_bfs as fosnr
FROM avdpool.einzelobjekte_linienelement as b, mopublic.eoso2ch as c, avdpool.einzelobjekte_einzelobjekt as a
WHERE a.tid = b.linienelement_von
AND a.art = c.so;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'single_objects_linear_element', 'geometry', 2, 21781, 'LINESTRING')

Projektierte Objekte werden nicht berücksichtigt.

SO Linear_elemen_PosText

CREATE OR REPLACE VIEW mopublic.single_objects_linear_element_postext AS
SELECT DISTINCT b.ogc_fid as ogc_fid, a.tid as tid, a.objektname_von as postext_of, 1 as type, a.name as number_name, b.pos as pos, b.ori as ori, a.gem_bfs as fosnr
FROM avdpool.einzelobjekte_objektname as a, avdpool.einzelobjekte_objektnamepos as b, avdpool.einzelobjekte_einzelobjekt as c, avdpool.einzelobjekte_linienelement as d
WHERE b.objektnamepos_von = a.tid
AND a.objektname_von = c.tid
AND d.linienelement_von = c.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'single_objects_linear_element_postext', 'pos', 2, 21781, 'POINT')

SO Point_element

CREATE OR REPLACE VIEW mopublic.single_objects_point_element AS
SELECT b.ogc_fid as ogc_fid, b.tid as tid, 1 as validity, c.ch as type, b.geometrie as geometry, 0 as quality, a.gem_bfs as fosnr
FROM avdpool.einzelobjekte_punktelement as b, mopublic.eoso2ch as c, avdpool.einzelobjekte_einzelobjekt as a
WHERE a.tid = b.punktelement_von
AND a.art = c.so;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'single_objects_point_element', 'geometry', 2, 21781, 'POINT')

Projektierte Objekte werden nicht berücksichtigt.

SO Point_element_PosText

CREATE OR REPLACE VIEW mopublic.single_objects_point_element_postext AS
SELECT DISTINCT b.ogc_fid as ogc_fid, a.tid as tid, a.objektname_von as postext_of, 1 as type, a.name as number_name, b.pos as pos, b.ori as ori, a.gem_bfs as fosnr
FROM avdpool.einzelobjekte_objektname as a, avdpool.einzelobjekte_objektnamepos as b, avdpool.einzelobjekte_einzelobjekt as c, avdpool.einzelobjekte_punktelement as d
WHERE b.objektnamepos_von = a.tid
AND a.objektname_von = c.tid
AND d.punktelement_von = c.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'single_objects_point_element_postext', 'pos', 2, 21781, 'POINT')

LN Names

CREATE SEQUENCE mopublic.local_names_names_ogc_fid_seq 
  MINVALUE 1
  MAXVALUE 2147483646;

CREATE OR REPLACE VIEW mopublic.local_names_names AS
  SELECT nextval('mopublic.local_names_names_ogc_fid_seq')::integer as ogc_fid, tid, 0 as category, name, geometrie as geometry, NULL::varchar as type, gem_bfs as fosnr
  FROM avdpool.nomenklatur_flurname 
UNION
  SELECT nextval('mopublic.local_names_names_ogc_fid_seq')::integer as ogc_fid, tid, 1 as category, name, geometrie as geometry, typ as type, gem_bfs as fosnr
  FROM avdpool.nomenklatur_ortsname ;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'local_names_names', 'geometry', 2, 21781, 'POLYGON')

LN Names_PosName

CREATE SEQUENCE mopublic.local_names_names_posname_ogc_fid_seq 
  MINVALUE 1
  MAXVALUE 2147483646;

CREATE OR REPLACE VIEW mopublic.local_names_names_posname AS
  SELECT nextval('mopublic.local_names_names_posname_ogc_fid_seq')::integer as ogc_fid, a.tid, a.flurnamepos_von as posname_of, 0 as category, b.name, a.pos, a.ori, a.gem_bfs as fosnr
  FROM avdpool.nomenklatur_flurnamepos as a, avdpool.nomenklatur_flurname as b
  WHERE b.tid = a.flurnamepos_von
UNION
  SELECT nextval('mopublic.local_names_names_posname_ogc_fid_seq')::integer as ogc_fid, a.tid, a.ortsnamepos_von as posname_of, 1 as category, b.name, a.pos, a.ori, a.gem_bfs as fosnr
  FROM avdpool.nomenklatur_ortsnamepos as a, avdpool.nomenklatur_ortsname as b
  WHERE b.tid = a.ortsnamepos_von
UNION
  SELECT nextval('mopublic.local_names_names_posname_ogc_fid_seq')::integer as ogc_fid, a.tid, NULL::varchar as posname_of, 2 as category, b.name, a.pos, a.ori, a.gem_bfs as fosnr
  FROM avdpool.nomenklatur_gelaendenamepos as a, avdpool.nomenklatur_gelaendename as b
  WHERE b.tid = a.gelaendenamepos_von;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'local_names_names_posname', 'pos', 2, 21781, 'POINT');

OS Boundary_point

CREATE OR REPLACE VIEW mopublic.ownership_boundary_point AS
SELECT b.ogc_fid as ogc_fid, b.tid as tid, b.geometrie as geometry, a.gueltigkeit as validity, b.lagegen as planaccuracy, b.lagezuv as plan_reliability, b.punktzeichen as mark, b.gem_bfs as fosnr
FROM avdpool.liegenschaften_lsnachfuehrung as a, avdpool.liegenschaften_grenzpunkt as b
WHERE b.entstehung = a.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'ownership_boundary_point', 'geometry', 2, 21781, 'POINT');

OS RealEstate

CREATE OR REPLACE VIEW mopublic.ownership_realestate AS
SELECT b.ogc_fid as ogc_fid, a.tid as tid, a.nbident as identnd, a.nummer as number, a.egris_egrid as egris_egrid, a.vollstaendigkeit as completeness, b.flaechenmass as area, b.geometrie as geometry, b.gem_bfs as fosnr
FROM avdpool.liegenschaften_grundstueck as a, avdpool.liegenschaften_liegenschaft as b
WHERE b.liegenschaft_von = a.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'ownership_realestate', 'geometry', 2, 21781, 'POLYGON');

OS RealEstate_PosNumer

CREATE OR REPLACE VIEW mopublic.ownership_realestate_posnumber AS
SELECT b.ogc_fid as ogc_fid, b.tid as tid, b.grundstueckpos_von as posnumber_of, a.nbident as identnd, a.nummer as number, b.pos as pos, b.ori as ori, b.gem_bfs as fosnr
FROM avdpool.liegenschaften_grundstueck as a, avdpool.liegenschaften_grundstueckpos as b
WHERE a.tid = b.grundstueckpos_von
AND art = 0;

OS DPR_Mine

CREATE SEQUENCE mopublic.ownership_dpr_mine_ogc_fid_seq 
  MINVALUE 1
  MAXVALUE 2147483646;

CREATE OR REPLACE VIEW mopublic.ownership_dpr_mine AS
  SELECT nextval('mopublic.ownership_dpr_mine_ogc_fid_seq')::integer as ogc_fid, b.tid as tid, b.nbident as identnd, b.nummer as number, b.egris_egrid as egris_egrid, b.vollstaendigkeit as completeness, (b.art-1) as realestate_type, a.flaechenmass as area, a.geometrie as geometry, a.gem_bfs as fosnr
  FROM avdpool.liegenschaften_selbstrecht as a, avdpool.liegenschaften_grundstueck as b
  WHERE a.selbstrecht_von = b.tid
UNION
  SELECT nextval('mopublic.ownership_dpr_mine_ogc_fid_seq')::integer as ogc_fid, b.tid as tid, b.nbident as identnd, b.nummer as number, b.egris_egrid as egris_egrid, b.vollstaendigkeit as completeness, 4 as realestate_type, a.flaechenmass as area, a.geometrie as geometry, a.gem_bfs as fosnr
  FROM avdpool.liegenschaften_bergwerk as a, avdpool.liegenschaften_grundstueck as b
  WHERE a.bergwerk_von = b.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'ownership_dpr_mine', 'pos', 2, 21781, 'POLYGON');

Grundstücke der Art Liegenschaft sind hier nicht vorhanden, darum muss gegenüber der DM01-Art "1" subtrahiert werden.

OS DPR_Mine_PosNumber

CREATE OR REPLACE VIEW mopublic.ownership_dpr_mine_posnumber AS
SELECT a.ogc_fid, a.tid as tid, a.grundstueckpos_von as posnumber_of, b.identnd as identnd, b.number as number, a.pos as pos, a.ori as ori, a.gem_bfs as fosnr
FROM avdpool.liegenschaften_grundstueckpos as a, mopublic.ownership_dpr_mine as b
WHERE b.tid = a.grundstueckpos_von;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'ownership_dpr_mine_posnumber', 'pos', 2, 21781, 'POINT');

OS RealEstateProj

CREATE OR REPLACE VIEW mopublic.ownership_realestateproj_posnumber AS
SELECT b.ogc_fid as ogc_fid, b.tid as tid, b.projgrundstueckpos_von as posnumber_of, a.nbident as identnd, a.nummer as number, b.pos as pos, b.ori as ori, b.gem_bfs as fosnr
FROM avdpool.liegenschaften_projgrundstueck as a, avdpool.liegenschaften_projgrundstueckpos as b
WHERE a.tid = b.projgrundstueckpos_von;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'ownership_realestateproj_posnumber', 'pos', 2, 21781, 'POINT');

OS DPR_MineProj

CREATE SEQUENCE mopublic.ownership_dpr_mineproj_ogc_fid_seq 
  MINVALUE 1
  MAXVALUE 2147483646;

CREATE OR REPLACE VIEW mopublic.ownership_dpr_mineproj AS
  SELECT nextval('mopublic.ownership_dpr_mineproj_ogc_fid_seq')::integer as ogc_fid, b.tid as tid, b.nbident as identnd, b.nummer as number, b.egris_egrid as egris_egrid, b.vollstaendigkeit as completeness, (b.art-1) as realestate_type, a.flaechenmass as area, a.geometrie as geometry, a.gem_bfs as fosnr
  FROM avdpool.liegenschaften_projselbstrecht as a, avdpool.liegenschaften_projgrundstueck as b
  WHERE a.projselbstrecht_von = b.tid
UNION
  SELECT nextval('mopublic.ownership_dpr_mineproj_ogc_fid_seq')::integer as ogc_fid, b.tid as tid, b.nbident as identnd, b.nummer as number, b.egris_egrid as egris_egrid, b.vollstaendigkeit as completeness, 4 as realestate_type, a.flaechenmass as area, a.geometrie as geometry, a.gem_bfs as fosnr
  FROM avdpool.liegenschaften_projbergwerk as a, avdpool.liegenschaften_projgrundstueck as b
  WHERE a.projbergwerk_von = b.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'ownership_dpr_mineproj', 'geometry', 2, 21781, 'POLYGON');

OS DPR_MineProj_PosNumber

CREATE OR REPLACE VIEW mopublic.ownership_dpr_mineproj_posnumber AS
SELECT a.ogc_fid, a.tid as tid, a.projgrundstueckpos_von as posnumber_of, b.identnd as identnd, b.number as number, a.pos as pos, a.ori as ori, a.gem_bfs as fosnr
FROM avdpool.liegenschaften_projgrundstueckpos as a, mopublic.ownership_dpr_mineproj as b
WHERE b.tid = a.projgrundstueckpos_von;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'ownership_dpr_mineproj_posnumber', 'pos', 2, 21781, 'POINT');

PI Linear_element

CREATE OR REPLACE VIEW mopublic.pipelines_linear_element AS
SELECT b.ogc_fid as ogc_fid, b.tid as tid, a.betreiber as operating_company, a.art as fluid, b.geometrie as geometry, c.gueltigkeit as validity, b.gem_bfs as fosnr
FROM avdpool.rohrleitungen_leitungsobjekt as a, avdpool.rohrleitungen_linienelement as b, avdpool.rohrleitungen_rlnachfuehrung as c
WHERE b.linienelement_von = a.tid
AND a.entstehung = c.tid

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'pipelines_linear_element', 'geometry', 2, 21781, 'LINESTRING');

PI Linear_element_PosName

CREATE OR REPLACE VIEW mopublic.pipelines_linear_element_posname AS
SELECT DISTINCT ON (b.ogc_fid) b.ogc_fid, b.tid as tid, a.tid as posname_of, b.pos as pos, b.ori as ori, b.gem_bfs as fosnr
FROM
  (SELECT DISTINCT b.linienelement_von, b.tid
  FROM  avdpool.rohrleitungen_leitungsobjekt as a, avdpool.rohrleitungen_linienelement as b
  WHERE a.tid = b.linienelement_von) AS a, avdpool.rohrleitungen_leitungsobjektpos as b
WHERE a.linienelement_von = b.leitungsobjektpos_von;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'pipelines_linear_element_posname', 'pos', 2, 21781, 'POINT');

Die Zuweisung der Positionen zu den Rohrleitungsgeometrien ist im DM01 nicht direkt vorhanden, sondern sie geschieht über das Rohrleitungselement. Hier wird eine mögliche Zuweisung gemacht.

TB Boundary_Terr_Point

CREATE OR REPLACE VIEW mopublic.territorial_boundaries_boundary_terr_point AS
SELECT b.ogc_fid as ogc_fid, b.tid as tid, b.geometrie as geometry, a.gueltigkeit as validity, b.lagegen as plan_accuracy, b.lagezuv as plan_reliability, b.punktzeichen as mark, b.gem_bfs as fosnr
FROM avdpool.gemeindegrenzen_gemnachfuehrung as a, avdpool.gemeindegrenzen_hoheitsgrenzpunkt as b
WHERE b.entstehung = a.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'territorial_boundaries_boundary_terr_point', 'geometry', 2, 21781, 'POINT');

TB Municipal_Boundary

CREATE OR REPLACE VIEW mopublic.territorial_boundaries_municipal_boundary as
SELECT b.ogc_fid as ogc_fid, b.tid as tid, a.name as name, b.geometrie as geometry, 0 as code, b.gem_bfs as fosnr
FROM avdpool.gemeindegrenzen_gemeinde as a, avdpool.gemeindegrenzen_gemeindegrenze as b
WHERE b.gemeindegrenze_von = a.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'territorial_boundaries_municipal_boundary', 'geometry', 2, 21781, 'POLYGON');

Das Linienattribut code wird hardcodiert, da beim Import in die Datenbank die Linienattribute verloren gehen.

TB Municipal_BoundProj

CREATE OR REPLACE VIEW mopublic.territorial_boundaries_municipal_boundproj as
SELECT b.ogc_fid as ogc_fid, b.tid as tid, a.name as name, b.geometrie as geometry, b.gem_bfs as fosnr
FROM avdpool.gemeindegrenzen_gemeinde as a, avdpool.gemeindegrenzen_projgemeindegrenze as b
WHERE b.projgemeindegrenze_von = a.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'territorial_boundaries_municipal_boundproj', 'geometry', 2, 21781, 'POLYGON');

TB Other_territ_boundary

CREATE SEQUENCE mopublic.territorial_boundaries_other_territ_boundary_ogc_fid_seq 
  MINVALUE 1
  MAXVALUE 2147483646;

CREATE OR REPLACE VIEW mopublic.territorial_boundaries_other_territ_boundary AS
  SELECT nextval('mopublic.territorial_boundaries_other_territ_boundary_ogc_fid_seq')::integer as ogc_fid, tid, 0 as type, geometrie as geometry, gueltigkeit as bound_validity_type
  FROM avdpool.bezirksgrenzen_bezirksgrenzabschnitt
UNION
  SELECT nextval('mopublic.territorial_boundaries_other_territ_boundary_ogc_fid_seq')::integer as ogc_fid, tid, 1 as type, geometrie as geometry, gueltigkeit as bound_validity_type
  FROM avdpool.kantonsgrenzen_kantonsgrenzabschnitt
UNION
  SELECT nextval('mopublic.territorial_boundaries_other_territ_boundary_ogc_fid_seq')::integer as ogc_fid, tid, 2 as type, geometrie as geometry, gueltigkeit as bound_validity_type
  FROM avdpool.landesgrenzen_landesgrenzabschnitt;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'territorial_boundaries_other_territ_boundary', 'geometry', 2, 21781, 'LINESTRING');

HOU Street_name

CREATE OR REPLACE VIEW mopublic.building_addresses_street_name AS
SELECT DISTINCT ON (a.ogc_fid) a.ogc_fid as ogc_fid, a.tid, b.text as street_name, a.istoffiziellebezeichnung as is_official_designation, a.gem_bfs as fosnr
FROM avdpool.gebaeudeadressen_lokalisation as a, avdpool.gebaeudeadressen_lokalisationsname as b
WHERE b.benannte = a.tid;

HOU Street_name_Pos

CREATE OR REPLACE VIEW mopublic.building_addresses_street_name_pos AS
SELECT b.ogc_fid as ogc_fid, b.tid as tid, a.tid as street_name_of, a.text as street_name, b.pos as pos, b.ori as ori, a.gem_bfs as fosnr
FROM avdpool.gebaeudeadressen_lokalisationsname as a, avdpool.gebaeudeadressen_lokalisationsnamepos as b
WHERE b.lokalisationsnamepos_von = a.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'building_addresses_street_name_pos', 'pos', 2, 21781, 'POINT');

HOU Building_entrance

CREATE OR REPLACE VIEW mopublic.building_addresses_building_entrance AS
SELECT c.ogc_fid as ogc_fid, c.tid as tid, a.tid as street_of, c.status as validity, 
       c.lage as pos, c.istoffiziellebezeichnung as is_official_designation, 
       c.hoehenlage as level, c.hausnummer as house_number, NULL::varchar as name_of_house,
       c.gwr_egid::INTEGER as regbl_egid, c.gwr_edid as regbl_edid, 
       b.text as street_name, NULL::INTEGER as postalcode, NULL::INTEGER as additional_number, NULL::VARCHAR as city,
       a.gem_bfs as fosnr
FROM avdpool.gebaeudeadressen_lokalisation as a, avdpool.gebaeudeadressen_lokalisationsname as b, avdpool.gebaeudeadressen_gebaeudeeingang as c
WHERE c.gebaeudeeingang_von = a.tid
AND b.benannte = a.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'building_addresses_building_entrance', 'pos', 2, 21781, 'POINT');

Postleitzahlen und Ortschaften sind noch nicht berücksichtigt, da sie noch nicht zur Verfügung standen.

HOU Building_entrance_Pos

CREATE OR REPLACE VIEW mopublic.building_addresses_building_entrance_pos AS
SELECT b.ogc_fid as ogc_fid, b.tid, a.tid as pos_of, 0 as type, a.hausnummer, b.pos as pos, b.ori as ori, b.gem_bfs as fosnr
FROM avdpool.gebaeudeadressen_gebaeudeeingang as a, avdpool.gebaeudeadressen_hausnummerpos as b
WHERE b.hausnummerpos_von = a.tid;

INSERT INTO geometry_columns VALUES ('"', 'mopublic', 'building_addresses_building_entrance_pos', 'pos', 2, 21781, 'POINT');