-- gis_spalten_erzeugen.sql
--
-- Skript zur Ausstattung einer Kommunalabwasserdatenbank alten Zuschnitts (ohne GIS) mit
-- den für die Koordinatenumrechnung notwendigen PostGIS Spalten
--
-- (c) ENDA GmbH & Co. KG, 2010
--
-- Rev. 3, 2012-03-12, mlt: Schreibfehler "Söldner" nach "Soldner" korrigiert
--                          Neues Skript "koordinatenberechnung.sql" zur Berechnung der
--                          aktuellen Koordinaten erstellt. Hinweis: Die EU bekommt WGS84!
-- Rev. 2, 2010-05-05, mlt: Kommentar zu Brandenburg-Koordinaten aktualisiert
-- Rev. 1, 2010-04-12, mlt: Ergänzung der Geometrie 4258, ETRS89 unprojiziert
-- Rev. 0, 2010-04-12, mlt: Erste Fassung


-- Geometriespalten den Agglomerationen hinzufügen
SELECT AddGeometryColumn('public','agglomerations','geo_wgs84',4326,'POINT',2);
SELECT AddGeometryColumn('public','agglomerations','geo_etrs89',4258,'POINT',2);
SELECT AddGeometryColumn('public','agglomerations','geo_etrs89_utm32',25832,'POINT',2);

SELECT AddGeometryColumn('public','agglomerations','geo_src_gk2',31466,'POINT',2);
SELECT AddGeometryColumn('public','agglomerations','geo_src_gk3',31467,'POINT',2);
SELECT AddGeometryColumn('public','agglomerations','geo_src_gk4',31468,'POINT',2);
SELECT AddGeometryColumn('public','agglomerations','geo_src_gk5',31469,'POINT',2);
SELECT AddGeometryColumn('public','agglomerations','geo_etrs89_utm33',25833,'POINT',2);
SELECT AddGeometryColumn('public','agglomerations','geo_soeldner',3068,'POINT',2);

-- Geometriespalten den Plants hinzufügen
SELECT AddGeometryColumn('public','plants','geo_wgs84',4326,'POINT',2);
SELECT AddGeometryColumn('public','plants','geo_etrs89',4258,'POINT',2);
SELECT AddGeometryColumn('public','plants','geo_etrs89_utm32',25832,'POINT',2);

SELECT AddGeometryColumn('public','plants','geo_src_gk2',31466,'POINT',2);
SELECT AddGeometryColumn('public','plants','geo_src_gk3',31467,'POINT',2);
SELECT AddGeometryColumn('public','plants','geo_src_gk4',31468,'POINT',2);
SELECT AddGeometryColumn('public','plants','geo_src_gk5',31469,'POINT',2);
SELECT AddGeometryColumn('public','plants','geo_etrs89_utm33',25833,'POINT',2);
SELECT AddGeometryColumn('public','plants','geo_soeldner',3068,'POINT',2);

-- Geometriespalten den Discharge Points hinzufügen
SELECT AddGeometryColumn('public','discharge_points','geo_wgs84',4326,'POINT',2);
SELECT AddGeometryColumn('public','discharge_points','geo_etrs89',4258,'POINT',2);
SELECT AddGeometryColumn('public','discharge_points','geo_etrs89_utm32',25832,'POINT',2);

SELECT AddGeometryColumn('public','discharge_points','geo_src_gk2',31466,'POINT',2);
SELECT AddGeometryColumn('public','discharge_points','geo_src_gk3',31467,'POINT',2);
SELECT AddGeometryColumn('public','discharge_points','geo_src_gk4',31468,'POINT',2);
SELECT AddGeometryColumn('public','discharge_points','geo_src_gk5',31469,'POINT',2);
SELECT AddGeometryColumn('public','discharge_points','geo_etrs89_utm33',25833,'POINT',2);
SELECT AddGeometryColumn('public','discharge_points','geo_soeldner',3068,'POINT',2);


