5

I want to split a line by point in postgis. But the result is whole line without splitting. I work with closest point on input line. This is my query:

select
    st_split(
        _input,
        ST_ClosestPoint(_input , _blade)
    )
    from (
    select st_geomfromtext('srid=3857;LINESTRING (6050668.141401841 3747562.695792065, 6050847.281693009 3748099.9265132365, 6051307.630580775 3747871.7845201474)') _input,
        st_geomfromtext('srid=3857;POINT (6050714.928364518 3747735.4091125955)') _blade
) foo

2 Answers 2

4

Except for those comparably few cases of regularity, it is impossible to create infinitesimally small position vectors on an infinitesimally thin direction vector, given the limitation of precision and rounding of numerical data types - even extrapolating a point from a line will be subject to precision rounding and thus the resulting point may not be part of the line.


I suggest to work with ST_LineSubstring instead:

WITH
  params(_input, _blade) AS (
    VALUES (
      'SRID=3857;LINESTRING(6050668.141401841 3747562.695792065, 6050847.281693009 3748099.9265132365, 6051307.630580775 3747871.7845201474)'::GEOMETRY,
      'SRID=3857;POINT(6050714.928364518 3747735.4091125955)'::GEOMETRY
    )
  )
SELECT
  ST_LineSubstring(_input, LEAST(n, frac), GREATEST(n, frac)) AS geom
FROM
  params,
  LATERAL ST_LineLocatePoint(_input, _blade) AS frac,
  GENERATE_SERIES(0.0, 1.0) AS n
;

Here we

  • find the fraction of line length (ST_LineLocatePoint) of the closest projection of the _blade on the _input line (frac)
  • produce two (corresponding to the expected split count) result set rows (GENERATE_SERIES) receiving the start and end fraction (0.0, 1.0) as values (n)
  • extract the two segments of the _input line (ST_LineSubstring) corresponding to the _blade fraction - the LEAST & GREATEST construct simply enables us to pass in both start and end fractions (n) in different function parameter locations dynamically

Note that this needs a bit more complexity if you need to deal with two tables of _input and _blade geometries, and would best be wrapped up into a custom function.

3

A simple way to deal with tolerance issues, as recommended by the docs, is to use st_snap. For your example, this would be

select st_split(
    st_snap(_input, ST_ClosestPoint(_input , _blade), 0.0001),
        ST_ClosestPoint(_input , _blade))
from (
    select st_geomfromtext('srid=3857;LINESTRING (6050668.141401841 3747562.695792065, 6050847.281693009 3748099.9265132365, 6051307.630580775 3747871.7845201474)') _input,
    st_geomfromtext('srid=3857;POINT (6050714.928364518 3747735.4091125955)') _blade) foo

This adds a vertex at the closest point on _line to _blade and splits it there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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