[postgis-users] split a line with one or more point features

[Kevin Neufeld]

Maybe linear referencing can help you.
In psql....

CREATE TABLE lines AS
    SELECT 'LINESTRING(0 0,1 1,3 3,5 5,6 6,7 7)'::geometry AS geom;

CREATE TABLE points AS
    SELECT 'POINT(3 3)'::geometry AS geom;

-- expanded output
\x

SELECT ST_AsText(ln_geom) AS original_geom,
       ST_AsText(pt_geom) AS point_geom,
       ST_AsText(ST_SnapToGrid(ST_Line_Substring(ln_geom, 0.0, 
location), 1)) AS split1_geom,
       ST_AsText(ST_SnapToGrid(ST_Line_Substring(ln_geom, location, 
1.0), 1)) AS split2_geom
FROM
   (
    SELECT ln.geom AS ln_geom,
           pt.geom AS pt_geom,
           ST_Line_Locate_Point(ln.geom, pt.geom) AS location
    FROM lines ln, points pt
    ) AS foo;

-[ RECORD 1 ]-+------------------------------------
original_geom | LINESTRING(0 0,1 1,3 3,5 5,6 6,7 7)
point_geom    | POINT(3 3)
split1_geom   | LINESTRING(0 0,1 1,3 3)
split2_geom   | LINESTRING(3 3,5 5,6 6,7 7)

Cheers,
-- Kevin

-------------
Kevin Neufeld
Software Developer
Refractions Research Inc.
300-1207 Douglas St.
Victoria, B.C., V8W 2E7

Phone: (250) 383-3022
Email: kneufeld at refractions.net



Rhys Stewart wrote:
> Hi all,
>
> as the subject says i would like to find a way to split several
> thousand lines into segments with an intersecting (or relatively near)
> point feature. I don't see how this could be done via a straight sql
> query, however I'm by no means the smartest cookie in the jar when it
> comes to these things, hence I'm throwing it out to the list. Failing
> that I reckon my only recourse would be  to cobble up a plpgsql
> function to do same. Before I go down that path I would like to see if
> such a function exists or if someone happens to be working on
> something similar at this moment.... right... so yah that would be it.
>
> Thanks.
>
> Rhys
> Peace & Love|Live Long & Prosper
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>