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

[Rhys Stewart]

Didnt realize that. thanks for pointing it out.



On 9/24/07, Kevin Neufeld <kneufeld at refractions.net> wrote:
>
>  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: kneufeld at refractions.net
>
>
>  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 <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
>
>
>  _______________________________________________
> 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
>
>