-- Löschen aller PostGIS Geometriespalten:
-- -- Ausgangsgeometrien:
-- update agglomerations set geo_src_gk2 = null;
-- update agglomerations set geo_src_gk3 = null;
-- update agglomerations set geo_src_gk4 = null;
-- update agglomerations set geo_src_gk5 = null;
-- update agglomerations set geo_etrs89_utm33 = null;
-- update agglomerations set geo_soeldner = null;
-- 
-- -- Zielgeometrien:
-- update agglomerations set geo_wgs84 = null;
-- update agglomerations set geo_etrs89 = null;
-- update agglomerations set geo_etrs89_utm32 = null;
-- 
-- -- Ausgangsgeometrien:
-- update plants set geo_src_gk2 = null;
-- update plants set geo_src_gk3 = null;
-- update plants set geo_src_gk4 = null;
-- update plants set geo_src_gk5 = null;
-- update plants set geo_etrs89_utm33 = null;
-- update plants set geo_soeldner = null;
-- 
-- -- Zielgeometrien:
-- update plants set geo_wgs84 = null;
-- update plants set geo_etrs89 = null;
-- update plants set geo_etrs89_utm32 = null;
-- 
-- -- Ausgangsgeometrien:
-- update discharge_points set geo_src_gk2 = null;
-- update discharge_points set geo_src_gk3 = null;
-- update discharge_points set geo_src_gk4 = null;
-- update discharge_points set geo_src_gk5 = null;
-- update discharge_points set geo_etrs89_utm33 = null;
-- update discharge_points set geo_soeldner = null;
-- 
-- -- Zielgeometrien:
-- update discharge_points set geo_wgs84 = null;
-- update discharge_points set geo_etrs89 = null;
-- update discharge_points set geo_etrs89_utm32 = null;

-- Alternativ alle auf einmal:
update agglomerations 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;
update plants 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;
update discharge_points 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;



-- Umrechnen der gelieferten Rechts-/Hochwerte in die jeweilige PostGIS Ausgangsgeometrie
-- und eintragen derselben in die jeweiligen Agglomerationsspalten:
-- GK, 2. Streifen:
update agglomerations set geo_src_gk2 = GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 31466)
where ag_koord_act_id = 1 and pl_long >= 2000000 and pl_long < 3000000;

-- GK, 3. Streifen:
update agglomerations set geo_src_gk3 = GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 31467)
where ag_koord_act_id = 1 and pl_long >= 3000000 and pl_long < 4000000;

-- GK, 4. Streifen:
update agglomerations set geo_src_gk4 = GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 31468)
where ag_koord_act_id = 1 and pl_long >= 4000000 and pl_long < 5000000;

-- GK, 5. Streifen:
update agglomerations set geo_src_gk5 = GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 31469)
where ag_koord_act_id = 1 and pl_long >= 5000000 and pl_long < 6000000;

-- BBs ETRS89 UTM33 (Die '''@#BlitzDerberFluch Fachleute''' in Brandenburg stellen dem sechstelligen Rechtwert nach ETRS89UTM33 eine 3 voran, diesmal sogar eine 33!!!):
update agglomerations set geo_etrs89_utm33 = GeometryFromText('POINT ' || '(' || pl_long-33000000 || ' ' || pl_lat || ')', 25833)
where ag_koord_act_id = 3 and federal_state_id = 12;

update agglomerations set geo_etrs89_utm33 = GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 25833)
where ag_koord_act_id = 3 and federal_state_id <> 12;

-- BEs Soldner:
update agglomerations set geo_soeldner = GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 3068)
where ag_koord_act_id = 5 and federal_state_id = 11;


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

-- GK, 3. Streifen:
update plants set geo_src_gk3 = GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 31467)
where pl_koord_act_id = 1 and pl_long >= 3000000 and pl_long < 4000000;

-- GK, 4. Streifen:
update plants set geo_src_gk4 = GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 31468)
where pl_koord_act_id = 1 and pl_long >= 4000000 and pl_long < 5000000;

-- GK, 5. Streifen:
update plants set geo_src_gk5 = GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 31469)
where pl_koord_act_id = 1 and pl_long >= 5000000 and pl_long < 6000000;

-- BBs ETRS89 UTM33 (Die '''@#BlitzDerberFluch Fachleute''' in Brandenburg stellen dem sechstelligen Rechtwert nach ETRS89UTM33 eine 3 voran, diesmal sogar eine 33!!!):
update plants set geo_etrs89_utm33 = GeometryFromText('POINT ' || '(' || pl_long-33000000 || ' ' || pl_lat || ')', 25833)
where pl_koord_act_id = 3 and federal_state_id = 12;

