Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Futur Membre du Club
    PostGIS - création automatique de plusieurs Polygones (Télécom FTTH : ZAPBO pour chaque PBO)
    Bonjour à à toute la communauté Developpez.Net,


    Je vous contacte pour une question de rédaction SQL (voire PLPGSQL si besoin).

    Le contexte : étude de raccordement des particuliers en fibre optique (FTTH, Fiber To The Home).

    Les données d'entrée :

    =1=> La table "BPE" avec géométrie pour les points de branchements optiques (PBO)/boîtes de protection d'épissures (BPE),
    =2=> La table "ST" avec géométrie pour les clients (points d'adresse),
    =3=> La table "CB" avec géométrie pour les câbles de raccordement entre les objets issus de "BPE" et les objets issus de "ST".

    En moyenne, nous avons entre 4 et 6 câbles "CB" qui partent en étoile d'un ponctuel "BPE" vers 4 à 6 points d'adresse "ST".

    Mon but : créer pour chaque PBO (table "BPE") un polygone couramment nommé "zapbo" (zone arrière de point de branchement optique) partant de ce PBO, ralliant les 4 à 6 objets "ST" pour terminer sa boucle sur le PBO en question.
    L'image ci-dessous illustre cette demande :



    Pour cela : j'ai créé une table intermédiaire, "st_geom_par_bpe", recensant les objets "ST" avec leurs géométries par objet (PBO) issu de "BPE" avec, là-aussi leurs géométries respectives.

    Le code SQL est :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    CREATE TABLE "gracethd"."st_geom_par_bpe" AS
    SELECT BPE."code_bpe", BPE."geom" AS bpegeom, ST."geom" AS stgeom, ROW_NUMBER() OVER (
    PARTITION BY BPE."code_bpe") AS id_order
    FROM "gracethd"."ST" ST, "gracethd"."CB" CB, "gracethd"."BPE" BPE
    WHERE ST_DWithin(BPE."geom", CB."geom", 0.5)
    AND ST_DWithin(CB."geom", ST."geom", 0.5)
    AND BPE."type_fonc" IN ('PBO', 'DERIVATION')
    GROUP BY BPE."code_bpe", ST."geom";
     
    ALTER TABLE "gracethd"."st_geom_par_bpe"
    ADD id_pk SERIAL PRIMARY KEY NOT NULL;


    et nous obtenons le résultat comme indiqué dans l'image (extrait) suivante :



    Dans cet extrait, nous avons 3 zapbo avec les coordonnées du PBO et des n objets "ST" associés.
    Pour chacun de ces cas, la géométrie (coordonnées du point) du PBO de rattachement (table "BPE") et des n objets "ST" en aval sont surlignés en jaune.
    L'extrait montre précisément , en jaune, les vertex de 3 polygones "zapbo".

    Dessin du polygone : pour dessiner un polygone, nous devons d'abord dessiner une polyligne fermée, en l’occurrence nous répéterons notre sommet PBO en début et fin de polygone.
    Pour dessiner une polyligne sur une n vertex, il est nécessaire (sauf s'il existe d'autres méthodes) de procéder à l'aide d'un "ARRAY" et c'est à partir de ce moment-là que je n'arriver plus à avancer.

    Mon code est le suivant :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
     
    SELECT ST_MakeLine( ARRAY( SELECT "geom" FROM "gracethd"."st_geom_par_bpe" ORDER BY "id_order") );


    Ce code me crée un seul et unique polygone et cela sur l'ensemble des objets "ST" (points d'adresses = clients potentiels) de la table "st_geom_par_bpe".
    Vu le code, c'est logique, mais je souhaiterais créer un nombre x d' "ARRAY" ou de "SELECT ST_MakeLine( ARRAY( SELECT "geom" FROM ........ "
    correspondant au même nombre x d'objets (PBO) dans la table "BPE"
    pour pourvoir enfin dessiner les x polygones "zapbo".

    Pour créer une suite de 1 à x "ARRAY" ou de "SELECT ST_MakeLine( ARRAY( SELECT "geom" FROM ........ ",
    faut-il passer par la création d'une fonction en PLPGSQL pour une bloucle de 1 à x ? ....


    Merci par avance pour toute réponse éventuelle de votre part.

    Bien Cordialement.


    Philippe

  2. #2
    Membre éclairé
    bonjour, peut-être une requête du style

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    select code_bpe, ST_MakeLine(geom order by id_order) as geom 
    from "gracethd"."st_geom_par_bpe"
    group by code_bpe

  3. #3
    Futur Membre du Club
    Tout d'abord, Merci Xavier Pierre.
    Effectivement, la clé de l'énigme réside dans le "order by" intégré au sein du "ST_MakeLine" ( ST_MakeLine(geom order by id_order) )

    ------------------------

    Sujet quasiment résolu :

    J'ai rédigé un code qui saisit automatiquement les ZAPBO-ZABPI, mais il subsiste quelques petites imperfections :
    - comme les polygones croisés (avec un ou plusieurs "8") en rason de l'ordre de sasie des adresses "ST". D'un point de vue bases de données, ce n'est pas faux mais visuellement c'est perturbant et je ne sais pas si Calimap va apprécier.
    - le chevauchement des ZAPBO avec 2 PBO dans la même chambre.
    - la saisie, via un ST_Buffer, du cas particulier d'une seule et unique adresse "ST" raccordée par un PBO (table "BPE"). Un polygone nécessite au moins 3 points et virtuellement 4 pour boucler une polyligne sur le point de départ; à défaut, nous utiliseronds un ST_Buffer comme déjà utililé pour les BPI (table "BPE" ) raccordant une adresse "ST" géométriquement superposée et avec un nombre de logements raccordables > 3.

    Hormis ces point à peaufiner, ça marche.

    Impression écran d'un extrait sur le 34_DI_T041S03 (STML_S03) :
    - en hachures rouges : le résultat du code SQL (zapbo_zabpi)
    - en vert : la saisie actuelle (manuelle) validée (t_zpbo)





    Le code SQL est le suivant à partir de l'alinéa --1]

    Bonne réception 😀

    Nous (à toute la communauté Développez.com) étudierons les améliorations/modifications potentielles ensemble. 😀

    Philippe Schitter

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
     
    --1] Création des ZAPBO
     
    CREATE TABLE "gracethd"."st_geom_par_bpe" AS
    SELECT BPE."code_bpe", ST."geom" AS geom
    FROM "gracethd"."ST" ST, "gracethd"."CB" CB, "gracethd"."BPE" BPE
    WHERE ST_DWithin(BPE."geom", CB."geom", 0.5)
    AND ST_DWithin(CB."geom", ST."geom", 0.5)
    AND BPE."type_fonc" IN ('PBO', 'DERIVATION')
    GROUP BY BPE."code_bpe", ST."geom"
    UNION
    SELECT "code_bpe", "geom"
    FROM "gracethd"."BPE";
     
    ALTER TABLE "gracethd"."st_geom_par_bpe"
    ADD id_pk SERIAL PRIMARY KEY NOT NULL;
     
    CREATE TABLE "gracethd"."st_geom_par_bpe_id_order" AS
    SELECT *, ROW_NUMBER() OVER (
    PARTITION BY "code_bpe") AS id_order
    FROM "gracethd"."st_geom_par_bpe";
     
    ALTER TABLE "gracethd"."st_geom_par_bpe_id_order"
    ADD idstbpe_pk SERIAL PRIMARY KEY NOT NULL;
     
    CREATE TABLE "gracethd"."zapbo_open_line" AS
    SELECT "code_bpe", ST_MakeLine("geom" ORDER BY "id_order") as geom
    from "gracethd"."st_geom_par_bpe_id_order"
    GROUP BY "code_bpe";
     
    ALTER TABLE "gracethd"."zapbo_open_line"
    ADD CONSTRAINT zapbo_open_line_pkey PRIMARY KEY("code_bpe");
     
    CREATE TABLE "gracethd"."zapbo_polygon" AS
    SELECT "code_bpe", ST_MakePolygon( ST_AddPoint(ZAPBOOL."geom", ST_StartPoint(ZAPBOOL."geom")) ) AS "geom"
    FROM "gracethd"."zapbo_open_line" ZAPBOOL
    WHERE ST_NPoints("geom") > 2;
     
    ALTER TABLE "gracethd"."zapbo_polygon"
    ADD CONSTRAINT zapbo_polygon_pkey PRIMARY KEY("code_bpe");
     
    ---
     
    --2] Création des ZABPI
     
    CREATE TABLE "gracethd"."bpi" AS
    SELECT *
    FROM "gracethd"."BPE"
    WHERE "nb_prises" > 3 AND "type_fonc" IN ('BPI', 'PBI');
     
    ALTER TABLE "gracethd"."bpi"
    ADD CONSTRAINT bpi_pkey PRIMARY KEY("code_bpe");
     
    CREATE TABLE "gracethd"."zabpi" AS
    SELECT "code_bpe", ST_Buffer("geom", 1) AS "geom"
    FROM "gracethd"."bpi";
     
    ALTER TABLE "gracethd"."zabpi"
    ADD CONSTRAINT zabpi_pkey PRIMARY KEY("code_bpe");
     
    ---
     
    --3] Création de la fusion ZAPBO-ZABPI
     
    CREATE TABLE "gracethd"."zapbo_zabpi" AS
    SELECT *
    FROM "gracethd"."zapbo_polygon"
    UNION
    SELECT *
    FROM "gracethd"."zabpi";
     
    ALTER TABLE "gracethd"."zapbo_zabpi"
    ADD CONSTRAINT zapbo_zabpi_pkey PRIMARY KEY("code_bpe");

  4. #4
    Futur Membre du Club
    ZAPBO-ZABPI saisie automatique SQL sur le MCD COVAGE [RESOLU]
    Rédaction d'un code SQL pour la saisie automatique des ZAPBO-ZABPI sur le MCD COVAGE. [Sujet RÉSOLU]
    Le code SQL se trouve en-dessous des impressions écran ainsi qu'en pièce jointe.

    Attention : Cas des chambres dont le nombre BPE/PBO > 1 (souvent = 2 dans ce cas décrit ici) :
    Afin d'éviter des doublons de poches avec ~ deux fois plus d'adresses qu'il n'en faut,
    il faut utiliser la méthode de la jointure via la table attributaire de la couche "gracethd"."ST" et son champ "code_bpe". Cette méthode permet d'établir le lien entre les bonnes adresses "ST" et la bonne BPE de la chambre étudiée;
    ce que ne permet pas la méthode géométrique (testée dans ma précédente réponse et qui s'avère problépatique, donc fausse) qui consiste en la double-jointure spatiale via les câbles de raccordement de la couche "gracethd"."CB" ("gracethd"."BPE" <==> "gracethd"."CB" <==> "gracethd"."ST").

    Impression écran d'un extrait sur le 34_DI_T041S03 (STML_S03) :
    - en hachures rouges : le résultat du code SQL (zapbo_zabpi)
    - en bleu : la saisie actuelle (manuelle) validée (t_zpbo)







    Le code SQL est le suivant à partir de l'alinéa --1]

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
     
    --1] Création des ZAPBO
     
    CREATE TABLE "gracethd"."st_geom_par_bpe" AS
    SELECT BPE."code_bpe", ST."geom" AS geom
    FROM "gracethd"."ST" ST, "gracethd"."BPE" BPE
    WHERE BPE."code_bpe" = ST."code_bpe"
    AND BPE."type_fonc" IN ('PBO', 'DERIVATION')
    GROUP BY BPE."code_bpe", ST."geom"
    UNION
    SELECT "code_bpe", "geom"
    FROM "gracethd"."BPE";
     
    ALTER TABLE "gracethd"."st_geom_par_bpe"
    ADD id_pk SERIAL PRIMARY KEY NOT NULL;
     
    CREATE TABLE "gracethd"."st_geom_par_bpe_id_order" AS
    SELECT *, ROW_NUMBER() OVER (
    PARTITION BY "code_bpe") AS id_order
    FROM "gracethd"."st_geom_par_bpe";
     
    ALTER TABLE "gracethd"."st_geom_par_bpe_id_order"
    ADD idstbpe_pk SERIAL PRIMARY KEY NOT NULL;
     
    CREATE TABLE "gracethd"."zapbo_open_line" AS
    SELECT "code_bpe", ST_MakeLine("geom" ORDER BY "id_order") as geom
    from "gracethd"."st_geom_par_bpe_id_order"
    GROUP BY "code_bpe";
     
    ALTER TABLE "gracethd"."zapbo_open_line"
    ADD CONSTRAINT zapbo_open_line_pkey PRIMARY KEY("code_bpe");
     
    CREATE TABLE "gracethd"."zapbo_polygon" AS
    SELECT "code_bpe", ST_MakePolygon( ST_AddPoint(ZAPBOOL."geom", ST_StartPoint(ZAPBOOL."geom")) ) AS "geom"
    FROM "gracethd"."zapbo_open_line" ZAPBOOL
    WHERE ST_NPoints("geom") > 2;
     
    ALTER TABLE "gracethd"."zapbo_polygon"
    ADD CONSTRAINT zapbo_polygon_pkey PRIMARY KEY("code_bpe");
     
    ---
     
    --2] ZAPBO avec 1 seul ST :
     
    CREATE TABLE "gracethd"."BPE_1ST" AS
    SELECT ST_par_BPE."code_bpe", ST_par_BPE."geom", ST_par_BPE."nb_st"
    FROM
    (SELECT BPE."code_bpe", BPE."geom", COUNT(BPE."code_bpe") AS nb_st
    FROM "gracethd"."ST" ST, "gracethd"."BPE" BPE
    WHERE BPE."code_bpe" = ST."code_bpe"
    AND BPE."type_fonc" IN ('PBO', 'DERIVATION')
    GROUP BY BPE."code_bpe") ST_par_BPE
    WHERE ST_par_BPE."nb_st" = 1;
     
    ALTER TABLE "gracethd"."BPE_1ST"
    ADD CONSTRAINT BPE_1ST_pkey PRIMARY KEY("code_bpe");
     
    CREATE TABLE "gracethd"."zapbo_1st" AS
    SELECT B."code_bpe", ST_Buffer(ST_MakeLine(B."geom", ST."geom"), 1) AS "geom"
    FROM "gracethd"."BPE_1ST" B, "gracethd"."ST" ST
    WHERE B."code_bpe" = ST."code_bpe";
     
    ALTER TABLE "gracethd"."zapbo_1st"
    ADD CONSTRAINT zapbo_1st_pkey PRIMARY KEY("code_bpe");
     
    ---
     
    --3] Création des ZABPI
     
    CREATE TABLE "gracethd"."bpi" AS
    SELECT *
    FROM "gracethd"."BPE"
    WHERE "nb_prises" > 3 AND "type_fonc" IN ('BPI', 'PBI');
     
    ALTER TABLE "gracethd"."bpi"
    ADD CONSTRAINT bpi_pkey PRIMARY KEY("code_bpe");
     
    CREATE TABLE "gracethd"."zabpi" AS
    SELECT "code_bpe", ST_Buffer("geom", 1) AS "geom"
    FROM "gracethd"."bpi";
     
    ALTER TABLE "gracethd"."zabpi"
    ADD CONSTRAINT zabpi_pkey PRIMARY KEY("code_bpe");
     
    ---
     
    --4] Création de la fusion ZAPBO-ZABPI
     
    CREATE TABLE "gracethd"."zapbo_zabpi" AS
    SELECT *
    FROM "gracethd"."zapbo_polygon"
    UNION
    SELECT *
    FROM "gracethd"."zapbo_1st"
    UNION
    SELECT *
    FROM "gracethd"."zabpi";
     
    ALTER TABLE "gracethd"."zapbo_zabpi"
    ADD CONSTRAINT zapbo_zabpi_pkey PRIMARY KEY("code_bpe");

###raw>template_hook.ano_emploi###