Skip to content

Instantly share code, notes, and snippets.

@CEZERT

CEZERT/sql_scripts_divers

Created September 22, 2022 07:59
Show Gist options
  • Save CEZERT/a133ff5f6c8735b3d540745c3a383f4f to your computer and use it in GitHub Desktop.
Save CEZERT/a133ff5f6c8735b3d540745c3a383f4f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
sql_scripts_divers
-- 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