update plants set geo_etrs89_utm33 = GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 25833)
where pl_koord_act_id = 3 and federal_state_id <> 12;

-- BEs Soldner:
update plants set geo_soeldner = GeometryFromText('POINT ' || '(' || pl_long || ' ' || pl_lat || ')', 3068)
where pl_koord_act_id = 5 and federal_state_id = 11;


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

-- GK, 3. Streifen:
update discharge_points set geo_src_gk3 = GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 31467)
where dp_koord_act_id = 1 and dp_long >= 3000000 and dp_long < 4000000;

-- GK, 4. Streifen:
update discharge_points set geo_src_gk4 = GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 31468)
where dp_koord_act_id = 1 and dp_long >= 4000000 and dp_long < 5000000;

-- GK, 5. Streifen:
update discharge_points set geo_src_gk5 = GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 31469)
where dp_koord_act_id = 1 and dp_long >= 5000000 and dp_long < 6000000;

-- BBs ETRS89 UTM33 (Die '''@#BlitzDerberFluch Fachleute''' in Brandenburg stellen dem sechstelligen Rechtwert nach ETRS89UTM33 eine 3, diesmal sogar eine 33 voran!!!):
update discharge_points set geo_etrs89_utm33 = GeometryFromText('POINT ' || '(' || dp_long-33000000 || ' ' || dp_lat || ')', 25833)
where dp_koord_act_id = 3 and federal_state_id = 12;

update discharge_points set geo_etrs89_utm33 = GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 25833)
where dp_koord_act_id = 3 and federal_state_id <> 12;

-- BEs Soldner:
update discharge_points set geo_soeldner = GeometryFromText('POINT ' || '(' || dp_long || ' ' || dp_lat || ')', 3068)
where dp_koord_act_id = 5 and federal_state_id = 11;


-- Umrechnen der PostGIS Ausgangskoordinaten in die beiden PostGIS Zielkoordinatensysteme WGS84 und ETRS89_UTM32:
-- Agglomerationen
-- GK 2
UPDATE agglomerations SET geo_wgs84 = st_transform(geo_src_gk2, 4326)
  where geo_wgs84 is null and geo_src_gk2 is not null;
UPDATE agglomerations SET geo_etrs89 = st_transform(geo_src_gk2, 4258)
  where geo_etrs89 is null and geo_src_gk2 is not null;
UPDATE agglomerations SET geo_etrs89_utm32 = st_transform(geo_src_gk2, 25832)
  where geo_etrs89_utm32 is null and geo_src_gk2 is not null;

-- GK 3
UPDATE agglomerations SET geo_wgs84 = st_transform(geo_src_gk3, 4326) 
  where geo_wgs84 is null and geo_src_gk3 is not null;
UPDATE agglomerations SET geo_etrs89 = st_transform(geo_src_gk3, 4258)
  where geo_etrs89 is null and geo_src_gk3 is not null;
UPDATE agglomerations SET geo_etrs89_utm32 = st_transform(geo_src_gk3, 25832) 
  where geo_etrs89_utm32 is null and geo_src_gk3 is not null;

-- GK 4
UPDATE agglomerations SET geo_wgs84 = st_transform(geo_src_gk4, 4326) 
  where geo_wgs84 is null and geo_src_gk4 is not null;
UPDATE agglomerations SET geo_etrs89 = st_transform(geo_src_gk4, 4258)
  where geo_etrs89 is null and geo_src_gk4 is not null;
UPDATE agglomerations SET geo_etrs89_utm32 = st_transform(geo_src_gk4, 25832) 
  where geo_etrs89_utm32 is null and geo_src_gk4 is not null;

-- GK 5
UPDATE agglomerations SET geo_wgs84 = st_transform(geo_src_gk5, 4326) 
  where geo_wgs84 is null and geo_src_gk5 is not null;
UPDATE agglomerations SET geo_etrs89 = st_transform(geo_src_gk5, 4258)
  where geo_etrs89 is null and geo_src_gk5 is not null;
UPDATE agglomerations SET geo_etrs89_utm32 = st_transform(geo_src_gk5, 25832) 
  where geo_etrs89_utm32 is null and geo_src_gk5 is not null;

