gis Database

Parameters

Name	Type	Mode
in_anfinpose	text	IN
in_qualglocxy	text	IN
in_qualglocz	text	IN
in_etat	text	IN
in_source_historique	text	IN
in_code_chantier	text	IN
in_type	text	IN

Definition

DECLARE
  sql_template text;
BEGIN

-- Tests des variables d'entrée
    IF NOT in_anfinpose ~ '^\d{4}$' THEN
        RAISE EXCEPTION '[raepa] L''année de fin de pose n''est pas correcte';
        RETURN 0;
    END IF;
    IF in_qualglocxy NOT IN ('A', 'B', 'C') OR in_qualglocz NOT IN ('A', 'B', 'C') THEN
        RAISE EXCEPTION '[raepa] Les qualités de géolocalisation ne sont pas correctes: % et %', in_qualglocxy, in_qualglocz;
        RETURN 0;
    END IF;
    IF in_etat NOT IN ('00', '01', '02', '03', '04') THEN
        RAISE EXCEPTION '[raepa] Le paramètre état n''est pas correct';
        RETURN 0;
    END IF;

-- Transformation des variables
    in_qualglocxy := CASE
        WHEN in_qualglocxy = 'A' THEN '01'
        WHEN in_qualglocxy = 'B' THEN '02'
        ELSE '03'
    END;
    in_qualglocz := CASE
        WHEN in_qualglocz = 'A' THEN '01'
        WHEN in_qualglocz = 'B' THEN '02'
        ELSE '03'
    END;

IF (in_type = 'ass') THEN
   	    -- Création du schéma d'import et des tables si nécessaire
        DROP TABLE IF EXISTS imports.appareils_ass;
        CREATE TABLE imports.appareils_ass AS SELECT * FROM raepa.raepa_apparass_p LIMIT 0;
        DROP TABLE IF EXISTS imports.ouvrages_ass;
        CREATE TABLE imports.ouvrages_ass AS SELECT * FROM raepa.raepa_ouvrass_p LIMIT 0;
        DROP TABLE IF EXISTS imports.canalisations_ass;
        CREATE TABLE imports.canalisations_ass AS SELECT * FROM raepa.raepa_canalass_l LIMIT 0;
        DROP TABLE IF EXISTS imports.reparation_ass;
        CREATE TABLE imports.reparation_ass AS SELECT * FROM raepa.raepa_reparass_p LIMIT 0;

-- ouvrages
        INSERT INTO imports.ouvrages_ass (
            idouvrage,
            typreseau, fnouvass,
            z, _ztampon, _angletampon,
            qualglocxy, qualglocz, anfinpose,
            _source_historique, _code_chantier, _date_import,
            geom
        )
        SELECT
            o.idouvrage,
            o.typreseau, o.fnouvass,
            o.z_radier, o.z_tampon, o.angle_tamp,
            -- degrees(o.ang_ouv) * -1 AS _angletampon,
            in_qualglocxy, in_qualglocz, in_anfinpose,
            in_source_historique, in_code_chantier, now()::date,
            ST_Transform(o.geom, 2154)
        FROM imports.gabarit_ouvrages_ass o
        ;

-- reparation
        INSERT INTO imports.reparation_ass (
            idrepar,
            supprepare, defreparee,
            idsuprepar, daterepar, mouvrage, _typeintervention,
            _source_historique, _code_chantier,
            geom
        )
        SELECT
            r.idrepar,
            r.supprepare, r.defreparee,
            r.idsuprepar, r.daterepar, r.mouvrage, r._typeinter,
            in_source_historique, in_code_chantier,
            ST_Transform(r.geom, 2154)
        FROM imports.gabarit_reparation_ass r
        ;

-- appareils
        INSERT INTO imports.appareils_ass (
            idappareil,
            typreseau, fnappass,
            diametre, z,
            idouvrage,
            qualglocxy, qualglocz, anfinpose,
            _source_historique, _code_chantier, _date_import,
            geom
        )
        SELECT
            a.idappareil,
            a.typreseau, a.fnappass,
            a.diametre, a.z_appareil,
            a.idouvrage,
            in_qualglocxy, in_qualglocz, in_anfinpose,
            in_source_historique, in_code_chantier, now()::date,
            ST_Transform(a.geom, 2154)
        FROM imports.gabarit_appareils_ass a
        ;

