/*
-- Fonction permettant de vérifier et corriger les erreurs de géométries et de topologies
-- inspiré entre autres par :
--      o https://makina-corpus.com/blog/metier/2013/utiliser-les-topologies-postgis-pour-nettoyer-un-filaire-de-voirie
--      o http://www.geoinformations.developpement-durable.gouv.fr/verification-et-corrections-des-geometries-a3522.html
-- Auteur: Guillaume Boitel <guillaume.boitel@portcros-parcnational.fr>
--          Parc National de Port Cros
-- Mise à jour le: 18/03/2019
-- Descripton :
-- Les erreurs corrigées sont :
--      o géométries nulles
--      o géométries de longueurs nulles
--      o boucles (loop) et enchevêtrements (knots)
--      o points en double
--      o point "inutile" (points allignés par exemple)
--      o intersection
--      o dépassement négatif (undershoot) avec tolérance de 1m
--      o dépassement positif (overshoot) avec tolérance de 1m
--      o superposition de tronçons
--      o suppression des pseudo-noeuds
-- En entrée :
--      o tableSrc : la table contenant les tronçons d'origine
--      o tronconsTopo : le schéma qui contiendra la topologie
--      o tableDest : la table qui contiendra les tronçons corrigés
-- Attention, cette fonction supprime :
--		o la topologie donnée dans le paramètre tronconsTopo
--		o la table donnée dans le paramètre tableDest
-- En sortie :
--      o on récupère la table des pseudo points (pas très utile)
--      => il faut donc faire une autre requête pour avoir les tronçons corrigés :
--      par ex. : SELECT * FROM troncons_finaux;
-- A faire : arriver à supprimer la topologie (la fonction DropTopology ne fonctionne pas en fin de script)
*/

CREATE OR REPLACE FUNCTION verif_topo(tableSrc varchar, tronconsTopo varchar, tableDest varchar) RETURNS SETOF RECORD AS
$BODY$
DECLARE
    r RECORD;
    tmpschema varchar;
BEGIN
	-- Gestion des noeuds en doublons ou inutiles, des géométrie nulle ou de longueur nulle
    EXECUTE '
        CREATE TEMP TABLE troncons AS (
            -- suppression des noeuds en doublons ou inutiles : ST_Simplify
            SELECT id, ST_Simplify(geom,0) AS geom	
            FROM  ' || tableSrc || '
            -- suppression des géometries nulles ou de longueur égale à 0 et
            WHERE not st_isempty(geom) and st_length(geom) > 0
        )';
    
    tmpschema := (SELECT nspname FROM pg_catalog.pg_namespace 
                    WHERE oid = pg_my_temp_schema());
                    
	-- Creation de la topologie
	CREATE EXTENSION IF NOT EXISTS postgis_topology;
    PERFORM topology.DropTopology(tronconsTopo)
		WHERE EXISTS ( 
		SELECT * 
			FROM topology.topology 
			WHERE name = tronconsTopo 
		);
	PERFORM topology.CreateTopology(tronconsTopo, 2154);
	PERFORM topology.AddTopoGeometryColumn(tronconsTopo, tmpschema, 'troncons', 'topo_geom', 'LINESTRING');
    
	-- Gestion des boucles et des dépassements
	EXECUTE 'UPDATE troncons SET topo_geom = topology.toTopoGeom(geom, '||quote_literal(tronconsTopo)||', 1, 1.0)'; -- Tolérance de 1.0 mètre
	
	-- Gestion des intersections
	EXECUTE '
    UPDATE troncons 
		SET geom = e.geom
		FROM '||tronconsTopo||'.edge e, '||tronconsTopo||'.relation rel
		WHERE e.edge_id = rel.element_id
	  		AND rel.topogeo_id = (troncons.topo_geom).id';
				  
	-- Suppression des morceaux en double :
	EXECUTE 'DROP TABLE IF EXISTS '||tableDest||' CASCADE';
	EXECUTE '
	CREATE TABLE '||tableDest||' AS (
		WITH doublons AS
			(SELECT a.id, a.geom 
			 FROM troncons a, troncons b 
			 WHERE a.id <> b.id 
				AND st_equals(a.geom, b.geom) 
				AND a.id < b.id)
		SELECT DISTINCT id, geom
			FROM troncons
			WHERE id NOT IN (SELECT id FROM doublons) 
				OR geom NOT IN (select geom FROM doublons)
	)';
	
	-- Suppression des pseudo noeuds
    FOR r IN EXECUTE '
        SELECT pt 
        	FROM (SELECT ST_startpoint(geom) AS pt 
				  		FROM '|| quote_ident(tableDest) ||'
        			UNION ALL
        			SELECT ST_endpoint(geom) AS pt 
						FROM '|| quote_ident(tableDest) ||') pt
        	GROUP BY pt HAVING count(*) = 2'
    LOOP
        -- pour chaque pseudo noeuds faire le lien entre les deux segments
		EXECUTE '
        INSERT INTO '|| quote_ident(tableDest) ||' VALUES (0,
            (SELECT ST_LineMerge(ST_Union(t.geom)) AS geom
            	FROM '|| quote_ident(tableDest) ||' t
            	WHERE ST_Equals(ST_StartPoint(t.geom), $1) 
                	OR ST_Equals(ST_EndPoint(t.geom), $1)))'
		USING r.pt;
        -- supprimer les deux segments
		EXECUTE '
        DELETE FROM '|| quote_ident(tableDest) ||'
            WHERE ST_Equals(ST_StartPoint(geom), $1) 
                OR ST_Equals(ST_EndPoint(geom), $1)'
		USING r.pt;
        RETURN NEXT r;
    END LOOP;
    
    -- Ménage
    -- PERFORM topology.DropTopology(tronconsTopo)
	DROP TABLE troncons;
    
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

