-- koordinatenberechnung.sql
--
-- Skript zur Berechnung der Koordinaten derjenigen Fachobjekte einer Kommunalabwasserdatenbank,
-- die der aktuellen Berichtskampagne zuzuordnen sind, für die Europäische Kommission.
-- Ergo: Koordinaten alter (bereits in vergangenen Berichtsperioden berichteter) Objekte werden
-- nicht neu gerechnet.
--
-- (c) ENDA GmbH & Co. KG, 2012, 2014
--
-- Rev. 1, 2014-05-28, mlt: ETRS89 UTM32 ergänzt (l_coordinate_systems = 2 gab es vorher nicht)
-- Rev. 0, 2012-03-12, mlt: Erstellt unter Anpassung, Korrektur und Zuschnitt des bereits
--                          vorliegenden Skripts "gis_spalten_erzeugen.sql"
--                          Hinweis: Die EU bekommt WGS84!


-- Alle PostGIS-Koordinaten der aktuellen Berichtsperiode leeren:
update agglomerations a
set geo_src_gk2 = null, geo_src_gk3 = null,      geo_src_gk4 = null,
    geo_src_gk5 = null, geo_etrs89_utm33 = null, geo_soeldner = null,
    geo_wgs84 = null,   geo_etrs89 = null,       geo_etrs89_utm32 = null
from model.reporting_periods rp
where a.date_from = rp.date_from
  and rp.state = 1;

update plants p
set geo_src_gk2 = null, geo_src_gk3 = null,      geo_src_gk4 = null,
    geo_src_gk5 = null, geo_etrs89_utm33 = null, geo_soeldner = null,
    geo_wgs84 = null,   geo_etrs89 = null,       geo_etrs89_utm32 = null
from model.reporting_periods rp
where p.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
set geo_src_gk2 = null, geo_src_gk3 = null,       geo_src_gk4 = null,
    geo_src_gk5 = null, geo_etrs89_utm33 = null , geo_soeldner = null,
    geo_wgs84 = null,   geo_etrs89 = null,        geo_etrs89_utm32 = null
from model.reporting_periods rp
where dp.date_from = rp.date_from
  and rp.state = 1;



-- Umrechnen der gelieferten Rechts-/Hochwerte in die jeweilige PostGIS Ausgangsgeometrie
-- und eintragen derselben in die jeweiligen Agglomerationsspalten:
-- GK, 2. Streifen:
update agglomerations a
  set geo_src_gk2 = ST_GeometryFromText('POINT ' || '(' || ag_long || ' ' || ag_lat || ')', 31466)
from model.reporting_periods rp
where ag_koord_act_id = 1
  and ag_long >= 2000000
  and ag_long < 3000000
  and a.date_from = rp.date_from
  and rp.state = 1;

-- GK, 3. Streifen:
update agglomerations a
  set geo_src_gk3 = ST_GeometryFromText('POINT ' || '(' || ag_long || ' ' || ag_lat || ')', 31467)
from model.reporting_periods rp
where ag_koord_act_id = 1
  and ag_long >= 3000000
  and ag_long < 4000000
  and a.date_from = rp.date_from
  and rp.state = 1;

-- GK, 4. Streifen:
update agglomerations a
  set geo_src_gk4 = ST_GeometryFromText('POINT ' || '(' || ag_long || ' ' || ag_lat || ')', 31468)
from model.reporting_periods rp
where ag_koord_act_id = 1
  and ag_long >= 4000000
  and ag_long < 5000000
  and a.date_from = rp.date_from
  and rp.state = 1;

-- GK, 5. Streifen:
update agglomerations a
  set geo_src_gk5 = ST_GeometryFromText('POINT ' || '(' || ag_long || ' ' || ag_lat || ')', 31469)
from model.reporting_periods rp
where ag_koord_act_id = 1
  and ag_long >= 5000000
  and ag_long < 6000000
  and a.date_from = rp.date_from
  and rp.state = 1;


-- Normale Behandlung von ETRS89 UTM32:
update agglomerations a
  set geo_etrs89_utm32 = ST_GeometryFromText('POINT ' || '(' || ag_long || ' ' || ag_lat || ')', 25832)
