ST_LineSubstring
上一页  7.19. 线性参考  下一页

名称

ST_LineSubstring — 返回两个小数位置之间的直线部分。

大纲

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

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

描述

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.

如果 startfraction 和 endfraction 具有相同的值，则相当于ST_LineInterpolatePoint。

[注意]

这仅适用于 LINESTRING。 要在连续的 MULTILINESTRING 上使用，首先使用 ST_LineMerge将它们连接起来。
[注意]

从 1.1.1 版开始，此函数对 M 和 Z 值进行插值。 之前的版本将 Z 和 M 设置为未指定的值。

增强：3.4.0 - 引入了对地理的支持。

更改：2.1.0。 在 2.0.x 之前，这被称为 ST_Line_Substring。

可用性：1.1.0，1.1.1中添加支持Z和M

该函数支持 3d 并且不会丢失 z-index。

示例

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

如果起始位置和结束位置相同，则结果是一个 POINT。

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

将 LineString 切成长度为 100 或更短的部分的查询。 它使用generate_series() 和CROSS JOIN LATERAL 来生成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;
栅格输出
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;
栅格输出
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

地理实现沿球体表面进行测量，而几何实现沿线进行测量

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;
栅格输出
-[ 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;
栅格输出
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