Le portail francophone de la géomatique

  create table "test_interpo"."tmppolyg7123seldepare" as select distinct
row_number() over() as idpoly,D.drval1,D.drval2,  T.id_triangle, D.id as id,

  st_collectionextract(st_intersection(D.geom, T.geometryexplose),3) as geom
        From "test_interpo"."7123_DEPARE_Polygon" D inner join
"test_interpo"."7123_TRIANGLE_DISTINCT_sel" T on st_intersects(D.geom,
T.geometryexplose);

create table "test_interpo"."node2update" as WITH t AS -- Transfor polygons in sets of points
    (SELECT idpoly,id_triangle,
            st_dumppoints(geom) AS dump
     FROM "test_interpo"."tmppolyg7123seldepare"),
f AS -- Get the geometry and the indexes from the sets of points
    (SELECT t.idpoly,t.id_triangle,
           (t.dump).path[1] AS part,
           (t.dump).path[3] AS vertex,
           (t.dump).geom AS geom
     FROM t)
-- Get all points filtering the last point for each geometry part
SELECT row_number() OVER () AS gid, -- Creating a unique id
       f.idpoly,
       f.id_triangle,
       f.part,
       f.vertex,
       ST_X(f.geom) as x, -- Get point's X coordinate
       ST_Y(f.geom) as y, -- Get point's Y coordinate
       ST_Z(f.geom) as z,
       f.geom::geometry('POINTZ',4326) as geom -- make sure of the resulting geometry type
FROM f ;

drop table if exists "test_interpo"."Node2_Valdco" cascade;
 Create table
"test_interpo"."Node2_Valdco"
as
select N.*, L.valdco as Valdco
from
"test_interpo". "node2update" N inner join "test_interpo". "7123_DEPCNT_Arc" L
on st_dwithin (N.geom, L.geom,0.0000001);
 
-- noeuds polygone seulement les sondes
drop table if exists "test_interpo"."node2_SOUNDG" cascade;
create table "test_interpo". "node2_SOUNDG" as
select distinct n.* from "test_interpo"."node2update" n inner join "test_interpo". "7123_SOUNDG_UWTRO" s
 on   st_dwithin(n.geom,s.geom,0.00000001);
 
 
--Reconstitution table Node a jour des bons Z
drop table if exists "test_interpo"."Node2Z" cascade;
create table  "test_interpo"."Node2Z" as

select  gid,id_triangle, part, idpoly, vertex,x,y,z, st_setsrid((st_makepoint(st_X(geom),st_y(geom),valdco)),4326) geom

from   "test_interpo"."Node2_Valdco"

union all

select gid,id_triangle, part, idpoly, vertex,x,y,z,st_setsrid((st_makepoint(st_X(geom),st_y(geom),st_z(geom))),4326) from "test_interpo". "node2_SOUNDG";

select st_makepolygon(st_makeline(geom)) from
(SELECT idpoly,
             count(gid)
       FROM test_interpo."Node2Z"
       GROUP BY idpoly order by idpoly) as polyg inner join (SELECT idpoly,
            geom
       FROM test_interpo."Node2Z") t on  polyg.idpoly=t.idpoly

drop table if exists "T067"."tmppolyg067seldepare" cascade;
  create table "T067"."tmppolyg067seldepare" as select distinct
D.drval1,D.drval2,  T.id_triangle, D.id as id,

  (st_dump(st_collectionextract(st_intersection(D.geom, T.geometryexplose),3))).geom as geom
        From "T067"."067_DEPARE_Polygon" D inner join
"T067"."067_TRIANGLE_DISTINCT_sel" T on st_intersects(D.geom,
T.geometryexplose);
alter table "T067"."tmppolyg067seldepare"add column idpoly bigserial;

--reconstruire polygone
 create table "T067"."Newpoly" as select idpoly, st_convexhull( st_collect(geom) ) as geom, st_asewkt(st_convexhull( st_collect(geom) ))

from 
 "T067"."Node2Z" group by idpoly order by idpoly ;