ST_CollectionExtract

--> 'List of Sql-Commands'

---

Original Documentation 'Spatialite-Functions SQL functions that implement spatial operators (GEOS advanced features)'

• ST_CollectionExtract( geom Geometry , type Integer ) : Geometry

• While using 'ST_Intersection' I received a GEOMETRYCOLLECTION

  • it had 1 POINT and LINESTRINGs
    • the red LINESTRING crosses the blue LINESTRING and merges later into the blue LINESTRING
      • Goal: is to cut the red LINESTRING where it crosses the blue LINESTRING
        • removing the upper portion that merges with the blue LINESTRING laster

So we will adapt the sql to use ST_CollectionExtract to extract from the GEOMETRYCOLLECTION the needed POINT

SELECT
ST_Intersects
(
 -- first LINESTRING
 (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (680)),
 -- second LINESTRING
 (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (106))
 -- returns FALSE (0) or TRUE (1)
) AS is_intersects,
ST_CollectionExtract
(
 ST_Intersection
 (
   -- first LINESTRING
  (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (680)),
  -- second LINESTRING
  (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (106))
  -- returns the geometry (if any)
 ),
 -- 1 = POINT, 2=LINESTRING, 3=POLYGON
 1
) AS geometry_intersection,
ST_GeometryType
(
 ST_CollectionExtract
 (
  ST_Intersection
  (
    -- first LINESTRING
   (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (680)),
   -- second LINESTRING
   (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (106))
   -- returns the geometry (if any)
  ),
  -- 1 = POINT, 2=LINESTRING, 3=POLYGON
  1
 )
) AS geometry_type;

This returned something like this:

1 BLOB sz=69 GEOMETRY MULTIPOINT

The POINT will then look something like this:

SRID=3068;MULTIPOINT(31728.54049562893 20279.87778570565)

The following sql was used to UPDATE the red LINESTRING with the portion that we needed

• Note : the received MULTIPOINT must be cast to a POINT

UPDATE berlin_ortsteil_segments SET soldner_segment =
 SanitizeGeometry
 (
  ST_Line_Substring
  (
   -- Parm 1: the LINESTRING to use
   (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (680)),
   -- Parm 2: the start position of the LINESTRING to use as a cutting POINT (0.0 to 1.0)
   ST_Line_Locate_Point
   (
    (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (680)),
    -- the single POINT in the MULTIPOINT must be cast to a POINT
    CastToPoint
    (
     ST_CollectionExtract
     (
      ST_Intersection
      (
       -- first LINESTRING
       (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (680)),
       -- second LINESTRING
       (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (106))
       -- returns the geometry (if any)
      ),
      -- 1 = POINT, 2=LINESTRING, 3=POLYGON
      1
     )
    )
   ),
   -- to the end of the LINESTRING
   1.0
  )
 )
WHERE id_segment IN (680);

The final result now looks like this:

• the lower blue LINESTRING (valid until 1938) and the red LINESTRING (valid since 1938)
  • now will use the common upper blue LINESTRING (valid until present)

---

2014-04-14: Mark Johnson, Berlin Germany

---