-- Simple Road Merge

DROP TABLE IF EXISTS example10;

CREATE TABLE example10 (id serial, road geometry(linestring));
INSERT INTO example10 (road)
VALUES 
    (ST_GeomFromText('LINESTRING(-2 -2, -1 -1)')),
    (ST_GeomFromText('LINESTRING(-1 -1, 0 0)')),
    (ST_GeomFromText('LINESTRING(1 1, 2 2)')),
    (ST_GeomFromText('LINESTRING(0 0, 1 1)'))
;

SELECT ST_AsText(ST_Union(road)) FROM example10;
SELECT ST_AsText(ST_LineMerge(ST_Union(road))) FROM example10;
SELECT ST_AsText(ST_Simplify(ST_LineMerge(ST_Union(road)),5)) FROM example10;

-- Duplicate LineString

DROP TABLE IF EXISTS example12;

CREATE TABLE example12 (id serial, name char(1), road geometry(linestring), attribution text);
INSERT INTO example12 (name, road, attribution)
VALUES 
    ('S', ST_GeomFromText('LINESTRING(-2 -2, -1 -1)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(-1 -1, 0 0)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(1 1, 2 2)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(0 0, 1 1)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(1 1, 2 2)'), 'Bastien'),
    ('S', ST_GeomFromText('LINESTRING(0 0, 1 1)'), 'Bastien')
;

SELECT ST_AsText(ST_Union(road)) FROM example12;

-- Reversed LineString

DROP TABLE IF EXISTS example13;

CREATE TABLE example13 (id serial, name char(1), road geometry(linestring), attribution text);
INSERT INTO example13 (name, road, attribution)
VALUES 
    ('S', ST_GeomFromText('LINESTRING(-2 -2, -1 -1)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(-1 -1, 0 0)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(1 1, 2 2)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(0 0, 1 1)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(1 1, 2 2)'), 'Bastien'),
    ('S', ST_GeomFromText('LINESTRING(0 0, 1 1)'), 'Bastien')
    ('S', ST_GeomFromText('LINESTRING(-1 -1, 2 2)'), 'Clement'),
    ('S', ST_GeomFromText('LINESTRING(-2 -2, 2 2)'), 'Clement')
;

SELECT ST_AsText(ST_Union(road)) FROM example13;

-- LineString within LineString

DROP TABLE IF EXISTS example14;

CREATE TABLE example14 (id serial, name char(1), road geometry(linestring), attribution text);
INSERT INTO example14 (name, road, attribution)
VALUES 
    ('S', ST_GeomFromText('LINESTRING(-2 -2, -1 -1)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(-1 -1, 0 0)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(1 1, 2 2)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(0 0, 1 1)'), 'Antoine'),
    ('S', ST_GeomFromText('LINESTRING(1 1, 2 2)'), 'Bastien'),
    ('S', ST_GeomFromText('LINESTRING(0 0, 1 1)'), 'Bastien'),
    ('S', ST_GeomFromText('LINESTRING(-1 -1, 2 2)'), 'Clement'),
    ('S', ST_GeomFromText('LINESTRING(-2 -2, 2 2)'), 'Clement'),
    ('S', ST_GeomFromText('LINESTRING(-.5 -.5, .25 .25)'), 'Dorian'),
    ('S', ST_GeomFromText('LINESTRING(' || -pi()/2 || ' ' || -pi()/2 || ',' || sqrt(2) || ' ' || sqrt(2) || ')'), 'Dorian')
;

SELECT ST_AsText(ST_Union(road)) FROM example14;
SELECT ST_AsText(ST_LineMerge(ST_Union(road))) FROM example14;
SELECT ST_AsText(ST_Simplify(ST_LineMerge(ST_Union(road)),6005)) FROM example14;


-- Parallel Road Snap

DROP TABLE IF EXISTS example20;

CREATE TABLE example20 (id serial, name char(1), road geometry(linestring), attribution text);
INSERT INTO example20 (name, road, attribution)
VALUES 
    ('S', ST_GeomFromText('LINESTRING(-2 -2, 2 2)'), 'Reference'),
    ('S', ST_GeomFromText('LINESTRING(-2 -2.25, 2 1.75)'),'Emma')
;

DROP TABLE IF EXISTS example21;

CREATE TABLE example21 (id serial, name char(1), road geometry(linestring), attribution text);
INSERT INTO example21 (name, road, attribution)
VALUES 
    ('S', ST_GeomFromText('LINESTRING(-2 -2, 2 2)'), 'Reference'),
    ('S', ST_GeomFromText('LINESTRING(1.9 1.8, 2.1 2.2)'),'Emma')
;

WITH 
    R As (SELECT road FROM example21 WHERE attribution = 'Reference'),
    E As (SELECT road FROM example21 WHERE attribution = 'Emma')
SELECT ST_AsText(ST_Snap(E.road, R.road, 15)) FROM R CROSS JOIN E;

-- Misplaced Intersection Snap

DROP TABLE IF EXISTS example30;

CREATE TABLE example30 (id serial, name char(1), road geometry(linestring));
INSERT INTO example30 (name, road)
VALUES 
    ('E',ST_GeomFromText('LINESTRING(.25 .25, 2 0)')),
    ('N',ST_GeomFromText('LINESTRING(.25 .25, 0 2)')),
    ('S',ST_GeomFromText('LINESTRING(.25 .25, 0 -2)')),
    ('W',ST_GeomFromText('LINESTRING(.25 .25, -2 0)'))
