2

If the geometry input (a line string in my case) crosses over itself, ST_Split is splitting on the crossover as well as the blade. Is that the expected case / is it possible to only split on the blade?

PostGIS: 3.3.2 Postgres: 15.1

ST_Split does not support M, but i needed a time dimension so put it in Z (yes this is very bad and has unintended consequences) it would be handy if split passed through M values and had an option not to cut itself.

I can get the desired outcome with some aggregates.

with fragments as ( 
    select  st_dump(st_split(st_makeline(point order by ts), (select st_union(wkb_geometry) from checkpoints))) frag
    from track 
    where activity_id = 101)
, part as (
select 
    (frag).geom line,
    c_start.name start_name,
    c_end.name end_name, 
    sum(case when c_start.name is null then 0 else 1 end) over (order by (frag).path[1]) as segment_partition
from fragments
left outer join checkpoints c_start on ST_DWithin(st_startpoint((frag).geom)::geography, c_start.wkb_geometry::geography, 0.1)
left outer join checkpoints c_end on ST_DWithin(st_endpoint((frag).geom)::geography, c_end.wkb_geometry::geography, 0.1)
)
, segments as (
select 
    st_makeline(line) segment,
    to_timestamp(st_z(st_startpoint(st_makeline(line)))) start_ts,
    to_timestamp(st_z(st_endpoint(st_makeline(line)))) end_ts,
    (array_agg(distinct start_name) filter (where start_name is not null))[1] start_segment,
    (array_agg(distinct end_name) filter (where end_name is not null))[1] end_segment,
    segment_partition
from part
group by segment_partition
)
select
    segment,
    st_length(ST_Force2D(segment)::geography) length_m,
    start_ts,
    end_ts,
    end_ts - start_ts duration,
    start_segment,
    end_segment,
    segment_partition segment_order
from segments;

QGIS output of ST_Split showing the blades as red lines

Simple WKT version:

enter image description here

Red is the blade, st_split returns 4 parts.

select st_dump(st_split(ST_GeomFromText('LINESTRING(13 10,12 11,11 12,10 13,9 14,10 15,11 15,11 16,12 16,13 14,12 13,9 5)'),
    ST_GeomFromText('LINESTRING(8 12, 11 6)')));

I should mention I have solved the problem in a somewhat more logical way then posted above without putting time into Z. Now it is just really a question of is that an expected output for st_split()?

8
  • Is your input linestring valid?
    – Ian Turton
    Commented Nov 27, 2022 at 18:51
  • Could you add simple examples of a blade and a linestring as WKT?
    – user30184
    Commented Nov 28, 2022 at 7:49
  • @user30184 example added
    – Steve
    Commented Nov 28, 2022 at 10:25
  • @IanTurton as far as I can tell, I also see the same outcome with a simple WKT example, added above.
    – Steve
    Commented Nov 28, 2022 at 10:27
  • did you run st_isValid on them to check?
    – Ian Turton
    Commented Nov 28, 2022 at 11:55

1 Answer 1

0

st_split() requires a Valid and Simple Geometry.

Using a GPS trail (or any linestring) that crosses over itself does not meet the requirements.

https://postgis.net/docs/using_postgis_dbmanagement.html#OGC_Validity

https://postgis.net/docs/manual-3.2/postgis_usage.html#OGC_Validity

4.1.5. Geometry Validation

PostGIS is compliant with the Open Geospatial Consortium’s (OGC) OpenGIS Specifications. As such, many PostGIS methods require, or more accurately, assume that geometries that are operated on are both simple and valid.

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.