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

Consultation et edition

Distance du PBO

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

#13 Sans informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle distance boitier PBO ---*/
DROP TABLE IF EXISTS base_rop.ctrl_13;
CREATE TABLE base_rop.ctrl_13 AS
WITH t_noeud_filtered AS (
    SELECT DISTINCT 
        COALESCE(nd1.nd_code, nd2.nd_code) AS nd_code,
        COALESCE(nd1.geom, nd2.geom) AS geom,
        COALESCE(ebp.bp_etiquet, ebp.bp_codeext) AS code_pbo
    FROM public.t_ebp ebp
    LEFT JOIN t_ptech pt ON ebp.bp_pt_code = pt.pt_code
    LEFT JOIN t_noeud nd1 ON pt.pt_nd_code = nd1.nd_code
    LEFT JOIN t_ltech lt ON ebp.bp_lt_code = lt.lt_code
    LEFT JOIN t_sitetech st ON lt.lt_st_code = st.st_code
    LEFT JOIN t_noeud nd2 ON st.st_nd_code = nd2.nd_code
    WHERE ebp.bp_typelog = 'PBO'
),
baserop_filtered AS (
    SELECT DISTINCT rv_refr_pbo AS code_pbo, geom_pbo
    FROM base_rop.baserop
    WHERE rv_refr_pbo IS NOT NULL AND geom_pbo IS NOT NULL
)
SELECT
    ROW_NUMBER() OVER () AS id,
    'C13' AS id_ctr,
    'Distance du PBO' AS name_ctr,
    -- ROUND((t.geom <-> b.geom_pbo)::numeric, 2) AS baserop_distance
    COALESCE(c.site_easy, c.site_ipe, c.site_baserop) AS site_sro,
    c.code_pbo AS donnee_ref,
    CASE
        WHEN t.geom IS NOT NULL 
             AND b.geom_pbo IS NOT NULL 
             AND (t.geom <-> b.geom_pbo) <= 2
        THEN 'OK'
        ELSE 'NOK'
    END AS statut,
    CASE WHEN t.geom IS NOT NULL THEN TRUE ELSE FALSE END AS easy,
    -- NULL AS ipe,
    CASE WHEN b.geom_pbo IS NOT NULL THEN TRUE ELSE FALSE END AS baserop
FROM base_rop.code_pbo c
LEFT JOIN t_noeud_filtered t ON c.code_pbo = t.code_pbo
LEFT JOIN baserop_filtered b ON c.code_pbo = b.code_pbo;

Annuler