using ST_Line_Substring to retrieve center of LINESTRING - geopaparazzi/Spatialite-Tasks-with-Sql-Scripts GitHub Wiki

create a POINT at the center of a LINESTRING

• and, with a bit of cheating, the same with a MULTILINESTRING

--> 'List of Sql-Commands'

---

Original Documentation Spatialite-Functions SQL functions that implement spatial operators [GEOS advanced features]

• ST_Line_Substring( line Curve , start_fraction Double precision , end_fraction Double precision ) : Curve

Original Documentation Spatialite-Functions SQL functions on type Curve [Linestring or Ring]

• ST_EndPoint( c Curve ) : Point
• ST_StartPoint( c Curve ) : Point

---

Goal is to calculate the true center of a LINESTRING:

• and to make a calculated guess with a MULTILINESTRING

• the input can look like this: *

• first a true LINESTRING

  • with use of WHERE ST_NumGeometries(soldner_linestring) = 1
    • this table is in the original form supplied by the City of Berlin in 2013
      • the are line segments (abschnitte) that end/continue at each street intersection

UPDATE berlin_strassen_abschnitte SET soldner_center=
(
 SanitizeGeometry
 (
  ST_EndPoint
  ( -- return only the EndPoint of the created LINESTRING
   ST_Line_Substring
   (
    -- Parm 1: the LINESTRING to use
    ( -- the source LINESTRING to use
     SELECT soldner_segment FROM berlin_strassen_abschnitte AS work_strassen_abschnitte 
     WHERE work_strassen_abschnitte.id_strasse = berlin_strassen_abschnitte.id_strasse),
    -- Parm 2: the start position of the LINESTRING to use as a cutting POINT (0.0 to 1.0)
    --- starting at the first POINT of the LINESTRING 
    0.0,
    -- Parm 3: the end position of the LINESTRING to use as a cutting POINT (0.0 to 1.0)
    --- 50 % LINESTRING 
    0.5
   )
  )
 )
)
WHERE ST_NumGeometries(soldner_linestring) = 1;

• the result can look like this: *

---

• second with MULTILINESTRING
  • with use of WHERE ST_NumGeometries(soldner_linestring) > 1
    • this table has been created with ST_LineMerge
      • CastToMultiLinestring(ST_LineMerge(ST_Collect(berlin_strassen_abschnitte.soldner_segment)))

UPDATE berlin_streets_geometries SET soldner_center=
(
 SanitizeGeometry
 (
  ST_EndPoint
  ( -- return only the EndPoint of the created LINESTRING
   ST_Line_Substring
   (
    -- Parm 1: the MULTILINESTRING to use
    ( -- the source MULTILINESTRING to use    
     SELECT MakeLine(ST_DissolvePoints(soldner_linestring),1) 
     FROM berlin_streets_geometries AS work_streets_geometries
     WHERE work_streets_geometries.id_admin = berlin_streets_geometries.id_admin),
    -- Parm 2: the start position of the created LINESTRING to use as a cutting POINT (0.0 to 1.0)
    --- starting at the first POINT of the created LINESTRING 
    0.0,
    -- Parm 3: the end position of the created LINESTRING to use as a cutting POINT (0.0 to 1.0)
    --- 50 % LINESTRING 
    0.5
   )
  )
 )
)
WHERE ST_NumGeometries(soldner_linestring) > 1;

• the result can look like this:
  • the yellow LINESTRINGs are the 3 LINESTRINGs shown above
    • the 2 MULTILINESTRINGs meet at the Top/Right (Nollendorfplatz)
      • the other MULTILINESTRING is of Motzstraße
        • this Street was split on the 1934-02-24
        • this map is from 1931, the geometries from 2013
          • that is why Motzstraße continues after Nollendorfplatz toward the Top/Right corner on the Map
            • the geometry stops at the present position where it meets the Nollendorfplatz
    • the LINESTRING is on the Right side south of Nollendorfplatz (Nollendorfstraße)
      • in the original table they were 2 LINESTRINGs
        • with ST_Collect they could have became a MULTILINESTRING
          • if the EndPoint of the first LINESTRING is not the same as the StartPoint of the second
        • with ST_LineMerge they become 1 LINESTRING
          • since the Start/End POINTs are the same
          • if the EndPoint of the first LINESTRING is the same as the EndPoint of the second
            • then the second LINESTRING will be read from EndPoint to StartPoint
              • and thus merged and becomes 1 LINESTRING
                • only 1 of the 2 POINTS will be stored
    • the yellow Stars are the calculated 'center' POINTs of the 3 geometries
    • 

---

2014-04-30: Mark Johnson, Berlin Germany

---