gis Database

Parameters

Name	Type	Mode
in_source_historique	text	IN
in_code_chantier	text	IN
nettoyer_chantier	boolean	IN
in_type	text	IN

Definition

DECLARE
  sql_template text;
BEGIN

IF (in_type = 'ass') THEN

-- Suppression des données précédemment importées
    	IF nettoyer_chantier IS TRUE THEN
        	DELETE FROM raepa.raepa_apparass_p WHERE _source_historique = in_source_historique AND _code_chantier = in_code_chantier;
        	DELETE FROM raepa.raepa_canalass_l WHERE _source_historique = in_source_historique AND _code_chantier = in_code_chantier;
        	DELETE FROM raepa.raepa_ouvrass_p WHERE _source_historique = in_source_historique AND _code_chantier = in_code_chantier;
        	DELETE FROM raepa.raepa_reparass_p WHERE _source_historique = in_source_historique AND _code_chantier = in_code_chantier;
    	END IF;

-- Mise à jour des champs dans les tables d'import
        UPDATE imports.ouvrages_ass
        SET "_temp_data" = hstore(array['code_ouvrage', idouvrage])
        ;
        UPDATE imports.appareils_ass
        SET "_temp_data" = hstore(ARRAY['code_appareil', idappareil, 'code_ouvrage', idouvrage])
        ;
        UPDATE imports.canalisations_ass
        SET "_temp_data" = hstore(array['code_canalisation',idcana,'ouvrage_amont', idnini, 'ouvrage_aval', idnterm] )
		;
		UPDATE imports.reparation_ass
        SET "_temp_data" = hstore(array['code_reparation', idrepar, 'code_support', idsuprepar] );

-- Insertion des données dans raepa
        -- ouvrages
        INSERT INTO raepa.raepa_ouvrass_p (
            typreseau, fnouvass,
            z, _ztampon, _angletampon,
            qualglocxy, qualglocz, anfinpose,
            _source_historique, _code_chantier, _date_import,
            _temp_data,
            geom
        )
        SELECT DISTINCT
            o.typreseau, o.fnouvass,
            o.z, o._ztampon, o._angletampon,
            -- degrees(o.ang_ouv) * -1 AS _angletampon,
            qualglocxy, qualglocz, anfinpose,
            _source_historique, _code_chantier, _date_import,
            _temp_data,
            o.geom
        FROM imports.ouvrages_ass o
        ;

-- appareils
        INSERT INTO raepa.raepa_apparass_p (
            typreseau, fnappass,
            diametre, z,
            idouvrage,
            qualglocxy, qualglocz, anfinpose,
            _source_historique, _code_chantier, _date_import,
            _temp_data,
            geom
        )
        SELECT DISTINCT
            a.typreseau, a.fnappass,
            a.diametre, a.z,
            (SELECT o.idouvrage FROM raepa.raepa_ouvrass_p o WHERE o."_temp_data"->'code_ouvrage' = a."_temp_data"->'code_ouvrage' LIMIT 1) AS idouvrage,
            qualglocxy, qualglocz, anfinpose,
            _source_historique, _code_chantier, _date_import,
            _temp_data,
            a.geom
        FROM imports.appareils_ass a
        ;

-- canalisations
        INSERT INTO raepa.raepa_canalass_l(
            branchemnt, typreseau, contcanass, fonccanass, materiau,
            _forme, diametre, _dimensionhorizontale,
            modecirc, nbranche,
            qualglocxy, qualglocz, anfinpose, _etat,
            _source_historique, _code_chantier, _date_import,
            _precisionannee,
			zamont, zaval,
            idnini, idnterm,
            _temp_data,
            geom
        )
        SELECT DISTINCT
            c.branchemnt, c.typreseau, c.typreseau AS contcanass, '02', c.materiau,
            c._forme, c.diametre, c._dimensionhorizontale,
            c.modecirc, c.nbranche,
            c.qualglocxy, c.qualglocz, c.anfinpose, c._etat,
            c._source_historique, c._code_chantier, c._date_import,
            c._precisionannee,
			c.zamont, c.zaval,
            o1.idouvrage AS idnini,
            o2.idouvrage AS idterm,
            c._temp_data,