from model.reporting_periods rp
where ag_koord_act_id = 2
  and ag_long < 1000000
  and a.date_from = rp.date_from
  and rp.state = 1;

-- Einige Länder stellen bei Verwendung von ETRS89 UTM32 dem sechstelligen Rechtwert
-- eine 32 voran. Das fangen wir hier ab:
update agglomerations a
  set geo_etrs89_utm32 = ST_GeometryFromText('POINT ' || '(' || ag_long-32000000 || ' ' || ag_lat || ')', 25832)
from model.reporting_periods rp
where ag_koord_act_id = 2
  and ag_long > 32000000
  and a.date_from = rp.date_from
  and rp.state = 1;


-- Einige Länder stellen bei Verwendung von ETRS89 UTM33 dem sechstelligen Rechtwert
-- mal eine 3 voran, mal eine 33
-- Hier fangen wir die 33 ab:
update agglomerations a
  set geo_etrs89_utm33 = ST_GeometryFromText('POINT ' || '(' || ag_long-33000000 || ' ' || ag_lat || ')', 25833)
from model.reporting_periods rp
where ag_koord_act_id = 3
  and ag_long > 33000000
  and a.date_from = rp.date_from
  and rp.state = 1;


-- Einige Länder stellen bei Verwendung von ETRS89 UTM33 dem sechstelligen Rechtwert
-- mal eine 3 voran, mal eine 33
-- Hier fangen wir die 3 ab:
update agglomerations a
  set geo_etrs89_utm33 = ST_GeometryFromText('POINT ' || '(' || ag_long-3000000 || ' ' || ag_lat || ')', 25833)
from model.reporting_periods rp
where ag_koord_act_id = 3
  and ag_long > 3000000
  and ag_long < 4000000
  and a.date_from = rp.date_from
  and rp.state = 1;


-- Normale Verarbeitung von ETRS89 UTM33:
update agglomerations a
  set geo_etrs89_utm33 = ST_GeometryFromText('POINT ' || '(' || ag_long || ' ' || ag_lat || ')', 25833)
from model.reporting_periods rp
where ag_koord_act_id = 3
  and ag_long < 1000000
  and a.date_from = rp.date_from
  and rp.state = 1;


-- BEs Soldner:
update agglomerations a
  set geo_soeldner = ST_GeometryFromText('POINT ' || '(' || ag_long || ' ' || ag_lat || ')', 3068)
from model.reporting_periods rp
where ag_koord_act_id = 5
  and federal_state_id = 11
  and a.date_from = rp.date_from
  and rp.state = 1;



-- Umrechnen der gelieferten Rechts-/Hochwerte in die jeweilige PostGIS Ausgangsgeometrie
-- und eintragen derselben in die jeweiligen Anlagenspalten:
-- GK, 2. Streifen:
update plants p
  set geo_src_gk2 = ST_GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 31466)
from model.reporting_periods rp
where pl_koord_act_id = 1
  and pl_long >= 2000000
  and pl_long < 3000000
  and p.date_from = rp.date_from
  and rp.state = 1;

-- GK, 3. Streifen:
update plants p
  set geo_src_gk3 = ST_GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 31467)
from model.reporting_periods rp
where pl_koord_act_id = 1
  and pl_long >= 3000000
  and pl_long < 4000000
  and p.date_from = rp.date_from
  and rp.state = 1;

-- GK, 4. Streifen:
update plants p
  set geo_src_gk4 = ST_GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 31468)
from model.reporting_periods rp
where pl_koord_act_id = 1
  and pl_long >= 4000000
  and pl_long < 5000000
  and p.date_from = rp.date_from
  and rp.state = 1;

-- GK, 5. Streifen:
update plants p
  set geo_src_gk5 = ST_GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 31469)
from model.reporting_periods rp
where pl_koord_act_id = 1
  and pl_long >= 5000000
  and pl_long < 6000000
  and p.date_from = rp.date_from
  and rp.state = 1;

-- Normale Behandlung von ETRS89 UTM32:
update plants p
  set geo_etrs89_utm32 = ST_GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 25832)
from model.reporting_periods rp
where p.pl_koord_act_id = 2
  and p.pl_long < 1000000
  and p.date_from = rp.date_from
  and rp.state = 1;