-- canalisations
        INSERT INTO imports.canalisations_ass(
            branchemnt, typreseau, materiau,
            _forme, diametre, _dimensionhorizontale,
            modecirc, nbranche,
            qualglocxy, qualglocz, anfinpose, _etat,
            _source_historique, _code_chantier, _date_import,
            _precisionannee,
            zamont, zaval, idnini, idnterm,
            geom
        )
        SELECT
            c.branchemnt, c.typreseau, c.materiau,
            c.forme, c.dim_v, c.dim_h,
            c.mode_circ, c.nbranche,
            in_qualglocxy, in_qualglocz, in_anfinpose, in_etat,
            in_source_historique, in_code_chantier, now()::date,
            '00' AS _precisionannee,
            c.fe_amont, c.fe_aval, c.id_nd_amon, c.id_nd_aval,
            ST_Transform((st_dump(c.geom)).geom, 2154)
        FROM imports.gabarit_canalisations_ass AS c
        ;

ELSE

-- Création du schéma d'import et des tables si nécessaire
        DROP TABLE IF EXISTS imports.appareils_aep;
        CREATE TABLE imports.appareils_aep AS SELECT * FROM raepa.raepa_apparaep_p LIMIT 0;
        DROP TABLE IF EXISTS imports.ouvrages_aep;
        CREATE TABLE imports.ouvrages_aep AS SELECT * FROM raepa.raepa_ouvraep_p LIMIT 0;
        DROP TABLE IF EXISTS imports.canalisations_aep;
        CREATE TABLE imports.canalisations_aep AS SELECT * FROM raepa.raepa_canalaep_l LIMIT 0;
        DROP TABLE IF EXISTS imports.reparation_aep;
        CREATE TABLE imports.reparation_aep AS SELECT * FROM raepa.raepa_reparaep_p LIMIT 0;

-- ouvrages
        INSERT INTO imports.ouvrages_aep (
            idouvrage,
            mouvrage, gexploit,
			fnouvaep, z,
			anfinpose, qualglocxy, qualglocz,
			_source_historique, _code_chantier, _date_import,
			geom
        )
        SELECT
            o.idouvrage,
            o.mouvrage, o.gexploit,
			o.fnouvaep, o.z,
			in_anfinpose, in_qualglocxy, in_qualglocz,
			in_source_historique, in_code_chantier, now()::date,
			ST_Transform(o.geom, 2154)
        FROM imports.gabarit_ouvrages_aep o
        ;

-- reparation
        INSERT INTO imports.reparation_aep (
            idrepar,
			supprepare, defreparee, idsuprepar,
			daterepar, mouvrage,
			_source_historique, _code_chantier, _date_import,
			geom
        )
        SELECT
            r.idrepar,
			r.supprepare, r.defreparee, r.idsuprepar,
			r.daterepar, r.mouvrage,
			in_source_historique, in_code_chantier, now()::date,
			ST_Transform(r.geom, 2154)
        FROM imports.gabarit_reparation_aep r
        ;

-- appareils
        INSERT INTO imports.appareils_aep (
            idappareil,
            mouvrage, gexploit,
			diametre,
			fnappaep, z,
			idouvrage,
			anfinpose, qualglocxy, qualglocz,
			_source_historique, _code_chantier, _date_import,
			geom
        )
        SELECT
		    a.idappareil,
            a.mouvrage, a.gexploit,
			a.diametre,
			a.fnappaep, a.z,
			a.idouvrage,
			in_anfinpose, in_qualglocxy, in_qualglocz,
			in_source_historique, in_code_chantier, now()::date,
            ST_Transform(a.geom, 2154)
        FROM imports.gabarit_appareils_aep a
        ;

-- canalisations
        INSERT INTO imports.canalisations_aep(
            idcana,
			mouvrage, gexploit,
			enservice, branchemnt, materiau,
			diametre, anfinpose, modecirc, contcanaep, fonccanaep,
			idnini, idnterm,
			qualglocxy, qualglocz,
			_source_historique, _code_chantier, _date_import,
			geom
        )
        SELECT
            c.idcana,
			c.mouvrage, c.gexploit,
			c.enservice, c.branchemnt, c.materiau,
			c.diametre, in_anfinpose, c.modecirc, c.contcanaep, c.fonccanaep,
			c.idnini, c.idnterm,
			in_qualglocxy, in_qualglocz,
			in_source_historique, in_code_chantier, now()::date,
			ST_Transform((st_dump(c.geom)).geom, 2154)
        FROM imports.gabarit_canalisations_aep AS c
        ;

END IF;

RETURN TRUE;

END;

import_gabarit_dans_tables_temporaires

Parameters

Definition