<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Be warned, your function does not remove duplicate vertices at the
split point...<br>
<br>
<tt>CREATE TABLE lines AS<br>
SELECT 'LINESTRING(0 0,1 1,3 3,5 5,6 6,7 7)'::geometry AS geom;<br>
<br>
CREATE TABLE points AS<br>
SELECT 'POINT(3 3)'::geometry AS geom;<br>
<br>
SELECT astext(line) FROM <br>
(SELECT line FROM split_lines2(<br>
'SELECT geom FROM lines', <br>
'select geom FROM points')<br>
) as foo;<br>
<br>
<br>
astext <br>
---------------------------------<br>
LINESTRING(0 0,1 1,3 3)<br>
LINESTRING(3 3,3 3,5 5,6 6,7 7)<br>
(2 rows)<br>
</tt><br>
<pre class="moz-signature" cols="72">-------------
Kevin Neufeld
Software Developer
Refractions Research Inc.
300-1207 Douglas St.
Victoria, B.C., V8W 2E7
Phone: (250) 383-3022
Email: <a class="moz-txt-link-abbreviated" href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</a></pre>
<br>
<br>
Rhys Stewart wrote:
<blockquote
cite="mid:189966030709240942icb3edf4s1d590111a51d4866@mail.gmail.com"
type="cite">
<pre wrap="">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 <a class="moz-txt-link-rfc2396E" href="mailto:kneufeld@refractions.net"><kneufeld@refractions.net></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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: <a class="moz-txt-link-abbreviated" href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</a>
Rhys Stewart wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<pre wrap=""><!---->_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>