-- Exemple d'execution
SELECT * FROM verif_topo('gt_pcpql_troncons_finaux', 'troncons_topo', 'troncons_finaux') as pt(geom geometry);
SELECT * FROM troncons_finaux;




-- Pour les dépassements négatifs et positifs de plus de 1m, 
-- j'ai préféré passer en vérification semi-automatique pour éviter des modifications automatiques trop importantes. 
-- Pour cela j'ai crée deux requêtes SQL qui me permettent de sélectionner tous les tronçons potentiellement en dépassement positif ou négatif, 
-- puis j'ai vérifié manuellement ces tronçons, ce qui permet de gagner du temps.

-- Requêtes de sélections des tronçons potentiellement en dépassement
-- Penser à remplacer GT_SEGMENT par le nom de votre table
-- Tronçons potentiellement en dépassement positif avec une longueur maximal indiqué (par défaut 50m)
WITH segment AS ( SELECT id, geom AS geometry FROM GT_SEGMENT),
        lmax AS (SELECT 50) --  longueur maximum du tronçon
SELECT * FROM (
        SELECT * FROM segment
        EXCEPT 
        SELECT DISTINCT s1.id, s1.geometry
        FROM segment s1,segment s2
        WHERE NOT st_equals(s1.geometry, s2.geometry)
        AND st_startpoint(s1.geometry) IN (st_startpoint(s2.geometry), st_endpoint(s2.geometry))
        ) a WHERE st_length(geometry) < (SELECT * FROM lmax)
UNION
SELECT * FROM (
        SELECT * FROM segment
        EXCEPT 
        SELECT DISTINCT s1.id, s1.geometry
        FROM segment s1, segment s2
        WHERE NOT st_equals(s1.geometry, s2.geometry)
        AND st_endpoint(s1.geometry) IN (st_startpoint(s2.geometry), st_endpoint(s2.geometry)) 
        ) b WHERE st_length(geometry) < (SELECT * FROM lmax)

-- Tronçons potentiellement en dépassement négatif à une distance maximum d'un autre tronçon (par défaut 10m)
WITH segment AS ( SELECT id, geom AS geometry FROM GT_SEGMENT),
        lmax AS (SELECT 10) -- distance maximum d’un autre tronçon
SELECT s1.id, s1.geometry
FROM (
                        SELECT * FROM segment
                        EXCEPT 
                        SELECT DISTINCT s1.id, s1.geometry
                        FROM segment s1,segment s2
                        WHERE NOT st_equals(s1.geometry, s2.geometry)
                                AND st_startpoint(s1.geometry) IN (st_startpoint(s2.geometry), st_endpoint(s2.geometry))
                        ) s1, segment s2
WHERE  NOT st_equals(s1.geometry, s2.geometry)
                        AND ST_distance(st_startpoint(s1.geometry), s2.geometry) < (SELECT * FROM lmax)
GROUP BY s1.id, s1.geometry
UNION
SELECT s1.id, s1.geometry
FROM (
                        SELECT * FROM segment
                        EXCEPT 
                        SELECT DISTINCT s1.id, s1.geometry
                        FROM segment s1,segment s2
                        WHERE NOT st_equals(s1.geometry, s2.geometry)
                                AND st_endpoint(s1.geometry) IN (st_startpoint(s2.geometry), st_endpoint(s2.geometry))
                        ) s1, segment s2
WHERE  NOT st_equals(s1.geometry, s2.geometry)
                        AND ST_distance(st_endpoint(s1.geometry), s2.geometry) < (SELECT * FROM lmax)

GROUP BY s1.id, s1.geometry
