-- CREATE NODES TABLE
CREATE TABLE nodes (geom GEOMETRY);
-- SELECT UpdateGeometrySRID('nodes', 'geom', 4326) from nodes;

-- INSERT START-, END- and INTERSECTION POINTS
INSERT INTO nodes SELECT ST_StartPoint(geom) from network2clean;
INSERT INTO nodes SELECT ST_EndPoint(geom) from network2clean;
INSERT INTO nodes 
SELECT (ST_INTERSECTION(ne.geom, ne2.geom)) 
FROM 
	(SELECT id, geom FROM network2clean
		WHERE highway NOT IN ('motorway', 'motorway_link') 
			AND bridge IS NULL 
			AND tunnel IS NULL) ne, 
	(SELECT id, geom FROM network2clean 
		WHERE highway NOT IN ('motorway', 'motorway_link') 
			AND bridge IS NULL 
			AND (tunnel IS NULL OR tunnel = 'no')) ne2 
WHERE ST_INTERSECTS(ne.geom, ne2.geom) 
	AND ne.id < ne2.id;

-- VISUAL CHECK OF NODES GEOMETRY
-- SELECT DISTINCT st_geometrytype(geom), count(*) FROM nodes GROUP BY st_geometrytype(geom);
-- CREATE TABLE TO CHECK
-- CREATE TABLE check_GEOMCOLLECTION AS SELECT * FROM nodes WHERE ST_GEOMETRYTYPE(geom) = 'ST_GeometryCollection';
-- CREATE TABLE check_LINESTRING AS SELECT * FROM nodes WHERE ST_GEOMETRYTYPE(geom) = 'ST_LineString';
-- CREATE TABLE check_MULTILINESTRING AS SELECT * FROM nodes WHERE ST_GEOMETRYTYPE(geom) = 'ST_MultiLineString';
-- CREATE TABLE check_MULTIPOINT AS SELECT * FROM nodes WHERE ST_GEOMETRYTYPE(geom) = 'ST_MultiPoint';
-- CREATE TABLE check_NULL AS SELECT * FROM nodes WHERE ST_GEOMETRYTYPE(geom) IS NULL;

-- ONLY KEEP VALID SINGLE POINTS
INSERT INTO nodes SELECT (ST_DUMP(geom)).geom FROM nodes WHERE ST_GEOMETRYTYPE(geom) = 'ST_MultiPoint';

-- CLEAN TABLE
DELETE FROM nodes WHERE ST_GEOMETRYTYPE(geom) != 'ST_Point';
DELETE FROM nodes WHERE geom IS NULL;

-- CREATE INDEX
DROP INDEX IF EXISTS nodes_gidx;
CREATE INDEX nodes_gidx ON nodes USING GIST (geom);

-- ADD UNIQUE NODE IDENTIFIER
ALTER TABLE nodes ADD COLUMN id serial NOT NULL;

-- DELETE DUPLICATES
DELETE FROM nodes WHERE nodes.id IN (SELECT n2.id from nodes n1, nodes n2 where ST_EQUALS(n1.geom,n2.geom) and n1.id < n2.id);

SELECT count(*) from nodes;
