Splitting LineString into Multiple Substrings using PostGIS?

Question

I am new to PostGIS.

I have a set of (thousands) of Linestrings and associated multiple points along the line, expressed as fractions along each linestring using ST_LineLocatePoint. I am looking for a wrapper of sorts to take in a Linestring and a list of fractions, and return the geometry collection of all of the created Substrings.

Example input:

 LINESTRING=example_line, FRACTIONS=(0.25,0.835,0.9)

Example Output:

GEOMETRYCOLLECTION(
    ST_Line_Substring(example_line, 0, 0.25),
    ST_Line_Substring(example_line, 0.25, 0.835),
    ST_Line_Substring(example_line, 0.835, 0.9),
    ST_Line_Substring(example_line, 0.9, 1)
)

Does this kind of thing exist in PostGIS?

Answer 1

The following query does the following:

1. Create example lines
2. Explode string from fractions and make an array, add 0 and 1 to array and unnest
3. Create ranges [fraction_from, fraction_to] with a window function
4. Split the lines into substrings
5. Collect the substrings and group by id

Here is the query:

WITH
lines AS
(
  SELECT 1 AS id, 'LINESTRING (0 0, 1 1, 1 2, 2 2)' AS line, '0.25,0.835,0.9' AS fractions
  UNION
  SELECT 2 AS id, 'LINESTRING (0 0, 1 2, 2 2, 3 2)' AS line, '0.20,0.875,0.89' AS fractions
),
unnested AS
(
-- Create array and the unnest
SELECT
  id,
  ST_GeomFromText(line) geom,
  -- Create array from comma-separated string, add zero and one to array and unnest to create separate rows:
  unnest(array_cat(string_to_array(fractions, ',')::double precision[], ARRAY[0,1]::double precision[])) fraction
FROM
  lines
ORDER BY
  id, fraction
),
-- Create fraction ranges with the LEAD window function
ranges AS
(
SELECT
  id,
  geom,
  fraction AS fraction_from,
  lead(fraction) OVER w fraction_to
FROM 
  unnested
WINDOW w AS (PARTITION BY id ORDER BY fraction)
),
-- Create segments
segments AS
(
SELECT
  id,
  ST_Line_Substring(geom, fraction_from, fraction_to) geom,
  fraction_from,
  fraction_to
FROM
  ranges
WHERE
  fraction_to IS NOT NULL
)
-- Group segments by id
SELECT
  id,
  ST_AsText(ST_Collect(geom)) geom
FROM
  segments
GROUP BY
  id

Result: