ST_LineSubstring
Zurück  7.19. Kilometrierung  Weiter

Name

ST_LineSubstring — Gibt den Teil einer Linie zwischen zwei gebrochenen Stellen zurück.

Übersicht

geometry ST_LineSubstring(geometry a_linestring, float8 startfraction, float8 endfraction);

geography ST_LineSubstring(geography a_linestring, float8 startfraction, float8 endfraction);

Beschreibung

Computes the line which is the section of the input line starting and ending at the given fractional locations. The first argument must be a LINESTRING. The second and third arguments are values in the range [0, 1] representing the start and end locations. For geometry inputs, the fractions are measured in 2D line length. The Z and M values are interpolated for added endpoints if present.

Gleichbedeutend mit ST_LineInterpolatePoint, wenn Anfangswert und Endwert ident sind.

[Anmerkung]

Dies funktioniert nur bei LINESTRINGs. Zur Verwendung bei zusammenhängenden MULTILINESTRINGs müssen diese zunächst mit ST_LineMerge verbunden werden.
[Anmerkung]

Ab Version 1.1.1 interpoliert diese Funktion auch M- und Z-Werte (falls vorhanden), während frühere Versionen unbestimmte Werte setzten.

Verbessert: 3.4.0 - Unterstützung für Geographie wurde eingeführt.

Änderung: 2.1.0. Bis zu 2.0.x wurde diese Funktion mit ST_Line_Substring bezeichnet.

Verfügbarkeit: 1.1.0, mit 1.1.1 wurde die Unterstützung für Z und M hinzugefügt

Diese Funktion unterstützt 3d und lässt den Z-Index nicht fallen.

Beispiele

Extract the middle third of a LineString, from 0.333 to 0.666.

Code
SELECT ST_LineSubstring(
'LINESTRING (20 180,50 20,90 80,120 40,180 150)',
0.333,
0.666);
Ausgabe von Rastern
LINESTRING (45.17311810399485 45.74337011202746, 50 20, 90 80, 112.97593050157862 49.36542599789519)
Figure
Geometry figure for visual-st-linesubstring-01

Wenn Anfangs- und Endpunkt gleich sind, ist das Ergebnis ein PUNKT.

Code
SELECT ST_LineSubstring(
'LINESTRING(25 50,100 125,150 190)',
0.333,
0.333);
Ausgabe von Rastern
POINT(69.28469348539744 94.28469348539744)
Figure
Geometry figure for visual-st-linesubstring-02

Eine Abfrage, um einen LineString in Abschnitte der Länge 100 oder kürzer zu schneiden. Sie verwendet generate_series() mit einem CROSS JOIN LATERAL, um das Äquivalent einer FOR-Schleife zu erzeugen.

The final generated step is filtered out when the line length is an exact multiple of the requested section length.

Code
WITH data(id, geom) AS (VALUES
        ( 'A', 'LINESTRING( 0 0, 200 0)'::geometry ),
        ( 'B', 'LINESTRING( 0 100, 350 100)'::geometry ),
        ( 'C', 'LINESTRING( 0 200, 50 200)'::geometry )
    )
SELECT id, i,
       ST_LineSubstring(geom, startfrac, LEAST( endfrac, 1 )) AS geom
FROM (
    SELECT id, geom, ST_Length(geom) len, 100 sublen FROM data
    ) AS d
CROSS JOIN LATERAL (
    SELECT i, (sublen * i) / len AS startfrac,
              (sublen * (i+1)) / len AS endfrac
    FROM generate_series(0, floor( len / sublen )::integer ) AS t(i)
    WHERE (sublen * i) / len <
> 1.0
    ) AS d2;
Ausgabe von Rastern
id | i |            geom
----+---+-----------------------------
 A  | 0 | LINESTRING(0 0,100 0)
 A  | 1 | LINESTRING(100 0,200 0)
 B  | 0 | LINESTRING(0 100,100 100)
 B  | 1 | LINESTRING(100 100,200 100)
 B  | 2 | LINESTRING(200 100,300 100)
 B  | 3 | LINESTRING(300 100,350 100)
 C  | 0 | LINESTRING(0 200,50 200)
Figure
Geometry figure for visual-st-linesubstring-03

Split a LineString by points by locating the points along the line, adding the 0 and 1 endpoints, and extracting substrings between adjacent locations.

Code
WITH data AS (
  SELECT 'LINESTRING(0 0, 10 0)'::geometry AS line,
         'MULTIPOINT((2 0),(5 0),(8 0))'::geometry AS points
),
fractions AS (
  SELECT 0.0 AS fraction
  UNION ALL
  SELECT 1.0
  UNION ALL
  SELECT ST_LineLocatePoint(line, (dp).geom)
  FROM data
  CROSS JOIN LATERAL ST_Dump(points) AS dp
),
ordered AS (
  SELECT DISTINCT fraction
  FROM fractions
),
segments AS (
  SELECT fraction AS start_fraction,
         lead(fraction) OVER (ORDER BY fraction) AS end_fraction
  FROM ordered
)
SELECT row_number() OVER (ORDER BY start_fraction) AS segment_id,
       ST_LineSubstring(line, start_fraction, end_fraction) AS geom
FROM data
CROSS JOIN segments
WHERE end_fraction IS NOT NULL
  AND start_fraction <
> end_fraction
ORDER BY start_fraction;
Ausgabe von Rastern
segment_id |         geom
------------+----------------------
          1 | LINESTRING(0 0,2 0)
          2 | LINESTRING(2 0,5 0)
          3 | LINESTRING(5 0,8 0)
          4 | LINESTRING(8 0,10 0)
Figure
Geometry figure for visual-st-linesubstring-04

Geografische Umsetzungsmaßnahmen entlang eines Sphäroids, Geometrie entlang einer Linie

Code
SELECT ST_LineSubstring(
'LINESTRING(-118.2436 34.0522,-71.0570 42.3611)'::geography,
0.333,
0.666) AS geog_sub,
ST_LineSubstring(
    'LINESTRING(-118.2436 34.0522,-71.0570 42.3611)'::geometry,
    0.333,
    0.666) AS geom_sub;
Ausgabe von Rastern
-[ RECORD 1 ]----------
geog_sub | LINESTRING(-103.911641 38.931128,-87.941787 41.831072)
geom_sub | LINESTRING(-102.530462 36.819064,-86.817324 39.585927)
Figure
Geometry figure for visual-st-linesubstring-05

For geometry inputs, the fractional locations are based on the 2D length of the line, even when the input has Z values. The resulting Z values are interpolated along the selected 2D location.

Code
WITH data AS (
  SELECT 'LINESTRING Z (0 0 0,0 2 5,0 10 10)'::geometry AS geom
)
SELECT ST_Length(geom) AS length_2d,
       ST_3DLength(geom) AS length_3d,
       ST_LineSubstring(geom, 0, 0.5) AS substring
FROM data;
Ausgabe von Rastern
length_2d |    length_3d     |              substring
-----------+------------------+-------------------------------------
        10 | 14.819145939191106 | LINESTRING Z (0 0 0,0 2 5,0 5 6.875)
Figure
Geometry figure for visual-st-linesubstring-06