2 pièce(s) jointe(s)
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 :
Pièce jointe 514003
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:
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 :
Pièce jointe 513987
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:
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
3 pièce(s) jointe(s)
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)
Pièce jointe 524541
Pièce jointe 524544
Pièce jointe 524546
Le code SQL est le suivant à partir de l'alinéa --1]
Code:
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"); |
