cover_manhole_mod_pully

cover_manhole_mod_pully.sql

WITH netzlinien AS (
SELECT
  gid,
--  ST_Fineltra(ST_SetSRID(ST_Point(first(y1),first(x1)),21781), 'chenyx06.chenyx06_triangles', 'the_geom_lv03', 'the_geom_lv95') as from_point,
  ST_SetSRID(ST_Point(first(y1), first(x1)),21781) as from_point,
--  ST_Fineltra(ST_SetSRID(ST_Point(last(y1),last(x1)),21781), 'chenyx06.chenyx06_triangles', 'the_geom_lv03', 'the_geom_lv95') as to_point,
  ST_SetSRID(ST_Point(last(y1), first(x1)),21781) as to_point,
--  ST_Fineltra(St_SetSRID(ST_GeomFromText('LINESTRINGZ('||string_agg(y1::varchar||' '||x1::varchar||' '||coalesce(z1,0)::varchar, ',' ORDER BY seq)||')'),21781), 'chenyx06.chenyx06_triangles', 'the_geom_lv03', 'the_geom_lv95') AS geometry
  ST_SetSRID(ST_GeomFromText('LINESTRINGZ('||string_agg(y1::varchar||' '||x1::varchar||' '||coalesce(z1,0)::varchar, ',' ORDER BY seq)||')'),21781) as geometry
FROM sa.aw_netzlinie_geo
GROUP BY gid)

INSERT INTO qgep.vw_qgep_wastewater_structure
 (
  ws_type,
  co_identifier,
  bottom_level,
  --depth,
  year_of_construction,
  dimension1,
  dimension2,
  --year_of_replacement,
  location_name,
  remark,
  ws_remark,
  situation_geometry,
  level,
  manhole_function,
  status,
  identifier,
  fk_owner
)

SELECT
  'manhole',
  schacht.name2,
  unten_hoehe,
  --CASE  -- remove depth (apparently level was used instead)
  --    WHEN tiefe < 100 THEN 1000*tiefe
        --ELSE NULL
  --END,
  baujahr,
  dn,
  breite,
  --EXTRACT(YEAR FROM date_rehabil),
  ortsbezeichnung,
  substr(deckel.bemerkung, 1, 80),
  substr(schacht.bemerkung, 1, 80),
  --ST_Force2d(ST_Fineltra( ST_SetSRID(ST_MakePoint( deckel_geo.y1, deckel_geo.x1, deckel_geo.z1 ), 21781 ), 'chenyx06.chenyx06_triangles', 'the_geom_lv03', 'the_geom_lv95')),
  ST_Multi(ST_SetSRID(ST_MakePoint( deckel_geo.y1, deckel_geo.x1),21781))::geometry(MultiPoint, 21781),
  Z1,
  --CASE WHEN id_aeration=1 THEN 4533 ELSE mf.new END,
  mf.new,
  st.new,
  schacht.fid,
  org.obj_id
  
FROM sa.aw_schacht_deckel deckel
LEFT JOIN sa.aw_schacht schacht ON deckel.fid_schacht = schacht.fid
LEFT JOIN sa.aw_schacht_deckel_geo deckel_geo ON deckel_geo.gid = deckel.gid
LEFT JOIN sa.map_manhole_function mf ON schacht.id_schachtart = mf.old
LEFT JOIN sa.map_status st ON schacht.id_status = st.old
LEFT JOIN sa.ba_eigentumsverhaeltnis_tbd ev ON ev.id = schacht.id_eigentumsverhaeltnis
LEFT JOIN qgep.od_organisation org ON org.identifier = ev.value
WHERE COALESCE(deckel.deleted, 0) = 0 AND COALESCE(schacht.deleted, 0) = 0;