GLE-Postgis-ALB

Aus kvwmap
Wechseln zu: Navigation, Suche
CREATE OR REPLACE VIEW alb_adresse AS 
 SELECT a.oid, a.flurstkennz, a.flurstkennz AS fst, (((vg.gemeindename::text || ' '::text) || vs.strassenname::text) || ' '::text) || ltrim(lower(a.hausnr::text), ' '::text) AS adresse, vg.gemeinde, vg.gemeindename, ((vs.strassenname::text || ' ('::text) || a.strasse::text) || ')'::text AS strasse, ltrim(lower(a.hausnr::text), ' '::text) AS hausnr
   FROM alb_f_adressen a, alb_v_gemeinden vg, alb_v_strassen vs, alb_flurstuecke f
  WHERE a.gemeinde = vg.gemeinde AND a.gemeinde = vs.gemeinde AND a.strasse::text = vs.strasse::text AND a.flurstkennz::text = f.flurstkennz::text AND f.status <> 'H'::text
  ORDER BY a.flurstkennz;
 
CREATE OR REPLACE VIEW alb_buchung AS 
 SELECT alb_g_buchungen.oid, (alb_g_buchungen.bezirk::text || '-'::text) || alb_g_buchungen.blatt::text AS gb, alb_v_grundbuchbezirke.bezeichnung, alb_g_buchungen.bezirk, ltrim(alb_g_buchungen.blatt::text, '0'::text) AS blatt, alb_g_buchungen.flurstkennz AS fst, alb_g_buchungen.flurstkennz, alb_g_buchungen.bvnr, alb_g_buchungen.erbbaurechtshinw, ''::text AS grundbuch, ''::text AS grundstueck, ''::text AS eigentuemer, alkobj_e_fla.the_geom
   FROM alb_g_buchungen
   LEFT JOIN alb_grundbuecher ON alb_g_buchungen.bezirk = alb_grundbuecher.bezirk AND alb_g_buchungen.blatt::text = alb_grundbuecher.blatt::text
   LEFT JOIN alb_v_grundbuchbezirke ON alb_g_buchungen.bezirk = alb_v_grundbuchbezirke.grundbuchbezschl
   LEFT JOIN alknflst ON alb_g_buchungen.flurstkennz::text = alknflst.flurstkennz::text
   LEFT JOIN alkobj_e_fla ON alknflst.objnr::text = alkobj_e_fla.objnr::text
  WHERE alb_grundbuecher.aktualitaetsnr::text <> 'hist'::text;
 
CREATE OR REPLACE VIEW alb_eigentuemer AS 
 SELECT e.oid, (e.bezirk::text || '-'::text) || e.blatt::text AS gb, b.bezeichnung, e.bezirk, ltrim(e.blatt::text, '0'::text) AS blatt, e.namensnr, a.bezeichnung AS eigentuemerart, e.anteilsverhaeltnis, rtrim("replace"(n.name1::text, ',,'::text, ','::text), ','::text) AS name1, btrim(n.name2::text) AS name2, n.name3, n.name4
   FROM alb_g_eigentuemer e, alb_g_namen n, alb_grundbuecher g, alb_v_eigentuemerarten a, alb_v_grundbuchbezirke b
  WHERE e.lfd_nr_name = n.lfd_nr_name AND e.bezirk = g.bezirk AND e.blatt::text = g.blatt::text AND e.bezirk = b.grundbuchbezschl AND g.aktualitaetsnr::text <> 'hist'::text AND e.eigentuemerart = a.eigentuemerart;
 
CREATE OR REPLACE VIEW alb_flurstueck AS 
 SELECT alb_flurstuecke.oid, alb_flurstuecke.flurstkennz, alb_v_gemarkungen.gemkgname, alb_flurstuecke.gemkgschl, ltrim(alb_flurstuecke.flurnr, '0'::text) AS flurnr, ltrim("substring"(alb_flurstuecke.flurstkennz::text, 12, 5), '0'::text) AS zaehler, ltrim("substring"(alb_flurstuecke.flurstkennz::text, 18, 3), '0'::text) AS nenner, alb_flurstuecke.pruefzeichen, alb_flurstuecke.status, alb_flurstuecke.entsteh, alb_flurstuecke.letzff, alb_flurstuecke.flaeche, btrim(to_char(alb_flurstuecke.aktunr::double precision, '909'::text)) AS aktunr, ltrim(alb_flurstuecke.karte, ' '::text) AS karte, alb_flurstuecke.baublock, alb_v_forstaemter.name AS forstamt, alb_v_finanzaemter.name AS finanzamt, alb_flurstuecke.erbbau, ''::text AS grundbuch, ''::text AS adresse, ''::text AS lage, ''::text AS hinweis, ''::text AS verfahren, ''::text AS nutzung, ''::text AS vorgaenger, alkobj_e_fla.the_geom
   FROM alb_flurstuecke
   LEFT JOIN alb_v_finanzaemter ON alb_flurstuecke.finanzamt = alb_v_finanzaemter.finanzamt
   LEFT JOIN alb_v_forstaemter ON alb_flurstuecke.forstamt = alb_v_forstaemter.forstamt
   LEFT JOIN alb_v_gemarkungen ON alb_flurstuecke.gemkgschl = alb_v_gemarkungen.gemkgschl
   LEFT JOIN alknflst ON alb_flurstuecke.flurstkennz::text = alknflst.flurstkennz::text
   LEFT JOIN alkobj_e_fla ON alknflst.objnr::text = alkobj_e_fla.objnr::text
  WHERE alb_flurstuecke.status <> 'H'::text;

