Le portail francophone de la géomatique

-- TABLE DU CROISEMENT PRO / PSG
DROP TABLE IF EXISTS surface_unitaire_01.propsg_01;

-- 1. Intersection
CREATE TABLE surface_unitaire_01.propsg_01 AS
SELECT o.code_onf, p.code AS code_psg, ST_SetSRID((ST_Dump(ST_Intersection(o.geom, p.geom))).geom, 932006) AS geom
FROM surface_unitaire_01.pro_2018_simple_01 o
    INNER JOIN surface_unitaire_01.psg_2018_simple_01 p ON (o.geom && p.geom AND ST_Intersects(o.geom, p.geom));

-- Nettoyage des géométries invalides
DELETE FROM surface_unitaire_01.propsg_01 WHERE ST_GeometryType(geom) != 'ST_Polygon';

CREATE INDEX sp_idx_propsg_01_geom ON surface_unitaire_01.propsg_01 USING GIST (geom);

ANALYZE surface_unitaire_01.propsg_01;

-- 2. Différences PRO - PSG et PSG - PRO
CREATE TEMP TABLE pro_union AS 
SELECT code_onf, ST_Union(geom) AS geom
FROM surface_unitaire_01.propsg_01
GROUP BY code_onf;

CREATE INDEX sp_idx_pro_union_geom ON pro_union USING GIST (geom);
ANALYZE pro_union;

CREATE TEMP TABLE psg_union AS 
SELECT code_psg, ST_Union(geom) AS geom
FROM surface_unitaire_01.propsg_01
GROUP BY code_psg;

CREATE INDEX sp_idx_psg_union_geom ON psg_union USING GIST (geom);
ANALYZE psg_union;

DROP INDEX surface_unitaire_01.sp_idx_propsg_01_geom;

INSERT INTO surface_unitaire_01.propsg_01
SELECT o.code_onf, NULL::VARCHAR(3) AS code_psg, (ST_Dump(ST_Difference(o.geom, ST_SetSRID(COALESCE(t1.geom, 'GEOMETRYCOLLECTION EMPTY'::GEOMETRY), 932006)))).geom AS geom
FROM surface_unitaire_01.pro_2018_simple_01 o
    LEFT JOIN pro_union t1 ON o.code_onf = t1.code_onf
UNION 
SELECT NULL::VARCHAR(3) AS code_onf, p.code AS code_psg, (ST_Dump(ST_Difference(p.geom, ST_SetSRID(COALESCE(t2.geom, 'GEOMETRYCOLLECTION EMPTY'::GEOMETRY), 932006)))).geom AS geom
FROM surface_unitaire_01.psg_2018_simple_01 p
    LEFT JOIN psg_union t2 ON p.code = t2.code_psg;

-- Nettoyage des géométries invalides
SELECT DISTINCT ST_GeometryType(geom) FROM surface_unitaire_01.propsg_01;
SELECT DISTINCT ST_SRID(geom) FROM surface_unitaire_01.propsg_01;
SELECT COUNT(*) FROM surface_unitaire_01.propsg_01 WHERE NOT ST_IsValid(geom);

DELETE FROM surface_unitaire_01.propsg_01 WHERE ST_GeometryType(geom) != 'ST_Polygon';

CREATE INDEX sp_idx_propsg_01_geom ON surface_unitaire_01.propsg_01 USING GIST (geom); 

ANALYZE surface_unitaire_01.propsg_01;

DROP TABLE pro_union;
DROP TABLE psg_union;

-- contrôles de surfaces
SELECT 'couche onf' AS origine, code_onf, SUM(ST_Area(geom)) AS surface_total
FROM surface_unitaire_01.pro_2018_simple_01
GROUP BY origine, code_onf
UNION 
SELECT 'croisement' AS origine, code_onf, SUM(ST_Area(geom)) AS surface_total
FROM surface_unitaire_01.propsg_01
GROUP BY origine, code_onf
ORDER BY code_onf, origine;

SELECT 'couche psg' AS origine, code AS code_psg, SUM(ST_Area(geom)) AS surface_total
FROM surface_unitaire_01.psg_2018_simple_01
GROUP BY origine, code_psg
UNION 
SELECT 'croisement' AS origine, code_psg, SUM(ST_Area(geom)) AS surface_total
FROM surface_unitaire_01.propsg_01
GROUP BY origine, code_psg
ORDER BY code_psg, origine;

