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
|
--création arêtes des triangles
CREATE OR REPLACE VIEW test_interpo."7123_Triangletest_segment" AS
SELECT
row_number() over () as id,
ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) AS sp,
ST_PointN(geom, generate_series(2, ST_NPoints(geom) )) AS ep,
id_triangle
FROM
-- extract the individual linestrings
(SELECT id_triangle, (ST_Dump(ST_Boundary(geometryexplose))).geom
FROM test_interpo."7123_TRIANGLE_DISTINCT_test_interpo") AS linestrings ;
--ordonnancement des segments
CREATE Table test_interpo."7123_Triangletest_segment2"
AS SELECT row_number() over () as id,id_triangle, sp, ep, st_zmin(sp) minZ, st_zmax(ep) maxZ, case when
st_zmin(sp)<st_zmax(ep) then st_makeline(sp,ep)
when
st_zmin(sp)>st_zmax(ep) then st_makeline(ep,sp)
end geom
FROM test_interpo."7123_Triangletest_segment";
--pour Z tous les 0.10
create table test_interpo."7123_test_intervalle_Tr" as
select row_number() over() as id,id_triangle,sp, ep, geom, st_asewkt(geom) as essai, st_length(geom) as longueursegment,
st_z(sp) as Zsp, st_z(ep) as Zep,( st_Z(st_endpoint(geom))-st_Z(st_startpoint(geom))) as diff,
round(@( st_Z(st_endpoint(geom))- st_Z(st_startpoint(geom)))*10)::int as nbre_sgmt,st_length(geom)/(@( st_z(sp)-st_z(ep))*10) as lgueur_intervalle,
st_Z(st_startpoint(geom)) as Zmin_sgmt, st_Z(st_endpoint(geom)) as Zmax_sgmt
from test_interpo."7123_Triangletest_segment2"
--creation table avec id_segment
create table test_interpo."7123_test_intervalle_Tr_segmt" as select *
from (select tr.*, 2 id_segment from test_interpo."7123_test_intervalle_Tr" tr join
(select id_triangle, min(nbre_sgmt) as min_pt from test_interpo."7123_test_intervalle_Tr" group by id_triangle) t on tr.id_triangle = t.id_triangle and tr.nbre_sgmt=t.min_pt
union
select tr.*, 1 id_segment from test_interpo."7123_test_intervalle_Tr" tr join
(select id_triangle, max(nbre_sgmt) as max_pt from test_interpo."7123_test_intervalle_Tr" group by id_triangle) t on tr.id_triangle = t.id_triangle and tr.nbre_sgmt=t.max_pt
union
select tr.*, 3 id_segment from test_interpo."7123_test_intervalle_Tr" tr join
(select id_triangle, max(nbre_sgmt) as max_pt,min(nbre_sgmt) as min_pt from test_interpo."7123_test_intervalle_Tr" group by id_triangle) t on tr.id_triangle = t.id_triangle and tr.nbre_sgmt<> t.max_pt and tr.nbre_sgmt<> t.min_pt
union
select tr.*, 4 id_segment from test_interpo."7123_test_intervalle_Tr" tr join
(select id_triangle, max(nbre_sgmt) as max_pt,min(nbre_sgmt) as min_pt from test_interpo."7123_test_intervalle_Tr" group by id_triangle) t on tr.id_triangle = t.id_triangle and tr.nbre_sgmt = 0 or tr.nbre_sgmt is null
) tmp;
alter table test_interpo."7123_test_intervalle_Tr_segmt" add column id_sgmt_triang varchar(12);
update test_interpo."7123_test_intervalle_Tr_segmt" set id_sgmt_triang = concat(id_segment,'_',id_triangle);
--decoupage ligne par nombre de points
create or replace view test_interpo."7123_ligne_segmentize_Tr"as
select id_segment, id_triangle, nbre_sgmt, id_sgmt_triang,st_segmentize(geom,lgueur_intervalle) as geom, st_asewkt(st_segmentize(geom,lgueur_intervalle))
from test_interpo."7123_test_intervalle_Tr_segmt";
--Récupération de chaque point de segment
CREATE OR REPLACE VIEW test_interpo."7123_Triangletest_segmentize_View" AS
SELECT
id_segment,
ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) AS sp,
ST_PointN(geom, generate_series(2, ST_NPoints(geom) )) AS ep,
id_triangle,nbre_sgmt, id_sgmt_triang
FROM
-- extract the individual linestrings
(SELECT id_segment,id_triangle,nbre_sgmt, id_sgmt_triang, (ST_Dump(geom)).geom
FROM test_interpo."7123_ligne_segmentize_Tr") AS linestrings ;
create table test_interpo."7123_Triangletest_segmentize_T" as select *, st_Z(sp) as Zsp,st_Z(ep) as Zep from test_interpo."7123_Triangletest_segmentize_View";
alter table test_interpo."7123_Triangletest_segmentize_T" alter column Zsp type decimal,alter column Zep type decimal ;
create table test_interpo."7123_Triangletest_segmentize_T_arrondi" as select id_segment, id_triangle,concat(id_segment,'_',id_triangle) as id_sgmt_triang,sp, ep,zsp , round (Zsp,1) as Zsp_arrondi, zep, round (Zep,1)as Zep_arrondi from test_interpo."7123_Triangletest_segmentize_T"; |
Partager