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