-- Einige Länder stellen bei Verwendung von ETRS89 UTM32 dem sechstelligen Rechtwert
-- eine 32 voran. Das fangen wir hier ab:
update plants p
  set geo_etrs89_utm32 = ST_GeometryFromText('POINT ' || '(' || pl_long-32000000 || ' ' || pl_lat || ')', 25832)
from model.reporting_periods rp
where p.pl_koord_act_id = 2
  and p.pl_long > 32000000
  and p.date_from = rp.date_from
  and rp.state = 1;

-- Einige Länder stellen bei Verwendung von ETRS89 UTM33 dem sechstelligen Rechtwert
-- mal eine 3 voran, mal eine 33
-- Hier fangen wir die 33 ab:
update plants p
  set geo_etrs89_utm33 = ST_GeometryFromText('POINT ' || '(' || pl_long-33000000 || ' ' || pl_lat || ')', 25833)
from model.reporting_periods rp
where p.pl_koord_act_id = 3
  and p.pl_long > 33000000
  and p.date_from = rp.date_from
  and rp.state = 1;

-- Einige Länder stellen bei Verwendung von ETRS89 UTM33 dem sechstelligen Rechtwert
-- mal eine 3 voran, mal eine 33
-- Hier fangen wir die 3 ab:
update plants p
  set geo_etrs89_utm33 = ST_GeometryFromText('POINT ' || '(' || pl_long-3000000 || ' ' || pl_lat || ')', 25833)
from model.reporting_periods rp
where p.pl_koord_act_id = 3
  and p.pl_long > 3000000
  and p.pl_long < 4000000
  and p.date_from = rp.date_from
  and rp.state = 1;

-- Normale Verarbeitung von ETRS89 UTM33:
update plants p
  set geo_etrs89_utm33 = ST_GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 25833)
from model.reporting_periods rp
where p.pl_koord_act_id = 3
  and p.pl_long < 1000000
  and p.date_from = rp.date_from
  and rp.state = 1;

-- BEs Soldner:
update plants p
  set geo_soeldner = ST_GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 3068)
from model.reporting_periods rp
where pl_koord_act_id = 5
  and federal_state_id = 11
  and p.date_from = rp.date_from
  and rp.state = 1;


-- Umrechnen der gelieferten Rechts-/Hochwerte in die jeweilige PostGIS Ausgangsgeometrie
-- und eintragen derselben in die jeweiligen Einleitstellenspalten:
-- GK, 2. Streifen:
update discharge_points dp
  set geo_src_gk2 = ST_GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 31466)
from model.reporting_periods rp
where dp_koord_act_id = 1
  and dp_long >= 2000000
  and dp_long < 3000000
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- GK, 3. Streifen:
update discharge_points dp
  set geo_src_gk3 = ST_GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 31467)
from model.reporting_periods rp
where dp_koord_act_id = 1
  and dp_long >= 3000000
  and dp_long < 4000000
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- GK, 4. Streifen:
update discharge_points dp
  set geo_src_gk4 = ST_GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 31468)
from model.reporting_periods rp
where dp_koord_act_id = 1
  and dp_long >= 4000000
  and dp_long < 5000000
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- GK, 5. Streifen:
update discharge_points dp
  set geo_src_gk5 = ST_GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 31469)
from model.reporting_periods rp
where dp_koord_act_id = 1
  and dp_long >= 5000000
  and dp_long < 6000000
  and dp.date_from = rp.date_from
  and rp.state = 1;


-- Normale Verarbeitung von ETRS89 UTM32:
update discharge_points dp
  set geo_etrs89_utm32 = ST_GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 25832)
from model.reporting_periods rp
where dp_koord_act_id = 2
  and dp.dp_long < 1000000
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- Einige Länder stellen bei Verwendung von ETRS89 UTM32 dem sechstelligen Rechtwert
-- eine 32 voran. Das fangen wir hier ab:
update discharge_points dp
  set geo_etrs89_utm32 = ST_GeometryFromText('POINT ' || '(' || dp_long-32000000 || ' ' || dp_lat || ')', 25832)
from model.reporting_periods rp
where dp.dp_koord_act_id = 2
  and dp.dp_long > 32000000
  and dp.date_from = rp.date_from
  and rp.state = 1;


