raod_network_extraction_through_straight_skeleton_and_filtering

SET search_path to patch_connectivity,patch_connectivity_topo,  benchmark_cassette_2013, test_grouping, public, topology ;

ST_StraightSkeleton(geometry geom)

SELECT ST_IsSImple(total_area)
FROM total_area_2

DROP TABLE IF EXISTS total_area_2 ; 
CREATE TABLE total_area_2 AS 

SELECT 1 AS id, ST_Multi(
		(ST_Dump(
			ST_BUffer(
			ST_BUffer(ST_Union (
				ST_Buffer(patch_centroid,1 )
			),2)
			,-2)
		)).geom )::geometry(multipolygon,932011)AS total_area
FROM patch_node_table; 


UPDATE total_area_2 set total_area =  ST_Multi(ST_SimplifyPreserveTopology(total_area,1) )  ; 
UPDATE total_area_2 set total_area = st_makevalid(total_area); 

DROP TABLE IF EXISTS total_area_3 ; 
CREATE TABLE total_area_3 AS 
SELECT row_number() over() as id, dmp.geom as total_area
FROM total_area_2, st_dump(total_area) AS dmp ; 


SELECT ST_IsSImple(total_area)
FROM total_area ; 


DROP TABLE IF EXISTS sskeleton_2 ; 
CREATE TABLE sskeleton_2 AS 
SELECT row_number() over() as id,  ST_StraightSkeleton(total_area) sskeleton
FROM total_area_3 ;  


DROP TABLE IF EXISTS filtered_sskeleton ;
CREATE TABLE filtered_sskeleton AS 
WITH slines AS (
	SELECT dmp.path, dmp.geom
	FROM sskeleton_2, st_dump(sskeleton) as dmp
)
, overal_exteriorring AS (
	SELECT dmp.path[1], ST_ExteriorRing(dmp.geom) AS geom
	FROM total_area_3,  ST_DumpRings(total_area) AS dmp
	 
)
SELECT *
FROM slines
WHERE not exists
(	SELECT 1 FROM overal_exteriorring as oe WHERE ST_DWithin(oe.geom, slines.geom,0.5) AND ST_Length( slines.geom) < 16
) ; 

--UPDATE filtered_sskeleton SET geom = ST_SImplifyPreserveTopology(geom,10)

DROP TABLE IF EXISTS sewed_sskeleton ;
CREATE TABLE sewed_sskeleton AS 
with sewed AS (
SELECT ST_LineMerge(ST_Collect(geom)) as se
FROM filtered_sskeleton
) 
SELECT dmp.path, dmp.geom
FROM sewed, st_dump(se) AS dmp

CREATE EXTENSION IF NOT EXISTS postgis_topology ; 

SELECT DropTopology('patch_connectivity_topo') ; 
SELECT CreateTopology('patch_connectivity_topo', 932011, 0.1, hasz:=False); --2 

--adding filtered_skeleton result to topology 

SELECT TopoGeo_AddLineString('patch_connectivity_topo', geom,0.1)
FROM sewed_sskeleton ; 

--removing edges connected to node with 1 connectivity
WITH all_node_edge_pair AS (
	SELECT edge_id, start_node as node_id
	FROM edge_data
	UNION 
	SELECT edge_id, end_node as node_id
	FROM edge_data
)
  ,one_connected_node AS (
	SELECT DISTINCT ON (edges[1]) node_id, edges[1] AS edge_id, edge_per_node
	FROM (
	SELECT node_id, array_agg(edge_id) as edges ,  count(*)  as edge_per_node
	FROM all_node_edge_pair
	GROUP BY node_id) as sub
	WHERE edge_per_node <2
) 
,deleting_edges AS (
	SELECT ST_RemEdgeModFace('patch_connectivity_topo', edge_id)
	FROM one_connected_node NATURAL JOIN edge_data
	WHERE ST_Length(geom) < 15
)
,deleting_nodes AS (
	SELECT  (SELECT count(*) FROM deleting_edges) AS needed_to_be_executed_afterward
		, ST_RemoveIsoNode('patch_connectivity_topo',node_id)
	FROM node 
	WHERE  rc_IsNodeIsolated('patch_connectivity_topo', node_id ) = TRUE
)
SELECT (SELECT count(*) FROM deleting_edges), ( SELECT count(*) FROM deleting_nodes) ; 



