Created
September 22, 2022 07:59
-
-
Save CEZERT/a133ff5f6c8735b3d540745c3a383f4f to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- traitement tables fusion pour éviter les doublons | |
-- NON UTILISEE methode 1 : fonction trigger | |
-- NB : la comparaison ne fonctionne pas si les valeurs des attributs à comparer sont NULL | |
/* | |
--DROP FUNCTION terrain.verif_fusion_aff(); | |
create or replace function terrain.verif_fusion_aff() returns opaque as $body$ | |
begin | |
if NEW.geom in ( select geom from terrain.fusion_aff ) | |
AND NEW.lot in ( select lot from terrain.fusion_aff ) | |
AND NEW.etude in ( select etude from terrain.fusion_aff ) | |
AND NEW.id_pipe in ( select id_pipe from terrain.fusion_aff ) | |
AND NEW.hdop in ( select hdop from terrain.fusion_aff ) | |
AND NEW."n°_point_tn" in ( select "n°_point_tn" from terrain.fusion_aff ) | |
AND NEW.x in ( select x from terrain.fusion_aff ) | |
AND NEW.y in ( select y from terrain.fusion_aff ) | |
AND NEW.z_tn in ( select z_tn from terrain.fusion_aff ) | |
then | |
return null; | |
end if; | |
return NEW; | |
end; | |
$body$ | |
LANGUAGE 'plpgsql'; | |
--DROP FUNCTION terrain.verif_fusion_aff(); | |
create or replace function terrain.verif_fusion_aff() returns trigger as $body$ | |
begin | |
if NEW.geom in ( select geom from terrain.fusion_aff ) | |
AND NEW.lot in ( select lot from terrain.fusion_aff ) | |
THEN | |
raise notice 'la ligne existe deja'; | |
--return null; | |
end if; | |
return NEW; | |
end; | |
$body$ | |
LANGUAGE 'plpgsql'; | |
--DROP TRIGGER tr_verif_fusion_aff on terrain.fusion_aff ; | |
create trigger tr_verif_fusion_aff BEFORE insert on terrain.fusion_aff | |
for each row execute procedure terrain.verif_fusion_aff(); | |
*/ | |
-- Méthode 2 : utilisation de règles : | |
-- NB : la comparaison ne fonctionne pas si les valeurs des attributs à comparer sont NULL | |
CREATE OR REPLACE RULE fusion_aff_insert_ignore AS ON INSERT TO terrain.fusion_aff | |
WHERE EXISTS (SELECT 1 FROM terrain.fusion_aff WHERE geom = NEW.geom | |
AND lot=NEW.lot AND etude=NEW.etude AND id_pipe=NEW.id_pipe AND hdop=NEW.hdop | |
AND "n°_point_tn"=NEW."n°_point_tn" AND x=NEW.x AND y=NEW.y AND z_tn=NEW.z_tn) | |
DO INSTEAD NOTHING; | |
CREATE OR REPLACE RULE fusion_aff_insert_ignore AS ON INSERT TO terrain.fusion_aff | |
WHERE EXISTS (SELECT 1 FROM terrain.fusion_aff WHERE geom = NEW.geom AND lot=NEW.lot | |
AND etude=NEW.etude AND id_pipe=NEW.id_pipe AND hdop=NEW.hdop AND "n°_point_tn"=NEW."n°_point_tn" | |
AND x=NEW.x AND y=NEW.y | |
) | |
DO INSTEAD NOTHING; | |
CREATE OR REPLACE RULE fusion_ctrl_insert_ignore AS ON INSERT TO terrain.fusion_ctrl | |
WHERE EXISTS (SELECT 1 FROM terrain.fusion_ctrl WHERE geom = NEW.geom AND lot=NEW.lot | |
AND etude=NEW.etude AND id_pipe=NEW.id_pipe AND hdop=NEW.hdop AND "n°_point_tn"=NEW."n°_point_tn" | |
AND x=NEW.x AND y=NEW.y | |
) | |
DO INSTEAD NOTHING; | |
CREATE OR REPLACE RULE fusion_det_insert_ignore AS ON INSERT TO terrain.fusion_det | |
WHERE EXISTS (SELECT 1 FROM terrain.fusion_det WHERE geom = NEW.geom AND lot=NEW.lot | |
AND etude=NEW.etude AND id_pipe=NEW.id_pipe AND hdop=NEW.hdop AND "n°_point_tn"=NEW."n°_point_tn" | |
AND x=NEW.x AND y=NEW.y | |
) | |
DO INSTEAD NOTHING; | |
CREATE OR REPLACE RULE lineaire_dwg_insert_ignore AS ON INSERT TO terrain.lineaire_dwg | |
WHERE EXISTS (SELECT 1 FROM terrain.lineaire_dwg WHERE geom = NEW.geom AND lot=NEW.lot | |
AND date=NEW.date AND id_pipe=NEW.id_pipe AND nom_dwg=NEW.nom_dwg | |
) | |
DO INSTEAD NOTHING; | |
CREATE OR REPLACE RULE fusion_ctrl_insert_ignore AS ON INSERT TO traitement."Fusion_CTRL" | |
WHERE EXISTS (SELECT 1 FROM traitement."Fusion_CTRL" WHERE x = NEW.x AND y=NEW.y AND z_tn=NEW.z_tn | |
) | |
DO INSTEAD NOTHING; | |
CREATE OR REPLACE RULE fusion_aff_insert_ignore AS ON INSERT TO traitement."Fusion__AFF" | |
WHERE EXISTS (SELECT 1 FROM traitement."Fusion__AFF" WHERE x = NEW.x AND y=NEW.y AND z_tn=NEW.z_tn | |
) | |
DO INSTEAD NOTHING; | |
CREATE OR REPLACE RULE fusion_det_insert_ignore AS ON INSERT TO traitement."Fusion__DET" | |
WHERE EXISTS (SELECT 1 FROM traitement."Fusion__DET" WHERE x = NEW.x AND y=NEW.y AND z_tn=NEW.z_tn | |
) | |
DO INSTEAD NOTHING; | |
CREATE OR REPLACE RULE qualif_trace_insert_ignore AS ON INSERT TO traitement."Qualif_trace" | |
WHERE EXISTS (SELECT 1 FROM traitement."Qualif_trace" WHERE lot = NEW.lot AND id_pipe=NEW.id_pipe AND geom=NEW.geom | |
) | |
DO INSTEAD NOTHING; | |
CREATE OR REPLACE RULE classea_livraison_insert_ignore AS ON INSERT TO traitement."CLASSEA_LIVRAISON" | |
WHERE EXISTS (SELECT 1 FROM traitement."CLASSEA_LIVRAISON" WHERE numero_de_lot = NEW.numero_de_lot AND id_pipe=NEW.id_pipe AND geom=NEW.geom | |
AND id_point_detection=NEW.id_point_detection) | |
DO INSTEAD NOTHING; | |
/* | |
SELECT count(*) FROM terrain.fusion_aff; | |
TRUNCATE TABLE terrain.fusion_aff ; | |
INSERT INTO terrain.fusion_aff | |
values(139,ST_GeomFromText('POINT(662327.181 7059189.489)',2154),'LOT-1','BT_206_40_PAS-DE-CALAIS_20210412',40,0.5,20000,662327.181,7059189.489, | |
1.2,'',0.01,0.016,23,null,null,null,null,20000,null,NULL,'Lambert 93','Non',0.1,18.845,2,'2021-04-12 13:15:43'); | |
INSERT INTO terrain.fusion_aff | |
values(141,ST_GeomFromText('POINT(662327.181 7059189.489)',2154),'LOT-1','BT_206_40_PAS-DE-CALAIS_20210412',40,0.5,20000,662327.181,7059189.489, | |
1.2,'',0.01,0.016,23,null,null,null,null,20000,null,NULL,'Lambert 93','Non',0.1,18.845,2,'2021-04-12 13:15:43'); | |
INSERT INTO terrain.fusion_aff | |
values(142,ST_GeomFromText('POINT(662327.181 7059189.489)',2154),'LOT-2','BT_206_40_PAS-DE-CALAIS_20210412',40,0.5,20000,662327.181,7059189.489, | |
null,'',0.01,0.016,23,null,null,null,null,20000,null,NULL,'Lambert 93','Non',0.1,18.845,2,'2021-04-12 13:15:43'); | |
*/ | |
-- création des vues pour le suivi d'avancement des travaux : | |
-- création de la vue de suivi des longueur relevées par type de relevé (layer autocad) | |
DROP VIEW IF EXISTS suivi_avance.lineaire_det; | |
CREATE OR REPLACE VIEW suivi_avance.lineaire_det | |
AS SELECT lineaire_dwg.lot, | |
lineaire_dwg.id_pipe, | |
lineaire_dwg.nom_dwg, | |
lineaire_dwg.autocad_layer, | |
lineaire_dwg.geom, | |
lineaire_dwg.date AS date_releve, | |
row_number() OVER () AS gid, | |
st_length(lineaire_dwg.geom) AS longueur | |
FROM terrain.lineaire_dwg; | |
-- création du tableau de suivi qui aggrège les infos par lot, id_pipe, date, nom_dwg et layer : | |
--DROP VIEW suivi_avance.tableau_recap; | |
CREATE OR REPLACE VIEW suivi_avance.tableau_recap | |
AS | |
WITH agg AS ( | |
SELECT row_number() OVER () AS gid, | |
l.lot, | |
l.id_pipe, | |
l.nom_dwg, | |
l.autocad_layer AS type_releve, | |
l.date AS date_releve, | |
round(sum(st_length(l.geom))::numeric,0) AS longueur_releve | |
FROM terrain.lineaire_dwg l | |
GROUP BY l.lot, l.id_pipe, l.autocad_layer, l.nom_dwg, l.date | |
) | |
SELECT gid, | |
lot, | |
id_pipe, | |
nom_dwg, | |
type_releve, | |
date_releve, | |
longueur_releve, | |
long_pipe_tot_m, | |
round((longueur_releve/long_pipe_tot_m*100)::numeric,0) AS pourcent_long_pipe_concerne | |
FROM agg | |
LEFT JOIN (SELECT pipeline_i, round(sum(REPLACE(LEFT(c.longueur_k,10),',','.')::numeric)*1000,0) AS long_pipe_tot_m | |
FROM suivi_avance.centerline_avec_dn_2154 c GROUP BY pipeline_i) AS c | |
ON id_pipe=c.pipeline_i ; | |
/* | |
CREATE OR REPLACE VIEW suivi_avance.tableau_recap | |
AS | |
WITH agg AS ( | |
SELECT row_number() OVER () AS gid, | |
l.lot, | |
l.id_pipe, | |
l.nom_dwg, | |
l.autocad_layer AS type_releve, | |
l.date AS date_releve, | |
round(sum(st_length(l.geom))::numeric,0) AS longueur_releve, | |
round(c.longueur_pipe_tot*1000,0) AS long_pipe_tot_m | |
FROM terrain.lineaire_dwg l | |
LEFT JOIN (SELECT pipeline_i, sum(REPLACE(LEFT(c.longueur,10),',','.')::numeric) AS longueur_pipe_tot | |
FROM suivi_avance.centerline_octobre2020_2154 c GROUP BY pipeline_i) AS c | |
ON l.id_pipe=c.pipeline_i | |
GROUP BY l.lot, l.id_pipe, l.autocad_layer, l.nom_dwg, l.date, c.longueur_pipe_tot | |
) | |
SELECT gid, | |
lot, | |
id_pipe, | |
nom_dwg, | |
type_releve, | |
date_releve, | |
longueur_releve, | |
long_pipe_tot_m, | |
round((longueur_releve/long_pipe_tot_m*100)::numeric,0) AS pourcent_long_pipe_concerne | |
FROM agg;*/ | |
--DROP TABLE terrain.lineaire_dwg CASCADE; | |
/* | |
-- methode 2 en calculant la longueur graphique des pipes centerline, sans utiliser le champ d'origine longueur : | |
DROP VIEW suivi_avance.tableau_recap; | |
CREATE OR REPLACE VIEW suivi_avance.tableau_recap | |
AS | |
WITH agg AS ( | |
SELECT row_number() OVER () AS gid, | |
l.lot, | |
l.id_pipe, | |
l.autocad_layer AS type_releve, | |
sum(st_length(l.geom)) AS longueur_releve, | |
c.longueur_pipe_tot AS long_pipe_tot_m | |
FROM terrain.lineaire_dwg l | |
LEFT JOIN (SELECT pipeline_i, sum(ST_Length(geom)) AS longueur_pipe_tot | |
FROM suivi_avance.centerline_octobre2020_2154 c GROUP BY pipeline_i) AS c | |
ON l.id_pipe=c.pipeline_i | |
GROUP BY l.lot, l.id_pipe, l.autocad_layer, c.longueur_pipe_tot | |
) | |
SELECT gid, | |
lot, | |
id_pipe, | |
type_releve, | |
longueur_releve, | |
long_pipe_tot_m, | |
round((longueur_releve/long_pipe_tot_m*100)::numeric,2) AS pourcent_long_pipe_concerne | |
FROM agg; | |
*/ | |
DROP TABLE IF EXISTS terrain.lineaire_dwg CASCADE; | |
-- création du tableau de suivi qui aggrège les infos uniquement par lot, id_pipe et layer : | |
--DROP VIEW suivi_avance.tableau_recap_global; | |
CREATE OR REPLACE VIEW suivi_avance.tableau_recap_global | |
AS | |
WITH agg AS ( | |
SELECT row_number() OVER () AS gid, | |
l.lot, | |
l.id_pipe, | |
l.autocad_layer AS type_releve, | |
round(sum(st_length(l.geom))::numeric,0) AS longueur_releve | |
FROM terrain.lineaire_dwg l | |
GROUP BY l.lot, l.id_pipe, l.autocad_layer | |
) | |
SELECT gid, | |
lot, | |
id_pipe, | |
type_releve, | |
longueur_releve, | |
c.long_pipe_tot_m, | |
round((longueur_releve/long_pipe_tot_m*100)::numeric,0) AS pourcent_long_pipe_concerne | |
FROM agg | |
LEFT JOIN (SELECT pipeline_i, round(sum(REPLACE(LEFT(c.longueur_k,10),',','.')::numeric)*1000,0) AS long_pipe_tot_m | |
FROM suivi_avance.centerline_avec_dn_2154 c GROUP BY pipeline_i) AS c | |
ON id_pipe=c.pipeline_i ; | |
TRUNCATE TABLE terrain.fusion_aff ; | |
TRUNCATE TABLE terrain.fusion_ctrl ; | |
TRUNCATE TABLE terrain.fusion_det ; | |
SELECT DISTINCT descriptio FROM traitement.signalisations_france_2154; | |
DELETE FROM terrain.lineaire_dwg WHERE id_pipe IN('41', '3666'); | |
SELECT * FROM terrain.lineaire_dwg WHERE id_pipe IN('41', '3666'); | |
-- création vue de controle des linéaires relevés par rapports aux linéaires existants dans la base PODS ALFI | |
--DROP VIEW traitement.controle_lineaire_0_27; | |
CREATE OR REPLACE VIEW traitement.controle_lineaire_0_27 AS ( | |
WITH pipes_buff_agg AS ( | |
SELECT ST_Union(ST_Buffer(geom, 0.27)) AS geom | |
FROM traitement.pods_al_pipes_technical_alfi_2154 | |
WHERE code_zone='NF' | |
) | |
SELECT distinct row_number() OVER () AS gid, r.lot, r.id_pipe, r.igds_level_name AS classe_precision, | |
st_difference(r.geom,p.geom) as geom | |
FROM traitement."Qualif_trace" r, pipes_buff_agg p | |
WHERE ST_DWithin(r.geom,p.geom,1) | |
); | |
--DROP VIEW traitement.controle_lineaire_0_27; | |
CREATE OR REPLACE VIEW traitement.controle_lineaire_0_27 AS ( | |
WITH pipes_buff_agg AS ( | |
SELECT ST_UNion(ST_Buffer(geom, 0.27)) AS geom | |
FROM traitement.pods_al_pipes_technical_alfi_2154 | |
WHERE code_zone='NF' | |
), | |
tronc_horszone AS ( | |
SELECT distinct row_number() OVER () AS gid, r.lot, r.id_pipe, r.igds_level_name AS classe_precision_releve, | |
(ST_Dump(st_difference(r.geom,p.geom))).geom as geom | |
FROM traitement."Qualif_trace" r, pipes_buff_agg p | |
WHERE ST_DWithin(r.geom,p.geom,1) | |
) | |
SELECT row_number() OVER () AS gid, r.lot, r.id_pipe, r.classe_precision_releve, | |
pt.categorie_max AS classe_precision_initiale_alfi, | |
r.geom | |
FROM tronc_horszone r | |
JOIN traitement.pods_al_pipes_technical_alfi_2154 pt | |
ON ST_DWithin(ST_lineInterpolatePoint(r.geom, 0.5),pt.geom,1) | |
); | |
DROP TABLE IF EXISTS traitement.pods_al_pipes_technical_alfi_2154_union_027 CASCADE; | |
CREATE TABLE traitement.pods_al_pipes_technical_alfi_2154_union_027 AS ( | |
SELECT id_pipes, ST_UNion(ST_Buffer(geom, 0.27)) AS geom | |
FROM traitement.pods_al_pipes_technical_alfi_2154 | |
WHERE code_zone='NF' | |
GROUP BY id_pipes | |
); | |
CREATE INDEX idx_pods_al_pipes_technical_alfi_2154_union_027_geom ON traitement.pods_al_pipes_technical_alfi_2154_union_027 USING gist(geom); | |
DROP TABLE traitement.pods_al_pipes_technical_alfi_2154_union_040 CASCADE; | |
CREATE TABLE traitement.pods_al_pipes_technical_alfi_2154_union_040 AS ( | |
SELECT id_pipes, ST_UNion(ST_Buffer(geom, 0.40)) AS geom | |
FROM traitement.pods_al_pipes_technical_alfi_2154 | |
WHERE code_zone='NF' | |
GROUP BY id_pipes | |
); | |
CREATE INDEX idx_pods_al_pipes_technical_alfi_2154_union_040_geom ON traitement.pods_al_pipes_technical_alfi_2154_union_040 USING gist(geom); | |
/* | |
--DROP VIEW traitement.controle_lineaire_0_27_2; | |
CREATE OR REPLACE VIEW traitement.controle_lineaire_0_27_2 AS ( | |
WITH | |
tronc_horszone AS ( | |
SELECT distinct row_number() OVER () AS gid, r.lot, r.id_pipe, r.igds_level_name AS classe_precision_releve, | |
cl.cas_particulier, | |
cl.description_cas_particulier, | |
(ST_Dump(st_difference(r.geom,p.geom))).geom as geom | |
FROM traitement."Qualif_trace" r JOIN traitement.pods_al_pipes_technical_alfi_2154_union_027 p | |
ON ST_DWithin(r.geom,p.geom,1) | |
LEFT JOIN traitement."CLASSEA_LIVRAISON" cl ON ST_DWithin(r.geom, ST_Transform(cl.geom,2154), 0.15) | |
) | |
SELECT DISTINCT ON (r.geom, r.cas_particulier, r.description_cas_particulier) row_number() OVER () AS gid, r.lot, r.id_pipe, r.classe_precision_releve, | |
pt.categorie_max AS classe_precision_initiale_alfi, | |
r.geom, | |
r.cas_particulier, | |
r.description_cas_particulier | |
FROM tronc_horszone r | |
LEFT JOIN traitement.pods_al_pipes_technical_alfi_2154 pt | |
ON ST_DWithin(ST_lineInterpolatePoint(r.geom, 0.5),pt.geom,1) | |
); | |
*/ | |
--DROP VIEW traitement.controle_lineaire_0_27_3; | |
CREATE OR REPLACE VIEW traitement.controle_lineaire_0_27_3 AS ( | |
WITH | |
tronc_horszone AS ( | |
SELECT distinct row_number() OVER () AS gid, r.lot, r.id_pipe, r.igds_level_name AS classe_precision_releve, | |
(ST_Dump(st_difference(r.geom,p.geom))).geom as geom | |
FROM traitement."Qualif_trace" r JOIN traitement.pods_al_pipes_technical_alfi_2154_union_027 p | |
ON r.id_pipe = p.id_pipes | |
) | |
SELECT DISTINCT ON (r.geom, cl.cas_particulier, cl.description_cas_particulier) row_number() OVER () AS gid, r.lot, r.id_pipe, r.classe_precision_releve, | |
pt.categorie_max AS classe_precision_initiale_alfi, | |
r.geom, | |
cl.cas_particulier, | |
cl.description_cas_particulier | |
FROM tronc_horszone r | |
LEFT JOIN traitement.pods_al_pipes_technical_alfi_2154 pt | |
ON ST_DWithin(ST_lineInterpolatePoint(r.geom, 0.5),pt.geom,1) | |
LEFT JOIN traitement."CLASSEA_LIVRAISON" cl ON ST_DWithin(r.geom, ST_Transform(cl.geom,2154), 0.05) | |
); | |
/* | |
--DROP VIEW traitement.controle_lineaire_0_40; | |
CREATE OR REPLACE VIEW traitement.controle_lineaire_0_40 AS ( | |
WITH pipes_buff_agg AS ( | |
SELECT ST_UNion(ST_Buffer(geom, 0.40)) AS geom | |
FROM traitement.pods_al_pipes_technical_alfi_2154 | |
WHERE code_zone='NF' | |
), | |
tronc_horszone AS ( | |
SELECT distinct row_number() OVER () AS gid, r.lot, r.id_pipe, r.igds_level_name AS classe_precision_releve, | |
(ST_Dump(st_difference(r.geom,p.geom))).geom as geom | |
FROM traitement."Qualif_trace" r, pipes_buff_agg p | |
WHERE ST_DWithin(r.geom,p.geom,1) | |
) | |
SELECT row_number() OVER () AS gid, r.lot, r.id_pipe, r.classe_precision_releve, | |
pt.categorie_max AS classe_precision_initiale_alfi, | |
r.geom | |
FROM tronc_horszone r | |
JOIN traitement.pods_al_pipes_technical_alfi_2154 pt | |
ON ST_DWithin(ST_lineInterpolatePoint(r.geom, 0.5),pt.geom,1) | |
); | |
--DROP VIEW traitement.controle_lineaire_0_40_2; | |
CREATE OR REPLACE VIEW traitement.controle_lineaire_0_40_2 AS ( | |
WITH | |
tronc_horszone AS ( | |
SELECT distinct row_number() OVER () AS gid, r.lot, r.id_pipe, r.igds_level_name AS classe_precision_releve, | |
cl.cas_particulier, | |
cl.description_cas_particulier, | |
(ST_Dump(st_difference(r.geom,p.geom))).geom as geom | |
FROM traitement."Qualif_trace" r JOIN traitement.pods_al_pipes_technical_alfi_2154_union_040 p | |
ON ST_DWithin(r.geom,p.geom,1) | |
LEFT JOIN traitement."CLASSEA_LIVRAISON" cl ON ST_DWithin(r.geom, ST_Transform(cl.geom,2154), 0.05) | |
) | |
SELECT DISTINCT ON (r.geom, r.cas_particulier, r.description_cas_particulier) row_number() OVER () AS gid, r.lot, r.id_pipe, r.classe_precision_releve, | |
pt.categorie_max AS classe_precision_initiale_alfi, | |
r.geom, | |
r.cas_particulier, | |
r.description_cas_particulier | |
FROM tronc_horszone r | |
LEFT JOIN traitement.pods_al_pipes_technical_alfi_2154 pt | |
ON ST_DWithin(ST_lineInterpolatePoint(r.geom, 0.5),pt.geom,1) | |
); | |
*/ | |
--DROP VIEW traitement.controle_lineaire_0_40_3; | |
CREATE OR REPLACE VIEW traitement.controle_lineaire_0_40_3 AS ( | |
WITH | |
tronc_horszone AS ( | |
SELECT distinct row_number() OVER () AS gid, r.lot, r.id_pipe, r.igds_level_name AS classe_precision_releve, | |
(ST_Dump(st_difference(r.geom,p.geom))).geom as geom | |
FROM traitement."Qualif_trace" r JOIN traitement.pods_al_pipes_technical_alfi_2154_union_040 p | |
ON r.id_pipe = p.id_pipes | |
) | |
SELECT DISTINCT ON (r.geom, cl.cas_particulier, cl.description_cas_particulier) row_number() OVER () AS gid, r.lot, r.id_pipe, r.classe_precision_releve, | |
pt.categorie_max AS classe_precision_initiale_alfi, | |
r.geom, | |
cl.cas_particulier, | |
cl.description_cas_particulier | |
FROM tronc_horszone r | |
LEFT JOIN traitement.pods_al_pipes_technical_alfi_2154 pt | |
ON ST_DWithin(ST_lineInterpolatePoint(r.geom, 0.5),pt.geom,1) | |
LEFT JOIN traitement."CLASSEA_LIVRAISON" cl ON ST_DWithin(r.geom, ST_Transform(cl.geom,2154), 0.05) | |
); | |
DROP TABLE traitement."Qualif_trace" CASCADE; | |
TRUNCATE TABLE traitement."Qualif_trace"; | |
TRUNCATE TABLE traitement."CLASSEA_LIVRAISON"; | |
-- ajout d'une vue pour pouvoir générer une image de graphe camembert présentant le nombre de points par catégorie et par id_pipe/lot : | |
/* | |
-- DROP VIEW traitement.v_rapport_points; | |
CREATE OR REPLACE VIEW traitement.v_rapport_points AS ( | |
SELECT row_number() OVER () AS gid, | |
fd.lot, | |
fd.id_pipe, | |
count(*) AS nb_points, | |
qt.igds_level_name AS classe_precision | |
FROM traitement."Fusion__DET" fd | |
JOIN traitement."Qualif_trace" qt ON ST_DWithin (ST_SetSRID(ST_MakePoint(fd.x, fd.y),2154), qt.geom, 0.05 ) | |
group BY fd.lot, fd.id_pipe, qt.igds_level_name | |
); | |
-- DROP VIEW traitement.v_rapport_points2; | |
CREATE OR REPLACE VIEW traitement.v_rapport_points2 AS ( | |
SELECT DISTINCT row_number() OVER () AS gid, | |
fd.lot, | |
fd.id_pipe, | |
count(DISTINCT concat(fd.x::text, fd.y::TEXT)) AS nb_points, | |
qt.igds_level_name AS classe_precision, | |
cl.cas_particulier, | |
cl.description_cas_particulier | |
FROM traitement."Fusion__DET" fd | |
LEFT JOIN traitement."Qualif_trace" qt ON ST_DWithin (ST_SetSRID(ST_MakePoint(fd.x, fd.y),2154), qt.geom, 0.05 ) | |
LEFT JOIN traitement."CLASSEA_LIVRAISON" cl ON ST_DWithin(qt.geom, ST_Transform(cl.geom,2154), 0.05) | |
group BY fd.lot, fd.id_pipe, qt.igds_level_name, cl.cas_particulier, cl.description_cas_particulier | |
); | |
*/ | |
-- DROP VIEW traitement.v_rapport_points3; | |
CREATE OR REPLACE VIEW traitement.v_rapport_points3 AS ( | |
SELECT DISTINCT row_number() OVER () AS gid, | |
cl.numero_de_lot, | |
cl.id_pipe, | |
count(DISTINCT cl.geom) AS nb_points, | |
qt.igds_level_name AS classe_precision, | |
cl.cas_particulier, | |
cl.description_cas_particulier | |
FROM traitement."CLASSEA_LIVRAISON" cl | |
LEFT JOIN traitement."Qualif_trace" qt ON ST_DWithin (ST_Transform(cl.geom,2154), qt.geom, 0.05 ) | |
group BY cl.numero_de_lot, cl.id_pipe, qt.igds_level_name, cl.cas_particulier, cl.description_cas_particulier | |
); | |
CREATE OR REPLACE VIEW traitement.v_fusion_det_pts AS ( | |
SELECT *, | |
ST_SetSRID(ST_MakePoint(x, y),2154) AS geom | |
FROM traitement."Fusion__DET" | |
); | |
-- ajout d'une vue pour pouvoir générer une image de graphe camembert présentant les longueurs de linéaires par catégorie et par id_pipe/lot : | |
/* | |
-- DROP VIEW traitement.v_rapport_lineaire; | |
CREATE OR REPLACE VIEW traitement.v_rapport_lineaire AS ( | |
SELECT row_number() OVER () AS gid, | |
qt.lot, | |
qt.id_pipe, | |
round(sum(st_length(geom))::numeric,0) AS lineaire, | |
qt.igds_level_name AS classe_precision, | |
cl.cas_particulier, | |
cl.description_cas_particulier | |
FROM traitement."Qualif_trace" qt | |
LEFT JOIN traitement."CLASSEA_LIVRAISON" cl ON ST_DWithin(qt.geom,cl.geom,0.05) | |
WHERE cl.autres_classes_planimetrie IS NOT NULL | |
GROUP BY qt.lot, qt.id_pipe, qt.igds_level_name | |
); | |
-- DROP VIEW traitement.v_rapport_lineaire2; | |
CREATE OR REPLACE VIEW traitement.v_rapport_lineaire2 AS ( | |
SELECT distinct row_number() OVER () AS gid, | |
qt.lot, | |
qt.id_pipe, | |
round(sum(st_length(qt.geom))::numeric,0) AS lineaire, | |
qt.igds_level_name AS classe_precision, | |
cl.cas_particulier, | |
cl.description_cas_particulier | |
FROM traitement."Qualif_trace" qt | |
JOIN traitement."CLASSEA_LIVRAISON" cl ON ST_DWithin(qt.geom,ST_Transform(cl.geom,2154),0.05) | |
WHERE cl.autres_classes_planimetrie IS NOT NULL | |
GROUP BY qt.lot, qt.id_pipe, qt.igds_level_name, cl.cas_particulier, cl.description_cas_particulier | |
); | |
*/ | |
-- DROP VIEW traitement.v_rapport_lineaire3; | |
CREATE OR REPLACE VIEW traitement.v_rapport_lineaire3 AS ( | |
WITH sum_length AS (SELECT distinct row_number() OVER () AS gid, | |
qt.lot, | |
qt.id_pipe, | |
round(sum(st_length(qt.geom))::numeric,0) AS lineaire, | |
qt.igds_level_name AS classe_precision, | |
qt.geom | |
FROM traitement."Qualif_trace" qt | |
GROUP BY qt.lot, qt.id_pipe, qt.igds_level_name, qt.geom), | |
agg AS ( | |
SELECT DISTINCT row_number() OVER () AS gid, | |
sl.lot, | |
sl.id_pipe, | |
sl.lineaire, | |
sl.classe_precision, | |
cl.cas_particulier, | |
cl.description_cas_particulier | |
FROM sum_length sl | |
LEFT JOIN traitement."CLASSEA_LIVRAISON" cl ON ST_DWithin(sl.geom,ST_Transform(cl.geom,2154),0.05) | |
WHERE cl.autres_classes_planimetrie IS NOT NULL | |
GROUP BY sl.lot, | |
sl.id_pipe, | |
sl.lineaire, | |
sl.classe_precision, | |
cl.cas_particulier, | |
cl.description_cas_particulier) | |
SELECT DISTINCT row_number() OVER () AS gid, | |
lot, | |
id_pipe, | |
sum(lineaire) AS lineaire, | |
classe_precision, | |
cas_particulier, | |
description_cas_particulier | |
FROM agg | |
GROUP BY lot, id_pipe, classe_precision, cas_particulier, description_cas_particulier | |
); | |
CREATE OR REPLACE VIEW traitement.v_fusion_det_pts AS ( | |
SELECT *, | |
ST_SetSRID(ST_MakePoint(x, y),2154) AS geom | |
FROM traitement."Fusion__DET" | |
); | |
ALTER TABLE traitement."CLASSEA_LIVRAISON" | |
ALTER COLUMN geom TYPE geometry(geometry,4326) | |
USING ST_SetSRID(ST_Force2D(geom),4326); | |
--- modifs suite à détectin de doublons et erreurs sur le pipe 42 : | |
DELETE FROM terrain.lineaire_dwg WHERE nom_dwg ='xx_210_42_reprises_DET'; | |
DELETE FROM terrain.lineaire_dwg WHERE gid =201; | |
SELECT * FROM terrain.lineaire_dwg WHERE nom_dwg = 'NF_210_42_PAS-DE-CALAIS_20210417_CTRL' AND id_pipe='42'; | |
DELETE FROM terrain.lineaire_dwg WHERE nom_dwg = 'NF_210_42_PAS-DE-CALAIS_20210417_CTRL' AND id_pipe='42'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment