CREATE TABLE mauer_street_segments
(
 id_segment INTEGER PRIMARY KEY,
 id_street INTEGER,
 id_street_segment INTEGER,
 name TEXT DEFAULT '',
 notes TEXT DEFAULT '',
 belongs_to TEXT DEFAULT '',
 admin_level INTEGER DEFAULT 1100,
 valid_since DATE DEFAULT '0001-01-01',
 valid_until DATE DEFAULT '3000-01-01',
 street_class DEFAULT '',
 element_number DEFAULT '',
 meters_length DOUBLE  DEFAULT 0,
 intersection_type INTEGER  DEFAULT 0,
 relate_matrix TEXT DEFAULT ''
);
SELECT AddGeometryColumn('mauer_street_segments','soldner_segment',3068,'LINESTRING','XY');
SELECT CreateSpatialIndex('mauer_street_segments','soldner_segment');
SELECT AddGeometryColumn('mauer_street_segments','soldner_segment_mauer',3068,'LINESTRING','XY');
SELECT CreateSpatialIndex('mauer_street_segments','soldner_segment_mauer');
SELECT AddGeometryColumn('mauer_street_segments','soldner_segment_diff',3068,'MULTILINESTRING','XY');
SELECT CreateSpatialIndex('mauer_street_segments','soldner_segment_diff');
SELECT AddGeometryColumn('mauer_street_segments','soldner_polygon',3068,'MULTIPOLYGON','XY');
SELECT CreateSpatialIndex('mauer_street_segments','soldner_polygon');
SELECT AddGeometryColumn('mauer_street_segments','soldner_segment_diff_pg',3068,'MULTILINESTRING','XY');
SELECT CreateSpatialIndex('mauer_street_segments','soldner_segment_diff_pg');
SELECT AddGeometryColumn('mauer_street_segments','soldner_segment_snap',3068,'LINESTRING','XY');
SELECT CreateSpatialIndex('mauer_street_segments','soldner_segment_snap');
SELECT AddGeometryColumn('mauer_street_segments','soldner_segment_diff_snap',3068,'MULTILINESTRING','XY');
SELECT CreateSpatialIndex('mauer_street_segments','soldner_segment_diff_snap');

INSERT INTO mauer_street_segments (id_segment,id_street, id_street_segment, name, notes,belongs_to, admin_level,valid_since,valid_until,street_class,element_number,relate_matrix,
       soldner_segment, soldner_segment_mauer, intersection_type, soldner_polygon )
SELECT
 id_segment,id_street, id_street_segment, a_source.name, a_source.notes,a_source.belongs_to, a_source.admin_level,a_source.valid_since,a_source.valid_until,street_class,element_number,
ST_Relate(a_source.soldner_segment, b_source.soldner_polygon),
 soldner_segment,
 ST_Intersection(b_source.soldner_polygon, a_source.soldner_segment) AS soldner_segment_mauer,
 CASE
  WHEN ST_Within(a_source.soldner_segment, b_source.soldner_polygon)
  THEN 1 -- Compleatly contained inside
   ELSE 
    CASE
     WHEN ST_Crosses(a_source.soldner_segment, b_source.soldner_polygon)
     THEN 2 -- Crosses into and out again
     ELSE
      CASE
       WHEN ST_Overlaps(a_source.soldner_segment, b_source.soldner_polygon)
      THEN 3 
       ELSE
        CASE
         WHEN ST_Touches(a_source.soldner_segment, b_source.soldner_polygon)
         THEN 4 -- Crosses into, but not out again
         ELSE
          CASE
           WHEN ST_Disjoint(a_source.soldner_segment, b_source.soldner_polygon)
           THEN 5
           ELSE
            CASE
             WHEN ST_Equals(a_source.soldner_segment, b_source.soldner_polygon)
             THEN 6
            END
          END
        END
      END
    END
 END AS intersection_type, soldner_polygon
FROM
 berlin_street_segments a_source,
 berlin_polygons b_source
WHERE
(
 ST_Intersects(a_source.soldner_segment, b_source.soldner_polygon)
);

UPDATE mauer_street_segments SET soldner_segment_diff=CastToMultiLineString(ST_Difference(CastToMultiLineString(soldner_segment),soldner_segment_mauer)) WHERE soldner_segment_mauer IS NOT NULL;

UPDATE mauer_street_segments SET soldner_segment_diff_pg=CastToMultiLineString(ST_Difference(CastToMultiLineString(soldner_segment),soldner_polygon));

UPDATE mauer_street_segments SET soldner_segment_snap=ST_Snap(soldner_segment,soldner_segment_mauer, 0.000001);

UPDATE mauer_street_segments SET soldner_segment_diff_snap=CastToMultiLineString(ST_Difference(CastToMultiLineString(soldner_segment),soldner_segment_snap));

SELECT ST_Intersects(ST_StartPoint(soldner_segment_mauer), soldner_polygon) AS StartPolygon,
       ST_Intersects(ST_EndPoint(soldner_segment_mauer), soldner_polygon) AS EndPolygon,
       ST_Intersects(ST_StartPoint(soldner_segment_mauer), soldner_segment) AS StartSegment,
       ST_Intersects(ST_EndPoint(soldner_segment_mauer), soldner_segment) AS EndSegSegment,
       ST_Intersects(ST_StartPoint(soldner_segment_mauer), soldner_segment_snap) AS StartSnap,
       ST_Intersects(ST_EndPoint(soldner_segment_mauer), soldner_segment_snap) AS EndSnap
FROM mauer_street_segments;