[postgis-users] Making links

[Rhys Stewart]

Hi there,

for your second problem the follwoing sp/func worked for me. I had a
similar requirement a while back and came up with this. Good folks
pointed out that it didnt remove duplicate vertices at the split
point, that wasnt a problem for me but it might be something to take
into consideration.

usage is as follows:
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 integer, OUT line geometry)
  RETURNS SETOF record AS
$BODY$
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;
		line := line_substring(linerec.geom, start_, end_);
		start_:= end_;
		RETURN NEXT;
	END LOOP;
 END LOOP;
 DROP TABLE line_tmp;
 DROP TABLE point_tmp;
 RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

___________________________________________________________________________


This is a variant of the previous function, in this one the points
dont have to fall directly on the linestring:

________________________________________________________________________
CREATE OR REPLACE FUNCTION dev.split_lines_2(IN lineq text, IN pointq
text, OUT lineid integer, OUT line geometry)
  RETURNS SETOF record AS
$BODY$
DECLARE
	linerec record;
	pointrec record;
	loopy int;
	start_ numeric(30,26);
	end_ numeric(30,26);
	loopqry text;
BEGIN
 EXECUTE 'CREATE TEMP TABLE line_tmp as '|| lineq;
 EXECUTE 'CREATE TEMP TABLE point_tmp as '|| pointq;
 EXECUTE 'CREATE INDEX tmp_idx1 ON line_tmp USING gist (geom)';
 EXECUTE 'CREATE INDEX tmp_idx2 ON point_tmp USING gist (geom)';
 FOR linerec in EXECUTE 'select * from line_tmp order by id' LOOP
 start_ := 0;
 loopy := 0;
 loopqry := 'SELECT *,
line_locate_point('||quote_literal(linerec.geom)||',geom) as frac from
point_tmp where distance(geom,'||quote_literal(linerec.geom)||
		') < .1 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
		RAISE NOTICE 'in loop';
		loopy := 1;
		raise notice '%',pointrec;
		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);
	if loopy = 0 THEN
		line := linerec.geom;
		lineid:= linerec.id;
	END IF;
	RETURN NEXT;
 END LOOP;
 --DROP TABLE line_tmp;
 --DROP TABLE point_tmp;
 RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
_________________________________________________________________________
On 10/24/07, Gustavo Ces <g.ces at pettra.es> wrote:
> Hi,
>
>     Points are all over the net ( the linestrings) because they were
> extracted from them. The problem is just to classify the linestring points
> in two groups ( stations and not-stations) and then split the net into
> smallest lines with stations as start and end points ( because i need the
> not-stations data too).
>     So there is two problems :
> 1- classify nodes in two groups
> 2. split linestrings in smallest links using another point table.
>
> I started with python and org, so i´m searching a solution with funcions
> diferent to ogr's ones. In other words, is there any PostGis funcions to
> make this split-action? But, algorithmic solutions will be welcomed, too :)
>
> Gus
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>