-- Einige Länder stellen bei Verwendung von ETRS89 UTM33 dem sechstelligen Rechtwert
-- mal eine 3 voran, mal eine 33
-- Hier fangen wir die 33 ab:
update discharge_points dp
  set geo_etrs89_utm33 = ST_GeometryFromText('POINT ' || '(' || dp_long-33000000 || ' ' || dp_lat || ')', 25833)
from model.reporting_periods rp
where dp.dp_koord_act_id = 3
  and dp.dp_long > 33000000
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- Einige Länder stellen bei Verwendung von ETRS89 UTM33 dem sechstelligen Rechtwert
-- mal eine 3 voran, mal eine 33
-- Hier fangen wir die 3 ab:
update discharge_points dp
  set geo_etrs89_utm33 = ST_GeometryFromText('POINT ' || '(' || dp_long-3000000 || ' ' || dp_lat || ')', 25833)
from model.reporting_periods rp
where dp.dp_koord_act_id = 3
  and dp.dp_long > 3000000
  and dp.dp_long < 4000000
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- Normale Verarbeitung von ETRS89 UTM33:
update discharge_points dp
  set geo_etrs89_utm33 = ST_GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 25833)
from model.reporting_periods rp
where dp_koord_act_id = 3
  and dp.dp_long < 1000000
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- BEs Soldner:
update discharge_points dp
  set geo_soeldner = ST_GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 3068)
from model.reporting_periods rp
where dp_koord_act_id = 5
  and federal_state_id = 11
  and dp.date_from = rp.date_from
  and rp.state = 1;


