Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
cover_manhole_mod_pully
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment