# _MSSQLS sqls

sqlstr:
CREATE VIEW [dbo].[NEPUNKT_GEOM] AS
SELECT GENERATION, PUNKTID, XKOOR, YKOOR, ZKOOR, KOORKEY, LASTFALL, HBFLAG, IMPDATE, CHANGED,
geometry::STGeomFromText('POINT(' + STR(XKOOR) + ' ' + STR(YKOOR) + ')', 0) AS geom
FROM  dbo.NEPUNKT
WHERE (XKOOR > 0) AND (YKOOR > 0) AND (GENERATION = 'CALIBRATION')


sqlstr:
CREATE VIEW [dbo].[NEPOLYLINE_TXT] AS
Select distinct
 T2.OBJEKTID,SUBSTRING((
     SELECT ', '+CAST(convert(decimal(15,2),[XKOOR])AS VARCHAR)+ ' ',
        CAST(convert(decimal(15,2),[YKOOR]) AS VARCHAR)            
      FROM [KNJAZEVAC].[dbo].[NEPOLYEINTRAG_GEOM] as T1
      where T1.OBJEKTID = T2.OBJEKTID
      order by T1.OBJEKTID,T1.REIHENFOLGE
      For XML PATH ('')) ,3, 100000)as geo_txt
 From [KNJAZEVAC].[dbo].[NEPOLYEINTRAG_GEOM] as T2


sqlstr:
CREATE VIEW [dbo].[NEPOLYLINE_TXT] AS
SELECT DISTINCT T2.OBJEKTID, t2.GENERATION,  SUBSTRING
                   ((SELECT ', ' + CAST(CONVERT(decimal(15, 2), [XKOOR]) AS VARCHAR) + ' ', CAST(CONVERT(decimal(15, 2), [YKOOR]) AS VARCHAR)
                     FROM   [dbo].[NEPOLYEINTRAG_GEOM] AS T1
                     WHERE T1.OBJEKTID = T2.OBJEKTID
                     ORDER BY T1.OBJEKTID, T1.REIHENFOLGE FOR XML PATH('')), 3, 100000) AS geo_txt
FROM  [dbo].[NEPOLYEINTRAG_GEOM] AS T2


sqlstr:
CREATE VIEW [dbo].[NEPOLYLINE_GEOM] AS
Select
 T2.OBJEKTID, T2.GENERATION , geometry::STGeomFromText('LineString(' + geo_txt + ')', 0) AS geom
 From [dbo].[NEPOLYLINE_TXT] as T2


sqlstr:
CREATE VIEW [dbo].[NESTRANG_GEOM] AS
SELECT dbo.NESTRANG.GENERATION, dbo.NESTRANG.OBJEKTID, dbo.NESTRANG.N1, dbo.NESTRANG.WERKSTOFF, dbo.NESTRANG.NUMMER, 
               dbo.NESTRANG.BEZEICHNUNG, dbo.NESTRANG.RAUHIGKEIT, dbo.NESTRANG.VONKNOTENID, dbo.NESTRANG.NACHKNOTENID, dbo.NESTRANG.LAENGE, 
               dbo.NESTRANG.ART, dbo.NESTRANG.NETZZONE, dbo.NESTRANG.LASTFALL, dbo.NESTRANG.IMPDATE, dbo.NESTRANG.REALLAENGE, 
               dbo.NESTRANG.NENNWEITE, dbo.NESTRANG.LAENGEUSER, dbo.NESTRANG.LAENGEOK, dbo.NESTRANG.CHANGED, dbo.NESTRANG.EXTERNID, 
               dbo.NESTRANG.BAUJAHR, dbo.NESTRANG.INNENBESCHICHTUNG, dbo.NESTRANG.NENNDRUCK, dbo.NESTRANG.WRRATE_CHLOR, 
               dbo.NESTRANG.WRRATE_BEN, dbo.NESTRANG.INNENDURCHMESSER, dbo.NESTRANG.ZUSATZFELD1, dbo.NESTRANG.ZUSATZFELD2, 
               dbo.NEPOLYLINE_GEOM.geom
FROM  dbo.NESTRANG INNER JOIN
               dbo.NEPOLYLINE_GEOM ON dbo.NESTRANG.OBJEKTID = dbo.NEPOLYLINE_GEOM.OBJEKTID AND 
               dbo.NESTRANG.GENERATION = dbo.NEPOLYLINE_GEOM.GENERATION;


sqlstr:
CREATE VIEW [dbo].[NEKNOTEN_GEOM] AS
SELECT NEKNOTEN.*, NEPUNKT_GEOM.geom
FROM NEKNOTEN INNER JOIN NEPUNKT_GEOM ON (NEKNOTEN.GENERATION = NEPUNKT_GEOM.GENERATION) AND (NEKNOTEN.PUNKTID = NEPUNKT_GEOM.PUNKTID);


sqlstr:
CREATE VIEW [dbo].[NEPOLYEINTRAG_GEOM] AS
SELECT dbo.NEPOLYEINTRAG.OBJEKTID, dbo.NEPOLYEINTRAG.REIHENFOLGE, dbo.NEPUNKT_GEOM.XKOOR, dbo.NEPUNKT_GEOM.YKOOR, 
               dbo.NEPUNKT_GEOM.ZKOOR, dbo.NEPUNKT_GEOM.geom, dbo.NEPOLYEINTRAG.GENERATION
FROM  dbo.NEPUNKT_GEOM INNER JOIN
               dbo.NEPOLYEINTRAG ON dbo.NEPOLYEINTRAG.PUNKTID = dbo.NEPUNKT_GEOM.PUNKTID AND 
               dbo.NEPUNKT_GEOM.GENERATION = dbo.NEPOLYEINTRAG.GENERATION


# _PostGIS sqls

sqlstr:
update sewer_network_kanten set "länge_auf_" = round("länge_auf_",2);


sqlstr:
alter table sewer_network.kanten alter column geom type geometry(linestring,31255) using st_geometryN(geom,1)n


sqlstr:
ALTER TABLE sewer_network.knoten
    ALTER COLUMN geom TYPE geometry(Point,31255) USING ST_GeometryN(geom, 1);


sqlstr:
SELECT s_edge_id as id, st_length(geom) as length, 'S_EDGE' as type, geom from sewer_network.s_edges UNION Select id as id,st_length(geom) as length, 'S_TRASSE' as type, geom from sewer_network.trassen UNION Select s_s_con_id as id,st_length(geom) as length, 'HS' as type, geom from sewer_network.s_service_connection


sqlstr:
update sewer_admin.entsorgte_objekte
SET
  "RW" = round(ST_X( ST_GeometryN(geom, 1))), 
  "HW" = round(ST_Y( ST_GeometryN(geom, 1)));


sqlstr:
CREATE TABLE sewer_network.s_picklist
(
  w_picklist_id serial NOT NULL,
  tablename character varying,
  column_name character varying,
  att_abbreviation character varying,
  attribute_value character varying,
  parent_column_name character varying
)


sqlstr:
DROP TABLE kanal_netzwerk.upstream;
Create Table kanal_netzwerk.upstream as
WITH RECURSIVE walk_network(kanten_id, geom) AS (
  SELECT kanten_id, geom 
    FROM kanal_netzwerk.kanten 
    WHERE kanten_id = 33330
  UNION ALL
  SELECT n.kanten_id, n.geom
    FROM kanal_netzwerk.kanten  n, walk_network w
    WHERE ST_Dwithin(
      ST_StartPoint(w.geom),
      ST_EndPoint(n.geom),0.001) 
)
SELECT distinct kanten_id, geom 
FROM walk_network LIMIT 5000


sqlstr:
DROP TABLE kanal_netzwerk.downstream;
Create Table kanal_netzwerk.downstream as
WITH RECURSIVE walk_network(kanten_id, geom) AS (
  SELECT kanten_id, geom 
    FROM kanal_netzwerk.kanten 
    WHERE kanten_id = 34665
  UNION ALL
  SELECT distinct n.kanten_id, n.geom
    FROM kanal_netzwerk.kanten  n, walk_network w
    WHERE ST_DWithin(
      ST_StartPoint(n.geom),
      ST_EndPoint(w.geom),0.1) 
)
SELECT row_number() over() as rid,kanten_id, geom
FROM walk_network LIMIT 100;
ALTER TABLE kanal_netzwerk.downstream
  ADD CONSTRAINT pk_downstream PRIMARY KEY(rid);


sqlstr:
create or replace view kanal_netzwerk.straenge as
select row_number() over() as rowid, strang, st_union(geom) from kanal_netzwerk.kanten where strang is not null group by strang


sqlstr:
CREATE OR REPLACE FUNCTION kanal_netzwerk.on_update_kanten()
  RETURNS trigger AS
$BODY$ BEGIN NEW.modif_d = now(); NEW.modif_i = 'Hugo Fuchs'; 
if ST_EQUALS(NEW.geom, OLD.geom) = 'false' OR ST_EQUALS(ST_STARTPOINT(NEW.geom),ST_STARTPOINT(OLD.geom)) = 'false' THEN 
 NEW.von_knoten = (Select n.knoten_id from kanal_netzwerk.knoten n where ST_INTERSECTS(ST_STARTPOINT(New.GEOM),n.geom) = 'true'); 
NEW.bis_knoten = (Select n.knoten_id from kanal_netzwerk.knoten n where ST_INTERSECTS(ST_ENDPOINT(New.GEOM),n.geom) = 'true'); 
END IF; 

RETURN NEW; END; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


sqlstr:
update  kanal_netzwerk.kanten set  laenge_2d = cast(round(cast(st_length(geom) as numeric),2)as double precision)


sqlstr:
update kanal_netzwerk.kanten e SET bis_knoten = n.knoten_id
FROM kanal_netzwerk.knoten n
WHERE ST_DWITHIN(n.geom, ST_ENDPOINT(e.geom),0.0001)  = true;


sqlstr:
update kanal_netzwerk.kanten e SET von_knoten = n.knoten_id
FROM kanal_netzwerk.knoten n
WHERE ST_DWITHIN(n.geom, ST_STARTPOINT(e.geom),0.0001)  = true;


sqlstr:
CREATE OR REPLACE VIEW kanal_netzwerk.zu_befahren AS 
 SELECT k.kanten_id,
    k.geom,
    g.bez_gebiet
   FROM kanal_netzwerk.kanten k,
    kanal_netzwerk.befahrungsgebiete_2017 g
  WHERE st_coveredby(k.geom, g.geom) = true and typ_leit ='Freispiegelleitung';


sqlstr:
CREATE OR REPLACE VIEW kanal_netzwerk.Schächte_zu_inspizieren AS 
 SELECT k.knoten_id,
    k.geom,
    g.bez_gebiet
   FROM 
kanal_netzwerk.Schächte_öffentlich  k,
    kanal_netzwerk.befahrungsgebiete_2017 g
  WHERE st_coveredby(k.geom, g.geom) = true;


sqlstr:
CREATE OR REPLACE VIEW kanal_netzwerk.Schächte_öffentlich AS 
 SELECT k.knoten_id,
    k.geom,
    g.organisation
   FROM kanal_netzwerk.knoten k,
    dkm.gst_oeffentlich g
  WHERE st_coveredby(k.geom, g.geom) = true;


sqlstr:
UPDATE (pg_kanten INNER JOIN pg_zu_befahren ON pg_kanten.kanten_id = pg_zu_befahren.kanten_id) INNER JOIN pg_schächte_zu_inspizieren ON pg_kanten.von_knoten = pg_schächte_zu_inspizieren.knoten_id SET pg_kanten.temp_sel = TRUE;


sqlstr:
CREATE OR REPLACE VIEW kanal_netzwerk.netzabschnitte AS 
 SELECT row_number() OVER () AS rowid,
    kanten.netzabsch,
    st_union(kanten.geom) AS union_geom
   FROM kanal_netzwerk.kanten
  WHERE kanten.netzabsch IS NOT NULL
  GROUP BY kanten.netzabsch;

ALTER TABLE kanal_netzwerk.netzabschnitte
  OWNER TO postgres;


sqlstr:
CREATE OR REPLACE VIEW kanal_netzwerk.netzabschnitte_buffer AS 
select netzabsch, st_buffer(union_geom, 100, 'endcap=round join=round') from kanal_netzwerk.netzabschnitte


sqlstr:
ALTER TABLE kanal_netzwerk.kanten
  ADD CONSTRAINT kanten_kanten_uuid_key UNIQUE(kanten_uuid);


sqlstr:
CREATE TABLE dkm.street_buffer as
SELECT row_number() over() as id, name1, streetcat, funcroadcl,  
ST_BUFFER(ST_UNION(geom), (SELECT case when funcroadcl <=2 THEN 25
when funcroadcl = 3 THEN 20
when funcroadcl = 4 THEN 15
when funcroadcl >4 and funcroadcl <=11 THEN 10
ELSE 5
END 
)) as geom, count(id) FROM dkm.strassen  WHERE name1 IS NOT NULL AND streetcat NOT IN('EH','EN')
 GROUP BY name1, streetcat, funcroadcl ORDER BY funcroadcl ASC


