3

I have a dataset of LineStrings in a postgis enabled database and would like to automatically split any feature longer than a certain threshold (say 1 mile). I am aware of the Split Feature tool in QGIS but since my dataset contains potentially millions of lines, I need an automatic solution.

Is there a pre-existing function or something that can help me with this? I could write a Python script to go through each feature, and if its length > threshold then split at location X. But I am not sure how to actually split the line and maintain all other attributes

I can use ArcGIS if necessary but would strongly prefer to use open source tools

5

You can use a combination of generate_series and window LEAD function to generate dist_from and dist_to fields and use ST_LineSubstring to cut lines at some interval (here: 1000 m).

SELECT
  row_number() OVER() new_id,
  b.id,
  ST_LineSubstring(lin.the_geom, b.dist_from/ST_Length(lin.the_geom), LEAST(b.dist_to/ST_Length(lin.the_geom), 1)) geom
FROM
(
  SELECT
    id,
    dist dist_from,
    LEAD(dist) OVER (PARTITION BY id ORDER BY dist) dist_to
  FROM
  (
    SELECT
      id,
      generate_series(0, ceil(ST_Length(lin.the_geom)/1000.0)::int*1000, 1000) dist
    FROM
      lin
  ) a
) b
JOIN
  lin ON b.id = lin.id
WHERE
  b.dist_to IS NOT NULL

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.