Skip to content

ST_Line_Substring

Jump to bottom Edit New page
mj10777 edited this page Oct 26, 2018 · 42 revisions

create a LINESTRING from a portion of another LINESTRING

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

  • Task:

    • Create a new LINESTRING from a portion of another LINESTRING 64
    • Replace the original LINESTRING 64 with what is left of the LINESTRING
      • Scenario:
        • we have a LINESTRING that was a valid border until 1920-09-30
        • after 1920-10-01 this was split into 2 sub-districts
          • the border itself did NOT change
        • we what to use these LINESTRING for both periods
          • the yellow LINESTRING is the LINESTRING we want to split (LINESTRING 64)
          • the red lines are the 1938 borders
            • among them a copy of the new 1920 border (the vertical red line, crossing the yellow line)
              • that we will set further south later
              • this is explained in 'ST_SetPoint', if that is what you are looking for
      • before ST_Line_Substring

It is always a good idea to view the result before an Update is done!

  • if something is incorrect, the UPDATED Geometry could be destroyed !
  • this SELECT command will show you the following:
    • id_segment the primary ID
    • name the main text field
    • ewkt_point the Extended Well Known Text of the given point [to compare if the correct number was given]
    • soldner_segment the original Geometry
    • first_part the first part of the Geometry [from first POINT to POINT 14 of the LINESTRING 64]
    • second_part the second part of the Geometry [from POINT 14 to the end of the LINESTRING 64]
SELECT
id_segment,
name,
AsEWKT(ST_PointN(soldner_segment,14)) AS ewkt_point,
soldner_segment,
SanitizeGeometry
(
 ST_Line_Substring
 (
  -- Parm 1: the LINESTRING to use
  (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment = 64),
  -- 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 64
  0.0,
  -- Parm 3: the end position of the LINESTRING to use as a cutting POINT (0.0 to 1.0)
  --- ending at position of POINT 14 of LINESTRING 64
  ST_Line_Locate_Point
  (
   -- Parm 1: the line to use
   (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment = 64),
   -- Parm 2: the POINT to search for [will return a value between 0.0 and 1.0]
   -- if the LINESTRING has 10 POINTS, the first woult be '1' the last '10' - 1-based
   (SELECT ST_PointN(soldner_segment,14)  FROM berlin_ortsteil_segments WHERE id_segment = 64)
  )
 )
) AS first_part,
SanitizeGeometry
(
 ST_Line_Substring
 (
  -- Parm 1: the LINESTRING to use
  (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment = 64),
  -- Parm 2: the start position of the LINESTRING to use as a cutting POINT (0.0 to 1.0)
  --- starting at position of POINT 14 of LINESTRING 64
  ST_Line_Locate_Point
  (
   -- Parm 1: the line to use
   (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment = 64),
   -- Parm 2: the POINT to search for [will return a value between 0.0 and 1.0]
   (SELECT ST_PointN(soldner_segment,14)  FROM berlin_ortsteil_segments WHERE id_segment = 64)
  ),
  -- Parm 3: the end position of the LINESTRING to use as a cutting POINT (0.0 to 1.0)
  --- end at position of the last POINT of LINESTRING 64
  1.0
 )
) AS second_part
FROM berlin_ortsteil_segments WHERE id_segment = 64;

We will create the new LINESTRING (Gesundbrunnen) from a POINT in the middle of the LINESTRING to the end

INSERT INTO berlin_ortsteil_segments
(
 soldner_segment,
 name,
 comment,
 belongs_to_01,
 belongs_to_02,
 valid_since,
 valid_until
)
VALUES
(
 SanitizeGeometry
 (
  ST_Line_Substring
  (
   -- Parm 1: the LINESTRING to use
   (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment = 64),
   -- Parm 2: the start position of the LINESTRING to use as a cutting POINT (0.0 to 1.0)
   --- starting at position of POINT 14 of LINESTRING 64
   ST_Line_Locate_Point
   (
    -- Parm 1: the line to use
    (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment = 64),
    -- Parm 2: the POINT to search for [will return a value between 0.0 and 1.0]
    (SELECT ST_PointN(soldner_segment,14)  FROM berlin_ortsteil_segments WHERE id_segment = 64)
   ),
   -- Parm 3: the end position of the LINESTRING to use as a cutting POINT (0.0 to 1.0)
   --- end at position of the last POINT of LINESTRING 64
   1.0
  )
 ),
 'Wedding-Gesundbrunnen (Gesundbrunnen),Reinickendorf 1920',
 'until 1920-09-30 common border, split 1920-10-01 (eastern portion)',
 '1902010118,1911000106',
 '1902042001,1911001201',
 '1861-01-01',
 '1938-03-31'
);

After checking that the result is correct

  • (I often copy the database to a directory called save, just in case ...)
  • we will now replace the original LINESTRING 64 with the western portion (Wedding)
    • updating the database entries
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 = 64),
   -- 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 64
   0.0,
   -- Parm 3: the end position of the LINESTRING to use as a cutting POINT (0.0 to 1.0)
   --- ending at position of POINT 14 of LINESTRING 64
   ST_Line_Locate_Point
   (
    -- Parm 1: the line to use
    (SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment = 64),
    -- Parm 2: the POINT to search for [will return a value between 0.0 and 1.0]
    (SELECT ST_PointN(soldner_segment,14)  FROM berlin_ortsteil_segments WHERE id_segment = 64)
   )
  )
 ),
 name='Wedding-Gesundbrunnen (Wedding),Reinickendorf 1920',
 comment='until 1920-09-30 common border, split 1920-10-01 (western portion)',
 belongs_to_01='1902010118,1911000105',
 belongs_to_02='1902042001,1911001201',
 valid_since='1861-01-01',
 valid_until='1938-03-31'
WHERE id_segment = 64;

The original LINESTRING 64 had 102 POINTs,

  • the new 2 LINESTRING's (64 and 693) together have 103 POINTs
    • which is correct since they share a common Start/EndPoint
      • 22448.74576858792 25701.44142146081
SRID=3068;LINESTRING(19883.3007941815 25932.71571400824,
20054.23402127774 26075.08469200435,
20215.16779877482 26219.0248223752,
20439.76713327972 26413.71395870687,
20911.22035471018 26257.96626046815,
21265.8539049339 26133.51689337113,
21358.03517816995 25813.17662387725,
21895.18162188618 25971.119835466,
21989.91106391937 25952.29379803792,
22087.93890940786 25924.87871024689,
22204.08498124415 25906.73296891506,
22314.80986291374 25928.13582746716,
22344.99052167815 25667.15500592143,
22448.74576858792 25701.44142146081)
SRID=3068;LINESTRING(22448.74576858792 25701.44142146081,
22477.68758177238 25711.00535537528,
22927.48134835188 25690.77376961597,
...
24508.89654540874 25670.38653705389)

With ST_LineMerge we can check if everything is correct:

SELECT ST_LineMerge(ST_Collect(soldner_segment)) FROM berlin_ortsteil_segments WHERE id_segment IN (64,693);
  • does the Geometry look correct?
  • did the function return a LINESTRING?
    • returning a MULTILINESTRING is an error
      • cause: the LINESTRINGs have no common Start/EndPoint
  • did it return the correct amount of POINTs as the original (102)?

In this case it did, so we are happy.

  • now is the time to set the new 1920 border further south to meet, the just completed, LINESTRINGs

2014-04-02: Mark Johnson, Berlin Germany

Add a custom footer
Add a custom sidebar
Clone this wiki locally