sqlstr:
CREATE TABLE dkm.kanten_strassen as
SELECT DISTINCT ON(k.kanten_id)
 k.kanten_id,
 s.name1,
 ST_LENGTH(ST_INTERSECTION(k.geom, s.geom)) as length_interset,
 ST_LENGTH(k.geom) as length,
 ST_LENGTH(ST_INTERSECTION(k.geom, s.geom)) / ST_LENGTH(k.geom) as fract, 
 k.geom
 FROM kanal_netzwerk.kanten k, dkm.street_buffer s
 WHERE ST_INTERSECTS(k.geom, s.geom) = 't'
 ORDER BY k.kanten_id, ST_LENGTH(ST_Intersection(k.geom, s.geom)) DESC


funktion:
update kanal_netzwerk.kanten set geom = ST_RemoveRepeatedPoints ( geom)

sqlstr:
update kanal_netzwerk.kanten set geom = ST_RemoveRepeatedPoints ( geom)


# pg_kanten

infofeld:
PVC-Halbschale in der Absturzpfeife verkeilt


infofeld:
HNr.Gotenw. 1,3,5,Keltenstr. 77 zu Kanal


# Schächte

Beschreibung:
DN 1000        beim Gerinne auf 6 Uhr Wurzleinwuchs


Beschreibung:
DN 1000   Schachtaufbau Wurzeln auf 8 Uhr


Beschreibung:
DN 1000    Auftritt Wurzeln von 1 bis 5 Uhr  Schachtaufbau Wurzeln auf 11 Uhr      Steigeisen alle gebrochen


Beschreibung:
DN 1000   Abdeckung 10 cm unter Geländeoberkante


Beschreibung:
DN 1000   geschlossene Rohrdurchfürung mit Putzöffnung


Beschreibung:
DN 800     Abdeckung  Einlaufgitter     Auftritt gebrochen


Beschreibung:
DN 800  Abdeckung  Einlaufgitter  Auflagering gebrochen


Beschreibung:
DN 1000  Rahmen verschoben  Mitte Schacht befindet sich ein Rohr PVC 300


Beschreibung:
DN 1000  geschlossene Rohrdurchfürung mit Putzöffnung


Beschreibung:
DN 1000   geschlossene Rohrdurchführung mit Putzöffnung Abdeckung fehlt


Beschreibung:
DN 1000               Konus verschoben auf 08:00 Uhr


Beschreibung:
DN 1000    Auflagering u. Konus gebrochen


Beschreibung:
DN 1000     Auftritt Loch Boden sichtbar


Beschreibung:
DN 1000       Beton ausgeschwemmt Loch Boden sichtbar


Beschreibung:
DN 1000   Schachtaufbau Ringe verschoben


Beschreibung:
DN 1000  Auflagering und Konus gebrochen


Beschreibung:
DN 1000   Gerinne stark ausgeschwemmt Boden sichtbar


Beschreibung:
DN 1000     bei Anschluss 09:00 Uhr ausgebrochen


Beschreibung:
DN 1000       Sohle und Gerinne stark ausgeschwemmt


Beschreibung:
DN 1000  Auftritt und Sohle Loch Boden sichtbar bei HA auf 1:00 Uhr Rückstauklappe


Beschreibung:
DN 1000    Auflagering gebrochen   HA 11:00 Uhr Rückstauklappe


Beschreibung:
DN 1000        Sohle ausgeschwemmt Boden sichtbar


Beschreibung:
DN 1000        Steigeisen alle gebrochen


Beschreibung:
DN 1000      Gerinne Oberfläche beschädigt


Beschreibung:
DN 1000  Anschluss auf 7 Uhr Rückstauklappe


Beschreibung:
DN 1000    Abdeckung 10 cm unter Oberkante


Beschreibung:
DN 1000    Abdeckung  Einlaufgitter   Auftritt und Gerinne beschädigt


Beschreibung:
DN 1000  Auflagering verschoben   Gerinne großes Loch


Beschreibung:
DN 1000   Abdeckung 10 cm unter Oberkannte


Beschreibung:
DN 1000     Abdeckung ist 50 cm unter Oberkante


Beschreibung:
DN 1000  Auflagering gebrochen  Konus verschoben Schacht unten versetzt


Beschreibung:
DN 1000   Auftritt stark beschädigt  Anschluss nicht fachgerecht


Beschreibung:
DN 1000   Auftritt ausgeschwaschen   Sohle nicht fachgerecht montiert