-- BB ETRS89 UTM33:
UPDATE agglomerations SET geo_wgs84 = st_transform(geo_etrs89_utm33, 4326) 
  where geo_wgs84 is null and geo_etrs89_utm33 is not null;
UPDATE agglomerations SET geo_etrs89 = st_transform(geo_etrs89_utm33, 4258)
  where geo_etrs89 is null and geo_etrs89_utm33 is not null;
UPDATE agglomerations SET geo_etrs89_utm32 = st_transform(geo_etrs89_utm33, 25832) 
  where geo_etrs89_utm32 is null and geo_etrs89_utm33 is not null;

-- BE (DHDN ->) Soldner
UPDATE agglomerations SET geo_wgs84 = st_transform(geo_soeldner, 4326) 
  where geo_wgs84 is null and geo_soeldner is not null;
UPDATE agglomerations SET geo_etrs89 = st_transform(geo_soeldner, 4258)
  where geo_etrs89 is null and geo_soeldner is not null;
UPDATE agglomerations SET geo_etrs89_utm32 = st_transform(geo_soeldner, 25832) 
  where geo_etrs89_utm32 is null and geo_soeldner is not null;

-- Anlagen
-- GK 2
UPDATE plants SET geo_wgs84 = st_transform(geo_src_gk2, 4326) 
  where geo_wgs84 is null and geo_src_gk2 is not null;
UPDATE plants SET geo_etrs89 = st_transform(geo_src_gk2, 4258)
  where geo_etrs89 is null and geo_src_gk2 is not null;
UPDATE plants SET geo_etrs89_utm32 = st_transform(geo_src_gk2, 25832) 
  where geo_etrs89_utm32 is null and geo_src_gk2 is not null;

-- GK 3
UPDATE plants SET geo_wgs84 = st_transform(geo_src_gk3, 4326) 
  where geo_wgs84 is null and geo_src_gk3 is not null;
UPDATE plants SET geo_etrs89 = st_transform(geo_src_gk3, 4258)
  where geo_etrs89 is null and geo_src_gk3 is not null;
UPDATE plants SET geo_etrs89_utm32 = st_transform(geo_src_gk3, 25832) 
  where geo_etrs89_utm32 is null and geo_src_gk3 is not null;

-- GK 4
UPDATE plants SET geo_wgs84 = st_transform(geo_src_gk4, 4326) 
  where geo_wgs84 is null and geo_src_gk4 is not null;
UPDATE plants SET geo_etrs89 = st_transform(geo_src_gk4, 4258)
  where geo_etrs89 is null and geo_src_gk4 is not null;
UPDATE plants SET geo_etrs89_utm32 = st_transform(geo_src_gk4, 25832) 
  where geo_etrs89_utm32 is null and geo_src_gk4 is not null;

-- GK 5
UPDATE plants SET geo_wgs84 = st_transform(geo_src_gk5, 4326) 
  where geo_wgs84 is null and geo_src_gk5 is not null;
UPDATE plants SET geo_etrs89 = st_transform(geo_src_gk5, 4258)
  where geo_etrs89 is null and geo_src_gk5 is not null;
UPDATE plants SET geo_etrs89_utm32 = st_transform(geo_src_gk5, 25832) 
  where geo_etrs89_utm32 is null and geo_src_gk5 is not null;

-- BB ETRS89 UTM33:
UPDATE plants SET geo_wgs84 = st_transform(geo_etrs89_utm33, 4326) 
  where geo_wgs84 is null and geo_etrs89_utm33 is not null;
UPDATE plants SET geo_etrs89 = st_transform(geo_etrs89_utm33, 4258)
  where geo_etrs89 is null and geo_etrs89_utm33 is not null;
UPDATE plants SET geo_etrs89_utm32 = st_transform(geo_etrs89_utm33, 25832) 
  where geo_etrs89_utm32 is null and geo_etrs89_utm33 is not null;

-- BE (DHDN ->) Soldner
UPDATE plants SET geo_wgs84 = st_transform(geo_soeldner, 4326) 
  where geo_wgs84 is null and geo_soeldner is not null;
UPDATE plants SET geo_etrs89 = st_transform(geo_soeldner, 4258)
  where geo_etrs89 is null and geo_soeldner is not null;
