ST_LineSubstring
Précédent  7.19. Référencement linéaire  Suivant

Nom

ST_LineSubstring — Renvoie la partie d'une ligne située entre deux emplacements fractionnaires.

Synopsis

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

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

Description

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.

Si startfraction et endfraction ont la même valeur, cela équivaut à ST_LineInterpolatePoint.

[Note]

Cette méthode ne fonctionne qu'avec les LINESTRING. Pour l'utiliser sur des MULTILINESTRINGs contigues, il faut d'abord les joindre avec ST_LineMerge.
[Note]

Depuis la version 1.1.1, cette fonction interpole les valeurs de M et Z. Les versions précédentes fixent Z et M à des valeurs non spécifiées.

Amélioration : 3.4.0 - La prise en charge de la géographie a été introduite.

Modifié : 2.1.0. Jusqu'à la version 2.0.x, cette fonction était appelée ST_Line_Substring.

Disponibilité : 1.1.0, prise en charge des Z et M ajoutée dans la 1.1.1

Cette fonction prend en charge la 3D et ne supprime pas l'indice z.

Exemples

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);
Export de raster
LINESTRING (45.17311810399485 45.74337011202746, 50 20, 90 80, 112.97593050157862 49.36542599789519)
Figure
Geometry figure for visual-st-linesubstring-01

Si les points de départ et d'arrivée sont identiques, le résultat est un POINT.

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

Une requête pour découper une LineString en sections de longueur 100 ou moins. Elle utilise generate_series() avec un CROSS JOIN LATERAL pour produire l'équivalent d'une boucle FOR.

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;
Export de raster
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;
Export de raster
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

Mesures de mise en œuvre de la geography le long d'un sphéroïde, geometry le long d'une ligne

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;
Export de raster
-[ 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;
Export de raster
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