1

I need to split several multilinestring into a defined number of segments using PosgreSQL.

I tried using ST_subdvideand ST_segmentize but they didn't work.

Can someone help me to solve this problem?

I tried using the following SQL string to split the first line in segments of 2 m:

with x as(

select * from city.street t
where id_street = 1)

select 
id_street,
st_astext(geom),
st_astext(st_segmentize (x.geom, 2))from x;
1

I could propose a function in postgreSQL as follows.

CREATE OR REPLACE FUNCTION public.__bvv_segmentize_by(
    tabname text,
    geomname text,
    condition text,
    partnumber integer)
    RETURNS table(id integer, len double precision, segment geometry(linestring, 4326))
    LANGUAGE 'plpgsql'

AS $BODY$
DECLARE
    expr text;
    r record;
    pntnum integer;
    i integer;
    pnt_1 geometry;
    pnt_2 geometry;
    line_geom geometry;
    len double precision;
    delta double precision;
    accum_len double precision;
    threshold double precision;
BEGIN
    expr = 'select ST_LineMerge(%s) as geom from %s where %s limit 1';
    expr = format(expr, geomname, tabname, condition);
    raise notice 'expr=%', expr;
    threshold = 0.001;

    for r in execute expr
    loop
        line_geom = r.geom;
        len = ST_Length(line_geom);
        delta = 1.0 / partnumber;
        accum_len = 0;
        i = 0;
        loop
            pnt_1 = ST_LineInterpolatePoint(line_geom, accum_len);
            accum_len = accum_len + delta;
            if accum_len > 1.0 and (accum_len - 1.0) < threshold 
            then 
                accum_len = 1.0;
            end if;
            exit when accum_len > 1.0;
            pnt_2 = ST_LineInterpolatePoint(line_geom, accum_len);
            i = i + 1;
            return query select i, ST_Length(ST_MakeLine(pnt_1, pnt_2)),ST_MakeLine(pnt_1, pnt_2);
        end loop;
    end loop;
END

$BODY$;

If you create a table with a MultiLineString geometry field like

CREATE TABLE public.test_1
(
    id bigint NOT NULL,
    geom geometry(MultiLineString,3857),
    CONSTRAINT test_1_pkey PRIMARY KEY (id)
)

and draw a MultiLineString

then after performing the following query (the last parameter 6 is a number of segments)

select id, len, segment from __bvv_segmentize_by('test_1', 'geom', 'id=1', 6)

you will get the following table The result table

and the following map view where you will find your MultiLineString split into 6 parts

Split line

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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