Be warned, your function does not remove duplicate vertices at the
split point...
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;
SELECT astext(line) FROM
(SELECT line FROM split_lines2(
'SELECT geom FROM lines',
'select geom FROM points')
) as foo;
astext
---------------------------------
LINESTRING(0 0,1 1,3 3)
LINESTRING(3 3,3 3,5 5,6 6,7 7)
(2 rows)
-------------
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:
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
|