-- On retourne les canalisations dans le mauvais sens
            CASE
                WHEN
                    ST_Dwithin(ST_EndPoint(c.geom), o1.geom, 0.05)
                    OR ST_Dwithin(ST_StartPoint(c.geom), o2.geom, 0.05)
                THEN ST_Reverse(c.geom)
                ELSE c.geom
            END AS geom
        FROM imports.canalisations_ass AS c
        LEFT JOIN raepa.raepa_ouvrass_p o1
            ON TRUE
                AND o1._source_historique = in_source_historique
                AND o1._code_chantier = in_code_chantier
                AND o1."_temp_data"->'code_ouvrage' = c."_temp_data"->'ouvrage_amont'
        LEFT JOIN raepa.raepa_ouvrass_p o2
            ON TRUE
                AND o2._source_historique = in_source_historique
                AND o2._code_chantier = in_code_chantier
                AND o2."_temp_data"->'code_ouvrage' = c."_temp_data"->'ouvrage_aval'
        WHERE TRUE
        ;

-- Modification des ouvrages pour modifier les cana amont et aval
        WITH a AS (
            SELECT
                oo.idouvrage,
                COALESCE(string_agg(c.idcana, ',' ORDER BY idcana), 'INCONNU') AS idcanamont
            FROM raepa.raepa_ouvrass_p AS oo
            LEFT JOIN raepa.raepa_canalass_l AS c
            ON ST_DWithin(ST_EndPoint(c.geom), oo.geom, 0.05)
            WHERE TRUE
            AND idcanamont = 'INCONNU'
            AND oo._source_historique = in_source_historique AND oo._code_chantier = in_code_chantier
            GROUP BY idouvrage
        )
        UPDATE raepa.raepa_ouvrass_p AS o
        SET idcanamont = a.idcanamont
        FROM a
        WHERE TRUE
        AND a.idouvrage = o.idouvrage
        AND o.idcanamont = 'INCONNU'
        ;

WITH a AS (
            SELECT
                oo.idouvrage,
                COALESCE(string_agg(c.idcana, ',' ORDER BY idcana), 'INCONNU') AS idcanaval
            FROM raepa.raepa_ouvrass_p AS oo
            LEFT JOIN raepa.raepa_canalass_l AS c
            ON ST_DWithin(ST_StartPoint(c.geom), oo.geom, 0.05)
            WHERE TRUE
            AND idcanaval = 'INCONNU'
            AND oo._source_historique = in_source_historique AND oo._code_chantier = in_code_chantier
            GROUP BY idouvrage
        )
        UPDATE raepa.raepa_ouvrass_p AS o
        SET idcanaval = a.idcanaval
        FROM a
        WHERE TRUE
        AND a.idouvrage = o.idouvrage
        AND o.idcanaval = 'INCONNU'
        ;

-- reparation
        INSERT INTO raepa.raepa_reparass_p (
            supprepare, defreparee,
            idsuprepar,
			daterepar, mouvrage, _typeintervention,
            _source_historique, _code_chantier, _temp_data,
            geom
        )
        SELECT DISTINCT
            r.supprepare, r.defreparee,
            CASE
                WHEN r.supprepare = '01'
					THEN (SELECT c.idcana FROM raepa.raepa_canalass_l c WHERE c."_temp_data"->'code_canalisation' = r."_temp_data"->'code_support' LIMIT 1)
				WHEN r.supprepare = '02'
					THEN (SELECT c.idappareil FROM raepa.raepa_apparass_p c WHERE c."_temp_data"->'code_appareil' = r."_temp_data"->'code_support' LIMIT 1)
				WHEN r.supprepare = '03'
					THEN (SELECT c.idouvrage FROM raepa.raepa_ouvrass_p c WHERE c."_temp_data"->'code_ouvrage' = r."_temp_data"->'code_support' LIMIT 1)
				ELSE 'INCONNU' END AS idsuprepar,
			r.daterepar, r.mouvrage, r._typeintervention,
            _source_historique, _code_chantier, r._temp_data,
            r.geom
        FROM imports.reparation_ass r
        ;