SELECT DISTINCT st_geometrytype(geom)
FROM surface_unitaire_01.bd_foret_01_simple

SELECT id_foret, ST_NPoints(geom), ST_MemSize(geom)
FROM surface_unitaire_01.bd_foret_01_simple
ORDER BY ST_NPoints DESC;

SELECT COUNT(*), COUNT(*) FILTER (WHERE ST_MemSize(geom) > 8192)
FROM surface_unitaire_01.bd_foret_01_simple;

CREATE TABLE surface_unitaire_01.bd_foret_01_simple_sub AS
SELECT id_foret, tfv, ST_SubDivide(geom) AS geom
FROM surface_unitaire_01.bd_foret_01_simple;

CREATE INDEX idx_foret_geom_sub_01 ON surface_unitaire_01.bd_foret_01_simple_sub USING GIST (geom);

CREATE TABLE surface_unitaire_01.propsg_01_sub
AS SELECT ST_SUBDIVIDE(geom) as geom, code_onf,code_psg
FROM surface_unitaire_01.propsg_01
-- TABLE DU CROISEMENT BD_FORET / PRO_PSG
DROP TABLE IF EXISTS surface_unitaire_01.final_cedric_01;

-- 1. Intersection
CREATE TABLE surface_unitaire_01.final_cedric_01 AS
SELECT p.code_onf, p.code_psg, b.tfv, ST_SetSRID((ST_Dump(ST_Intersection(ST_Buffer(p.geom, 0.0000001), b.geom))).geom, 932006) AS geom
FROM surface_unitaire_01.propsg_01 p
    INNER JOIN surface_unitaire_01.bd_foret_01_simple_sub b ON (p.geom && b.geom AND ST_Intersects(p.geom, b.geom));

-- Nettoyage des géométries invalides
SELECT DISTINCT ST_GeometryType(geom) FROM surface_unitaire_01.final_cedric_01;

DELETE FROM surface_unitaire_01.final_cedric_01 WHERE ST_GeometryType(geom) != 'ST_Polygon';

CREATE INDEX sp_idx_final_cedric_01_geom ON surface_unitaire_01.final_cedric_01 USING GIST (geom); 

ANALYZE surface_unitaire_01.final_cedric_01;

-- 2. Différences BD_FORET - PRO_PSG et PRO_PSG - BD_FORET
DROP TABLE IF EXISTS pro_psg;
CREATE TEMP TABLE pro_psg AS 
SELECT code_onf, code_psg,geom
FROM surface_unitaire_01.final_cedric_01;

CREATE INDEX sp_idx_pro_psg_geom ON pro_psg USING GIST (geom);
ANALYZE pro_psg;

DROP TABLE IF EXISTS tfv;
CREATE TEMP TABLE tfv AS 
SELECT tfv, geom
FROM surface_unitaire_01.final_cedric_01;

CREATE INDEX sp_idx_tfv_geom ON tfv USING GIST (geom);
ANALYZE tfv;

DROP INDEX surface_unitaire_01.sp_idx_final_cedric_01_geom;

INSERT INTO surface_unitaire_01.final_cedric_01
SELECT p.code_onf, p.code_psg, NULL::VARCHAR(10) AS tfv, (ST_Dump(ST_Difference(p.geom, ST_SetSRID(COALESCE(t1.geom, 'GEOMETRYCOLLECTION EMPTY'::GEOMETRY), 932006)))).geom AS geom
FROM surface_unitaire_01.propsg_01_sub p
    LEFT JOIN pro_psg as t1 ON p.code_onf = t1.code_onf AND p.code_psg = t1.code_psg
UNION 
SELECT NULL::VARCHAR(3) AS code_onf, NULL::VARCHAR(3) AS code_psg, t.tfv, (ST_Dump(ST_Difference(t.geom, ST_SetSRID(COALESCE(t2.geom, 'GEOMETRYCOLLECTION EMPTY'::GEOMETRY), 932006)))).geom AS geom
FROM surface_unitaire_01.bd_foret_01_simple_sub t
    LEFT JOIN tfv t2 ON t.tfv = t2.tfv;

CREATE INDEX sp_idx_final_cedric_01_geom ON surface_unitaire_01.final_cedric_01 USING GIST (geom); 

ANALYZE surface_unitaire_01.propsg_01;

DROP TABLE pro_psg_union;
DROP TABLE IF EXISTS tfv_union;
DROP TABLE tfv;