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)

st_astext(st_segmentize (x.geom, 2))from x;

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'

    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;
    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
        line_geom = r.geom;
        len = ST_Length(line_geom);
        delta = 1.0 / partnumber;
        accum_len = 0;
        i = 0;
            pnt_1 = ST_LineInterpolatePoint(line_geom, accum_len);
            accum_len = accum_len + delta;
            if accum_len > 1.0 and (accum_len - 1.0) < threshold 
                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;


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 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.