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');

--
-- saving all street/polygon intersection points into a temporary table
--
CREATE TEMPORARY TABLE berlin_intersections AS
SELECT ST_Collect(ST_Intersection(s.soldner_segment, LinesFromRings(p.soldner_polygon))) AS intersections
FROM berlin_street_segments AS s, berlin_polygons AS p;

--
-- inserting intersection pointo into street segments
-----
UPDATE berlin_street_segments SET soldner_segment = ST_Snap(soldner_segment,
  (SELECT intersections FROM berlin_intersections), 0.000001);

--
-- inserting intersection pointo into mauer polygon
-----
UPDATE berlin_polygons SET soldner_polygon = CastToMultiPolygon(ST_Snap(soldner_polygon,
  (SELECT intersections FROM berlin_intersections), 0.000001));

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 )
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
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;