CREATE OR REPLACE VIEW alb_grundbuch AS 
 SELECT alb_grundbuecher.oid, (alb_grundbuecher.bezirk::text || '-'::text) || alb_grundbuecher.blatt::text AS gb, alb_v_grundbuchbezirke.bezeichnung, alb_grundbuecher.bezirk, ltrim(alb_grundbuecher.blatt::text, '0'::text) AS blatt, alb_grundbuecher.pruefzeichen, ltrim(alb_grundbuecher.aktualitaetsnr::text, '0'::text) AS aktualitaetsnr, alb_grundbuecher.zusatz_eigentuemer, alb_grundbuecher.bestandsflaeche, (( SELECT count(*) AS count
           FROM alb_buchung
          WHERE alb_buchung.bezirk = alb_grundbuecher.bezirk AND alb_buchung.blatt = ltrim(alb_grundbuecher.blatt::text, '0'::text)))::text || ' Buchungen'::text AS buchung, '' AS grundstueck, '' AS eigentuemer
   FROM alb_grundbuecher, alb_v_grundbuchbezirke
  WHERE alb_grundbuecher.bezirk = alb_v_grundbuchbezirke.grundbuchbezschl AND alb_grundbuecher.aktualitaetsnr::text <> 'hist'::text;
 
CREATE OR REPLACE VIEW alb_grundstueck AS 
 SELECT alb_g_grundstuecke.oid, (((alb_g_grundstuecke.bezirk::text || '-'::text) || alb_g_grundstuecke.blatt::text) || '-'::text) || alb_g_grundstuecke.bvnr::text AS gst, alb_v_grundbuchbezirke.bezeichnung, alb_g_grundstuecke.bezirk, ltrim(alb_g_grundstuecke.blatt::text, '0'::text) AS blatt, alb_g_grundstuecke.bvnr, (alb_g_grundstuecke.buchungsart::text || ' - '::text) || alb_v_buchungsarten.bezeichnung::text AS buchungsart, alb_g_grundstuecke.anteil, alb_g_grundstuecke.auftplannr, alb_g_grundstuecke.sondereigentum, (( SELECT count(*) AS count
           FROM alb_buchung
          WHERE alb_buchung.bezirk = alb_g_grundstuecke.bezirk AND alb_buchung.blatt = ltrim(alb_g_grundstuecke.blatt::text, '0'::text) AND alb_buchung.bvnr::text = alb_g_grundstuecke.bvnr::text))::text || ' Buchungen'::text AS buchung, '' AS grundbuch, '' AS eigentuemer
   FROM alb_g_grundstuecke, alb_grundbuecher, alb_v_buchungsarten, alb_v_grundbuchbezirke
  WHERE alb_g_grundstuecke.bezirk = alb_grundbuecher.bezirk AND alb_g_grundstuecke.blatt::text = alb_grundbuecher.blatt::text AND alb_g_grundstuecke.bezirk = alb_v_grundbuchbezirke.grundbuchbezschl AND alb_grundbuecher.aktualitaetsnr::text <> 'hist'::text AND alb_g_grundstuecke.buchungsart = alb_v_buchungsarten.buchungsart;
 
CREATE OR REPLACE VIEW alb_hinweise AS 
 SELECT fh.oid, fh.flurstkennz, vh.bezeichnung
   FROM alb_f_hinweise fh, alb_v_hinweise vh, alb_flurstuecke f
  WHERE fh.hinwzflst = vh.hinwzflst AND fh.flurstkennz::text = f.flurstkennz::text AND f.status <> 'H'::text;
 
CREATE OR REPLACE VIEW alb_nutzungen AS 
 SELECT fn.oid, fn.flurstkennz, fn.nutzungsart, vn.bezeichnung, fn.flaeche
   FROM alb_f_nutzungen fn, alb_v_nutzungsarten vn, alb_flurstuecke f
  WHERE fn.nutzungsart = vn.nutzungsart AND fn.flurstkennz::text = f.flurstkennz::text AND f.status <> 'H'::text;
 
CREATE OR REPLACE VIEW alb_verfahren AS 
 SELECT v.oid, v.flurstkennz, v.ausfstelle AS ausfstellenr, s.name AS ausfstellename, v.verfnr, b.bezeichnung AS verfbez
   FROM alb_f_verfahren v
   LEFT JOIN alb_v_ausfuehrendestellen s ON v.ausfstelle::text = s.ausfstelle::text
   LEFT JOIN alb_v_bemerkgzumverfahren b ON v.verfbem = b.verfbem
   LEFT JOIN alb_flurstuecke f ON v.flurstkennz::text = f.flurstkennz::text
  WHERE f.status <> 'H'::text;