-- Umrechnen der PostGIS Ausgangskoordinaten in die beiden PostGIS Zielkoordinatensysteme WGS84 und ETRS89_UTM32:
-- Agglomerationen
-- GK 2
update agglomerations a
  set geo_wgs84 = st_transform(geo_src_gk2, 4326)
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk2 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89 = st_transform(geo_src_gk2, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk2 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89_utm32 = st_transform(geo_src_gk2, 25832)
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk2 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

-- GK 3
update agglomerations a
  set geo_wgs84 = st_transform(geo_src_gk3, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk3 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89 = st_transform(geo_src_gk3, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk3 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89_utm32 = st_transform(geo_src_gk3, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk3 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

-- GK 4
update agglomerations a
  set geo_wgs84 = st_transform(geo_src_gk4, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk4 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89 = st_transform(geo_src_gk4, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk4 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89_utm32 = st_transform(geo_src_gk4, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk4 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

-- GK 5
update agglomerations a
  set geo_wgs84 = st_transform(geo_src_gk5, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk5 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89 = st_transform(geo_src_gk5, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk5 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89_utm32 = st_transform(geo_src_gk5, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk5 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;


-- ETRS89 UTM32:
update agglomerations a
  set geo_wgs84 = st_transform(geo_etrs89_utm32, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_etrs89_utm32 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89 = st_transform(geo_etrs89_utm32, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_etrs89_utm32 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;


-- BB ETRS89 UTM33:
update agglomerations a
  set geo_wgs84 = st_transform(geo_etrs89_utm33, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_etrs89_utm33 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89 = st_transform(geo_etrs89_utm33, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_etrs89_utm33 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89_utm32 = st_transform(geo_etrs89_utm33, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_etrs89_utm33 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

-- BE (DHDN ->) Soldner
update agglomerations a
  set geo_wgs84 = st_transform(geo_soeldner, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_soeldner is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89 = st_transform(geo_soeldner, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_soeldner is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update agglomerations a
  set geo_etrs89_utm32 = st_transform(geo_soeldner, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_soeldner is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

-- Anlagen
-- GK 2
update plants p
  set geo_wgs84 = st_transform(geo_src_gk2, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk2 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89 = st_transform(geo_src_gk2, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk2 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89_utm32 = st_transform(geo_src_gk2, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk2 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

-- GK 3
update plants p
  set geo_wgs84 = st_transform(geo_src_gk3, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk3 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89 = st_transform(geo_src_gk3, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk3 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89_utm32 = st_transform(geo_src_gk3, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk3 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

-- GK 4
update plants p
  set geo_wgs84 = st_transform(geo_src_gk4, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk4 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89 = st_transform(geo_src_gk4, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk4 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89_utm32 = st_transform(geo_src_gk4, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk4 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

-- GK 5
update plants p
  set geo_wgs84 = st_transform(geo_src_gk5, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk5 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89 = st_transform(geo_src_gk5, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk5 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89_utm32 = st_transform(geo_src_gk5, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk5 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

-- ETRS89 UTM32:
update plants p
  set geo_wgs84 = st_transform(geo_etrs89_utm32, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_etrs89_utm32 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89 = st_transform(geo_etrs89_utm32, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_etrs89_utm32 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

-- BB ETRS89 UTM33:
update plants p
  set geo_wgs84 = st_transform(geo_etrs89_utm33, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_etrs89_utm33 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89 = st_transform(geo_etrs89_utm33, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_etrs89_utm33 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89_utm32 = st_transform(geo_etrs89_utm33, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_etrs89_utm33 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

-- BE (DHDN ->) Soldner
update plants p
  set geo_wgs84 = st_transform(geo_soeldner, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_soeldner is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89 = st_transform(geo_soeldner, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_soeldner is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set geo_etrs89_utm32 = st_transform(geo_soeldner, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_soeldner is not null
  and p.date_from = rp.date_from
  and rp.state = 1;


-- Einleitstellen
-- GK 2
update discharge_points dp
  set geo_wgs84 = st_transform(geo_src_gk2, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk2 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89 = st_transform(geo_src_gk2, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk2 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89_utm32 = st_transform(geo_src_gk2, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk2 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- GK 3
update discharge_points dp
  set geo_wgs84 = st_transform(geo_src_gk3, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk3 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89 = st_transform(geo_src_gk3, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk3 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89_utm32 = st_transform(geo_src_gk3, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk3 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- GK 4
update discharge_points dp
  set geo_wgs84 = st_transform(geo_src_gk4, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk4 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89 = st_transform(geo_src_gk4, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk4 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89_utm32 = st_transform(geo_src_gk4, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk4 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- GK 5
update discharge_points dp
  set geo_wgs84 = st_transform(geo_src_gk5, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_src_gk5 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89 = st_transform(geo_src_gk5, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_src_gk5 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89_utm32 = st_transform(geo_src_gk5, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_src_gk5 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- ETRS89 UTM32:
update discharge_points dp
  set geo_wgs84 = st_transform(geo_etrs89_utm32, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_etrs89_utm32 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89 = st_transform(geo_etrs89_utm32, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_etrs89_utm32 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- BB ETRS89 UTM33:
update discharge_points dp
  set geo_wgs84 = st_transform(geo_etrs89_utm33, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_etrs89_utm33 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89 = st_transform(geo_etrs89_utm33, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_etrs89_utm33 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89_utm32 = st_transform(geo_etrs89_utm33, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_etrs89_utm33 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

-- BE (DHDN ->) Soldner
update discharge_points dp
  set geo_wgs84 = st_transform(geo_soeldner, 4326) 
from model.reporting_periods rp
where geo_wgs84 is null and geo_soeldner is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89 = st_transform(geo_soeldner, 4258)
from model.reporting_periods rp
where geo_etrs89 is null and geo_soeldner is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set geo_etrs89_utm32 = st_transform(geo_soeldner, 25832) 
from model.reporting_periods rp
where geo_etrs89_utm32 is null and geo_soeldner is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;


-- Übertragen der neu gerechneten GIS-Zielgeometriedaten in EU-Rechts-/Hochwerte
update agglomerations a
  set ag_eu_long = st_x(geo_wgs84), ag_eu_lat = st_y(geo_wgs84)
from model.reporting_periods rp
where geo_wgs84 is not null
  and a.date_from = rp.date_from
  and rp.state = 1;

update plants p
  set pl_eu_long = st_x(geo_wgs84), pl_eu_lat = st_y(geo_wgs84)
from model.reporting_periods rp
where geo_wgs84 is not null
  and p.date_from = rp.date_from
  and rp.state = 1;

update discharge_points dp
  set dp_eu_long = st_x(geo_wgs84), dp_eu_lat = st_y(geo_wgs84)
from model.reporting_periods rp
where geo_wgs84 is not null
  and dp.date_from = rp.date_from
  and rp.state = 1;
