Controles | Unification du Référentiel Télécom

Consultation et edition

Distance du PM

Les informations du controle se modifient directement dans le formulaire ci-dessous.

#7 Sans informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle distance PM ---*/
DROP TABLE IF EXISTS base_rop.ctrl_07;
CREATE TABLE base_rop.ctrl_07 AS 
WITH pm_all AS (
    SELECT DISTINCT site_easy AS pm
    FROM base_rop.code_pbo
    WHERE site_easy IS NOT NULL
    UNION
    SELECT DISTINCT referencepm AS pm
    FROM base_rop.ipe
    WHERE referencepm IS NOT NULL
    UNION
    SELECT DISTINCT rv_refr_pm AS pm
    FROM base_rop.baserop
    WHERE rv_refr_pm IS NOT NULL
)
SELECT
 DISTINCT
    ROW_NUMBER() OVER () AS id,
    'C07' AS id_ctr,
    'Distance du PM' AS name_ctr,
    pm_all.pm AS site_sro,
    pm_all.pm AS donnee_ref,
    CASE
        WHEN 
            (ipe.referencepm IS NOT NULL AND (t_noeud.geom <-> ipe.geom_pm) <= 10)
         AND (baserop.rv_refr_pm IS NOT NULL AND (t_noeud.geom <-> baserop.geom_pm) <= 10)
        THEN 'OK'
        ELSE 'NOK'
    END AS statut,
    TRUE AS easy,
    -- 'Référentiel' AS easy_value,
    (ipe.referencepm IS NOT NULL AND (t_noeud.geom <-> ipe.geom_pm) <= 10) AS ipe,
    -- ROUND((t_noeud.geom <-> ipe.geom_pm)::numeric, 2) AS ipe_value,
    (baserop.rv_refr_pm IS NOT NULL AND (t_noeud.geom <-> baserop.geom_pm) <= 10) AS baserop
    -- ROUND((t_noeud.geom <-> baserop.geom_pm)::numeric, 2) AS baserop_value
FROM pm_all
LEFT JOIN t_sitetech ON pm_all.pm = st_codeext
LEFT JOIN t_noeud ON st_nd_code = nd_code
LEFT JOIN (
    SELECT DISTINCT referencepm, geom_pm
    FROM base_rop.ipe
) ipe ON pm_all.pm = ipe.referencepm
LEFT JOIN (
    SELECT DISTINCT rv_refr_pm, geom_pm
    FROM base_rop.baserop
) baserop ON pm_all.pm = baserop.rv_refr_pm;

Annuler