5

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?

3

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:

Result

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.