ELSE

-- Suppression des données précédemment importées
		IF nettoyer_chantier IS TRUE THEN
		DELETE FROM raepa.raepa_apparaep_p WHERE _source_historique = in_source_historique AND _code_chantier = in_code_chantier;
        DELETE FROM raepa.raepa_canalaep_l WHERE _source_historique = in_source_historique AND _code_chantier = in_code_chantier;
        DELETE FROM raepa.raepa_ouvraep_p WHERE _source_historique = in_source_historique AND _code_chantier = in_code_chantier;
        DELETE FROM raepa.raepa_reparaep_p WHERE _source_historique = in_source_historique AND _code_chantier = in_code_chantier;
		END IF;

-- Mise à jour des champs dans les tables d'import
        UPDATE imports.ouvrages_aep
        SET "_temp_data" = hstore(array['code_ouvrage', idouvrage])
        ;
        UPDATE imports.appareils_aep
        SET "_temp_data" = hstore(ARRAY['code_appareil', idappareil, 'code_ouvrage', idouvrage])
        ;
        UPDATE imports.canalisations_aep
        SET "_temp_data" = hstore(array['ouvrage_amont', idnini, 'ouvrage_aval', idnterm] )
		;
		UPDATE imports.reparation_aep
        SET "_temp_data" = hstore(array['code_reparation', idrepar, 'code_support', idsuprepar] );

-- Insertion des données dans raepa
        -- ouvrages
        INSERT INTO raepa.raepa_ouvraep_p (
            fnouvaep,
            z,
            qualglocxy, qualglocz, anfinpose,
            _source_historique, _code_chantier, _date_import,
            _temp_data,
            geom
        )
        SELECT DISTINCT
            o.fnouvaep,
            o.z,
            -- degrees(o.ang_ouv) * -1 AS _angletampon,
            qualglocxy, qualglocz, anfinpose,
            _source_historique, _code_chantier, _date_import,
            _temp_data,
            o.geom
        FROM imports.ouvrages_aep o
        ;

-- appareils
        INSERT INTO raepa.raepa_apparaep_p (
            fnappaep,
            diametre, z,
            idouvrage,
            qualglocxy, qualglocz, anfinpose,
            _source_historique, _code_chantier, _date_import,
            _temp_data,
            geom
        )
        SELECT DISTINCT
            a.fnappaep,
            a.diametre, a.z,
            (SELECT o.idouvrage FROM raepa.raepa_ouvraep_p o WHERE o."_temp_data"->'code_ouvrage' = a."_temp_data"->'code_ouvrage' LIMIT 1) AS idouvrage,
            qualglocxy, qualglocz, anfinpose,
            _source_historique, _code_chantier, _date_import,
            _temp_data,
            a.geom
        FROM imports.appareils_aep a
        ;

-- canalisations
        INSERT INTO raepa.raepa_canalaep_l(
            branchemnt, contcanaep, fonccanaep, materiau,
            diametre,
            modecirc, nbranche,
            qualglocxy, qualglocz, anfinpose,
            _source_historique, _code_chantier, _date_import,
            idnini, idnterm,
            _temp_data,
            geom
        )
        SELECT DISTINCT
            c.branchemnt, c.contcanaep, c.fonccanaep, c.materiau,
            c.diametre,
            c.modecirc, c.nbranche,
            c.qualglocxy, c.qualglocz, c.anfinpose,
            c._source_historique, c._code_chantier, c._date_import,
            /*o1.idouvrage AS idnini,
            o2.idouvrage AS idterm,*/
			'INCONNU','INCONNU',
            c._temp_data,

-- On retourne les canalisations dans le mauvais sens
            CASE
                WHEN
                    ST_Dwithin(ST_EndPoint(c.geom), o1.geom, 0.05)
                    OR ST_Dwithin(ST_StartPoint(c.geom), o2.geom, 0.05)
                THEN ST_Reverse(c.geom)
                ELSE c.geom
            END AS geom
        FROM imports.canalisations_aep AS c
        LEFT JOIN raepa.raepa_ouvraep_p o1
            ON TRUE
                AND o1._source_historique = in_source_historique
                AND o1._code_chantier = in_code_chantier
                AND o1."_temp_data"->'code_ouvrage' = c."_temp_data"->'ouvrage_amont'
        LEFT JOIN raepa.raepa_ouvraep_p o2
            ON TRUE
                AND o2._source_historique = in_source_historique
                AND o2._code_chantier = in_code_chantier
                AND o2."_temp_data"->'code_ouvrage' = c."_temp_data"->'ouvrage_aval'
        WHERE TRUE
        ;

-- Modification des ouvrages pour modifier les cana amont et aval
        WITH a AS (
            SELECT
                oo.idouvrage,
                COALESCE(string_agg(c.idcana, ',' ORDER BY idcana), 'INCONNU') AS idcanamont
            FROM raepa.raepa_ouvraep_p AS oo
            LEFT JOIN raepa.raepa_canalaep_l AS c
            ON ST_DWithin(ST_EndPoint(c.geom), oo.geom, 0.05)
            WHERE TRUE
            AND idcanamont = 'INCONNU'
            AND oo._source_historique = in_source_historique AND oo._code_chantier = in_code_chantier
            GROUP BY idouvrage
        )
        UPDATE raepa.raepa_ouvraep_p AS o
        SET idcanamont = a.idcanamont
        FROM a
        WHERE TRUE
        AND a.idouvrage = o.idouvrage
        AND o.idcanamont = 'INCONNU'
        ;

WITH a AS (
            SELECT
                oo.idouvrage,
                COALESCE(string_agg(c.idcana, ',' ORDER BY idcana), 'INCONNU') AS idcanaval
            FROM raepa.raepa_ouvraep_p AS oo
            LEFT JOIN raepa.raepa_canalaep_l AS c
            ON ST_DWithin(ST_StartPoint(c.geom), oo.geom, 0.05)
            WHERE TRUE
            AND idcanaval = 'INCONNU'
            AND oo._source_historique = in_source_historique AND oo._code_chantier = in_code_chantier
            GROUP BY idouvrage
        )
        UPDATE raepa.raepa_ouvraep_p AS o
        SET idcanaval = a.idcanaval
        FROM a
        WHERE TRUE
        AND a.idouvrage = o.idouvrage
        AND o.idcanaval = 'INCONNU'
        ;

-- reparation
        INSERT INTO raepa.raepa_reparaep_p (
            supprepare, defreparee,
            idsuprepar,
			daterepar, mouvrage,
            _source_historique, _code_chantier, _temp_data,
            geom
        )
        SELECT DISTINCT
            r.supprepare, r.defreparee,
            CASE
                WHEN r.supprepare = '01'
					THEN (SELECT c.idcana FROM raepa.raepa_canalaep_l c WHERE c."_temp_data"->'code_canalisation' = r."_temp_data"->'code_support' LIMIT 1)
				WHEN r.supprepare = '02'
					THEN (SELECT c.idappareil FROM raepa.raepa_apparaep_p c WHERE c."_temp_data"->'code_appareil' = r."_temp_data"->'code_support' LIMIT 1)
				WHEN r.supprepare = '03'
					THEN (SELECT c.idouvrage FROM raepa.raepa_ouvraep_p c WHERE c."_temp_data"->'code_ouvrage' = r."_temp_data"->'code_support' LIMIT 1)
				ELSE 'INCONNU' END AS idsuprepar,
			r.daterepar, r.mouvrage,
            _source_historique, _code_chantier, r._temp_data,
            r.geom
        FROM imports.reparation_aep r
        ;

END IF;

RETURN TRUE;
END;

import_tables_temporaires_dans_raepa

Parameters

Definition