-- removing the 2 connected nodes and healing edges
 SELECT rc_heal_edge_sequentially('patch_connectivity_topo') ; --note : ùmust be done iteratively ! 

 --simplifying the edges

 SELECT  ST_ChangeEdgeGeom('patch_connectivity_topo',edge_id, ST_SimplifyPreserveTopology(geom,3))
 FROM edge_data

  

DROP TABLE IF EXISTS sskeleton_2 ; 
CREATE TABLE sskeleton_2 AS 
WITH  rings AS (
	SELECT dmp.path[1], dmp.geom
	FROM total_area_3,  ST_DumpRings(total_area) AS dmp
	WHERE id = 63  
		AND ST_Area(dmp.geom) > 10
)
, outer_ring AS (
	SELECT ST_ExteriorRing(geom) As geom
	FROM rings
	WHERE path = 0
)
, inner_ring AS (
	SELECT array_agg(ST_ExteriorRing(geom)) As geoms
	FROM rings
	WHERE path != 0
)

/*
SELECT *
FROM rings AS r1
WHERE path != 0 AND  EXISTS 
(SELECT 1 FROM rings AS r2 WHERE ST_DWIthin(r2.geom, r1.geom,0.1) = true AND r1.path != r2.path AND r2.path != 0)
*/
SELECT  1 as id, ST_StraightSkeleton(ST_MakePolygon(ori.geom, ir.geoms))
FROM outer_ring AS ori, inner_ring AS ir

-- truncate sskeleton
DROP FUNCTION IF EXISTS rc_heal_edge_sequentially(varchar ); 
CREATE OR REPLACE FUNCTION rc_heal_edge_sequentially( IN atopology  varchar   )
RETURNS VOID AS
$BODY$
		--@brief this function recusrively find node with exactly 2 incoming edge, and fuse those edges
		DECLARE  
			_loop boolean := true ; 
			_r record ; 
			_useless record ; 
		BEGIN 
			
-- removing the nodes that have a 2 connectivity : healing edges 
		WHILE _loop = TRUE  
		LOOP
			WITH all_node_edge_pair AS (
				SELECT edge_id, start_node as node_id
				FROM edge_data
				UNION 
				SELECT edge_id, end_node as node_id
				FROM edge_data
			)
			  ,one_connected_node AS (
				SELECT DISTINCT ON (edges[1]) node_id, edges[1] AS edge_id, edge_per_node
				FROM (
				SELECT node_id, array_agg(edge_id) as edges ,  count(*)  as edge_per_node
				FROM all_node_edge_pair
				GROUP BY node_id) as sub
				WHERE edge_per_node = 2
				LIMIT 1
			)
			SELECT oc.node_id, oc.edge_id AS edge_id1, ed.edge_id AS edge_id2 INTO _r -- , ST_ModEdgeHeal('patch_connectivity_topo',oc.edge_id, ed.edge_id )
			FROM one_connected_node AS oc   LEFT OUTER JOIN edge_data AS ed ON ( (ed.start_node = oc.node_id OR ed.end_node = oc.node_id ) AND ed.edge_id != oc.edge_id) ; 

			IF _r IS NULL THEN 
				_loop :=FALSE ; EXIT; 
			ELSE
				SELECT ST_ModEdgeHeal(quote_ident(atopology),_r.edge_id1, _r.edge_id2 )INTO _useless ; 
			END IF; 
	END LOOP; 
			RETURN; 
		END ;
	$BODY$
LANGUAGE plpgsql VOLATILE;