;

DROP TABLE IF EXISTS example31;

CREATE TABLE example31 (id serial, name char(1), road geometry(linestring));
INSERT INTO example31 (name, road)
SELECT name, ST_Snap(road, ST_GeomFromText('POINT (0 0)'),1) FROM example30

-- Incomplete Segment Snap

DROP TABLE IF EXISTS example40;

CREATE TABLE example40 (id serial, name varchar, road geometry(linestring));
INSERT INTO example40 (name, road)
VALUES 
    ('Main',ST_GeomFromText('LINESTRING(-2 -2, 2 2)')),
    ('Scion',ST_GeomFromText('LINESTRING(0 1, 0 2)'))
;

WITH 
    M As (SELECT road FROM example40 WHERE name = 'Main'),
    S As (SELECT road FROM example40 WHERE name = 'Scion')
SELECT ST_ShortestLine(M.road, S.road) FROM M CROSS JOIN S;

WITH 
    M As (SELECT road FROM example40 WHERE name = 'Main'),
    S As (SELECT road FROM example40 WHERE name = 'Scion')
SELECT ST_Snap(S.road, ST_ClosestPoint(M.road,S.road), .1) FROM M CROSS JOIN S;

--
TRUNCATE TABLE planet_osm_line;
INSERT INTO planet_osm_line SELECT * FROM planet_osm_line_original;

UPDATE planet_osm_line SET way = ST_GeomFromText('LINESTRING(-8229152.78 4992858.26,-8229140.06 4992864.35,-8228926.84 4992963.73,-8228782.92 4993033.39,-8228772.38 4993038.49)',900913) WHERE name = 'Cooper Street';

UPDATE planet_osm_line SET way = ST_GeomFromText('LINESTRING(-8229633.65 4992626.18, -8229510.724774585 4992773.701388271 ,-8229449.87 4992800.46, -8229395.65 4992857.14, -8229382.13 4992867.28, -8229186.26 4992960.77, -8229118.91 4992992.12, -8228975.53 4993060.43)', 900913) WHERE name = 'Seaman Avenue' AND highway = 'tertiary';
UPDATE planet_osm_line SET way = ST_GeomFromText('LINESTRING(-8229390.42 4992600.42, -8229510.724774585 4992773.701388271)',900913) WHERE name = 'Cumming Street';

---

INSERT INTO planet_osm_line (osm_id, highway, name, oneway, z_order, tags, way)
SELECT -osm_id, highway, name, oneway, z_order, tags, ST_Snap(way, ST_GeomFromText('POINT(-8229497.96 4992753.36)',900913),50) As way FROM planet_osm_line WHERE name = 'Cumming Street';

INSERT INTO planet_osm_line (osm_id, highway, name, oneway, z_order, tags, way)
SELECT -osm_id, highway, name, oneway, z_order, tags, ST_Snap(way, ST_GeomFromText('POINT(-8229497.96 4992753.36)',900913),50) As way FROM planet_osm_line WHERE name = 'Seaman Avenue' AND highway = 'tertiary';

DELETE FROM planet_osm_line WHERE name = 'Cumming Street' AND osm_id > 0;
DELETE FROM planet_osm_line WHERE name = 'Seaman Avenue' AND highway = 'tertiary' AND osm_id > 0;

WITH 
    C As (SELECT osm_id, highway, name, oneway, z_order, tags, way FROM planet_osm_line WHERE name = 'Cooper Street'),
    A As (SELECT way FROM planet_osm_line WHERE name = 'Academy Street')
INSERT INTO planet_osm_line (osm_id, highway, name, oneway, z_order, tags, way)
SELECT -osm_id, highway, 'Coooper Street Extension', oneway, z_order, tags, ST_ShortestLine(A.way, C.way) As way FROM A CROSS JOIN C;


WITH 
    H As (SELECT osm_id, highway, name, oneway, z_order, tags, way FROM planet_osm_line WHERE osm_id = 33145995),
    X As (SELECT ST_SetSRID(ST_Simplify(way,5),900913) As way FROM planet_osm_line WHERE osm_id = 33145772)
    
INSERT INTO planet_osm_line (osm_id, highway, name, oneway, z_order, tags, way)    
SELECT -osm_id, highway, 'SNAPPED', oneway, z_order, tags, ST_Snap(H.way, X.way, 15) FROM H CROSS JOIN X;

DELETE FROM planet_osm_line WHERE osm_id IN (33145995,33145772);

---
INSERT INTO planet_osm_line (osm_id, highway, name, oneway, z_order, tags, way)
SELECT -1, highway, name, oneway, z_order, tags, L.way 
FROM 
    (SELECT ST_SetSRID(ST_Simplify(ST_LineMerge(ST_AsText(ST_Union(way))),500), 900913) As way FROM planet_osm_line WHERE name = 'Payson Avenue') L
    CROSS JOIN 
    (SELECT * FROM planet_osm_line WHERE osm_id = 5672284) R;
---


/*
WITH 
S AS (SELECT way FROM planet_osm_line_original WHERE name = 'Seaman Avenue' AND highway = 'tertiary'),
C AS (SELECT way FROM planet_osm_line_original WHERE name = 'Cumming Street')
SELECT ST_AsTExt(ST_INTERSECTION(S.way,C.way))
FROM S CROSS JOIN C
*/