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

[Rhys Stewart]

Well here is the function:
the two in variables are select queries. First one selects the line
features and the second one the point features. really simple.
select * from dev.split_lines2('select road_id as id, geom from
roadnet','select geom from points')

CREATE OR REPLACE FUNCTION dev.split_lines2(in lineq text, in pointq
text, out lineid int, out line geometry)
RETURNS SETOF RECORD AS
$$
DECLARE
	linerec record;
	pointrec record;
	linepos float;
	start_ float;
	end_ float;
	loopqry text;
BEGIN
 EXECUTE 'CREATE TEMP TABLE line_tmp as '|| lineq;
 EXECUTE 'CREATE TEMP TABLE point_tmp as '|| pointq;
 FOR linerec in EXECUTE 'select * from line_tmp order by id' LOOP
 start_ := 0;
 loopqry := 'SELECT *,
line_locate_point('||quote_literal(linerec.geom)||',geom) as frac from
point_tmp where intersects(geom,'||quote_literal(linerec.geom)||
		')ORDER BY line_locate_point('||quote_literal(linerec.geom)||',geom)';
	--FOR pointrec IN SELECT *,line_locate_point(linerec.geom, geom) as
frac from point_tmp where intersects(geom,linerec.geom) ORDER BY
line_locate_point(linerec.geom, geom) LOOP
	FOR pointrec in EXECUTE loopqry LOOP
		end_ := pointrec.frac;
		lineid := linerec.id;
		--RAISE NOTICE 'start=%,end=%',start_, end_;
		line := line_substring(linerec.geom, start_, end_);
		start_:= end_;
		RETURN NEXT;
	END LOOP;
	line:= line_substring(linerec.geom, end_,1.0);
	RETURN NEXT;
 END LOOP;
 DROP TABLE line_tmp;
 DROP TABLE point_tmp;
 RETURN;
END;
$$
LANGUAGE plpgsql;

On 9/21/07, Kevin Neufeld <kneufeld at refractions.net> wrote:
> 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
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>