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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
>
>
>
> 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
> > [email protected]
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list
> [email protected]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