UPDATE plants SET geo_etrs89_utm32 = st_transform(geo_soeldner, 25832) 
  where geo_etrs89_utm32 is null and geo_soeldner is not null;


-- Einleitstellen
-- GK 2
UPDATE discharge_points SET geo_wgs84 = st_transform(geo_src_gk2, 4326) 
  where geo_wgs84 is null and geo_src_gk2 is not null;
UPDATE discharge_points SET geo_etrs89 = st_transform(geo_src_gk2, 4258)
  where geo_etrs89 is null and geo_src_gk2 is not null;
UPDATE discharge_points SET geo_etrs89_utm32 = st_transform(geo_src_gk2, 25832) 
  where geo_etrs89_utm32 is null and geo_src_gk2 is not null;

-- GK 3
UPDATE discharge_points SET geo_wgs84 = st_transform(geo_src_gk3, 4326) 
  where geo_wgs84 is null and geo_src_gk3 is not null;
UPDATE discharge_points SET geo_etrs89 = st_transform(geo_src_gk3, 4258)
  where geo_etrs89 is null and geo_src_gk3 is not null;
UPDATE discharge_points SET geo_etrs89_utm32 = st_transform(geo_src_gk3, 25832) 
  where geo_etrs89_utm32 is null and geo_src_gk3 is not null;

-- GK 4
UPDATE discharge_points SET geo_wgs84 = st_transform(geo_src_gk4, 4326) 
  where geo_wgs84 is null and geo_src_gk4 is not null;
UPDATE discharge_points SET geo_etrs89 = st_transform(geo_src_gk4, 4258)
  where geo_etrs89 is null and geo_src_gk4 is not null;
UPDATE discharge_points SET geo_etrs89_utm32 = st_transform(geo_src_gk4, 25832) 
  where geo_etrs89_utm32 is null and geo_src_gk4 is not null;

-- GK 5
UPDATE discharge_points SET geo_wgs84 = st_transform(geo_src_gk5, 4326) 
  where geo_wgs84 is null and geo_src_gk5 is not null;
UPDATE discharge_points SET geo_etrs89 = st_transform(geo_src_gk5, 4258)
  where geo_etrs89 is null and geo_src_gk5 is not null;
UPDATE discharge_points SET geo_etrs89_utm32 = st_transform(geo_src_gk5, 25832) 
  where geo_etrs89_utm32 is null and geo_src_gk5 is not null;

-- BB ETRS89 UTM33:
UPDATE discharge_points SET geo_wgs84 = st_transform(geo_etrs89_utm33, 4326) 
  where geo_wgs84 is null and geo_etrs89_utm33 is not null;
UPDATE discharge_points SET geo_etrs89 = st_transform(geo_etrs89_utm33, 4258)
  where geo_etrs89 is null and geo_etrs89_utm33 is not null;
UPDATE discharge_points SET geo_etrs89_utm32 = st_transform(geo_etrs89_utm33, 25832) 
  where geo_etrs89_utm32 is null and geo_etrs89_utm33 is not null;

-- BE (DHDN ->) Soldner
UPDATE discharge_points SET geo_wgs84 = st_transform(geo_soeldner, 4326) 
  where geo_wgs84 is null and geo_soeldner is not null;
UPDATE discharge_points SET geo_etrs89 = st_transform(geo_soeldner, 4258)
  where geo_etrs89 is null and geo_soeldner is not null;
UPDATE discharge_points SET geo_etrs89_utm32 = st_transform(geo_soeldner, 25832) 
  where geo_etrs89_utm32 is null and geo_soeldner is not null;


-- Übertragen der neu gerechneten GIS-Zielgeometriedaten in EU-Rechts-/Hochwerte
update agglomerations set ag_long_eu = st_x(geo_etrs89), ag_lat_eu = st_y(geo_etrs89)
where geo_etrs89 is not null
  and date_from = 20060101;

update plants set pl_eu_long = st_x(geo_etrs89), pl_eu_lat = st_y(geo_etrs89)
where geo_etrs89 is not null
  and date_from = 20060101;

update discharge_points set dp_eu_long = st_x(geo_etrs89), dp_eu_lat = st_y(geo_etrs89)
where geo_etrs89 is not null
  and date_from = 20060101;
