-- Build ways as geometric objects
-- TESTS TODO
-- Check that there two places and only two for way on table way_partition
-- Check that there is no missing START_PL/END_PL on table ways

DELETE FROM ways;
DELETE FROM way_places;
DELETE FROM way_angles;

-- Update place edges with way index

UPDATE place_edges SET 
WAY = (SELECT WAY FROM way_partition WHERE way_partition.EDGE=place_edges.OGC_FID)
;

-- Cleanup ways attributes on edges

UPDATE place_edges SET RTOPO = NULL, ACCES = NULL;

-- Compute Way geometry (as MULTILINESTRING)

INSERT INTO ways(GEOMETRY,WAY_ID)
SELECT ST_Multi(ST_LineMerge(ST_Collect(e.GEOMETRY))), pe.WAY
FROM place_edges as pe, edges as e
WHERE pe.START_VTX = e.START_VTX and pe.END_VTX = e.END_VTX
GROUP BY pe.WAY
;

-- Update geometries for edges contained into the places
UPDATE ways SET geometry = t.geom
FROM (
	SELECT ST_Multi(ST_Union(ST_Collect(e.GEOMETRY), w.geometry)) geom, w.WAY_ID
	FROM ways as w JOIN (SELECT * FROM edges WHERE (START_VTX, END_VTX) NOT IN (SELECT START_VTX, END_VTX FROM place_edges)) as e
		ON e.START_VTX IN (SELECT START_VTX from place_edges where way = w.way_id UNION SELECT END_VTX from place_edges where way = w.way_id)
		and e.END_VTX IN (SELECT START_VTX from place_edges where way = w.way_id UNION SELECT END_VTX from place_edges where way = w.way_id)
	GROUP BY w.WAY_ID
) t
WHERE t.way_id = ways.way_id;


-- Build way/places association

INSERT INTO way_places(WAY_ID,PLACE)
SELECT way, pl
FROM (
    SELECT WAY as way, START_PL AS pl FROM place_edges
    UNION
    SELECT WAY as way, END_PL AS pl FROM place_edges
)
;

-- Compute basic way attributes

-- Tests TODO:
-- DEGREE > 0
-- CONNECTIVITY > 0


-- Length
UPDATE ways SET LENGTH = (
    SELECT ST_Length(ways.GEOMETRY) + Sum(p.DIST) FROM way_partition AS p WHERE p.WAY=ways.WAY_ID
)
;

-- Degree
-- Number of other intersecting ways
UPDATE ways SET DEGREE = (
    SELECT Count(DISTINCT WAY_ID) FROM way_places 
    WHERE PLACE IN (SELECT PLACE FROM way_places WHERE WAY_ID=ways.WAY_ID) 
    AND WAY_ID<>ways.WAY_ID 
)
;

-- Connectivity
-- Number of arcs in the viary graph intersected by a way wich are
-- not part of that way (sum by place)
 
UPDATE ways SET CONN = (SELECT Count(1) FROM (
    SELECT OGC_FID, START_PL FROM place_edges
    WHERE START_PL IN (SELECT PLACE FROM way_places WHERE WAY_ID=ways.WAY_ID)
    AND WAY<>ways.WAY_ID
    UNION
    SELECT OGC_FID, END_PL FROM place_edges
    WHERE END_PL IN (SELECT PLACE FROM way_places WHERE WAY_ID=ways.WAY_ID)
    AND WAY<>ways.WAY_ID)
)
;

-- Spacing
UPDATE ways SET SPACING = (SELECT ways.LENGTH/ways.DEGREE)
WHERE DEGREE>0
;

-- Degree desserte
UPDATE ways SET CROSS = CONN - DEGREE;

-- create dual graph edges
CREATE TABLE way_edges AS
SELECT wp1.way_id way1, wp2.way_id way2, count(distinct wp1.place) nb_place
FROM way_places wp1 JOIN way_places wp2 ON wp1.place = wp2.place and wp1.way_id < wp2.way_id
GROUP BY wp1.way_id, wp2.way_id;

-- Clean up
--VACUUM
;


