552901 (2) [Avatar] Offline
#1
sample.zip
[ 6 KB ]
Hi,

On page 277, 2nd edition, an example of a function that split linestring by a point is provided.

I was wondering how do i perform the same function with a table of points.

I have attached a sample point and linestring shapefiles.

Your help please.

"""
CREATE OR REPLACE FUNCTION ch11.upgis_cutlineatpoints(
param_mlgeom geometry,
param_mpgeom geometry,
param_tol double precision
)
RETURNS geometry AS
$$
DECLARE
var_resultgeom geometry;
var_sline geometry;
var_eline geometry;
var_perc_line double precision;
var_refgeom geometry;
var_pset geometry[] := -- <co id="co_code_upgis_cutlineatpoints_1" /> --
ARRAY(SELECT geom FROM ST_Dump(param_mpgeom));
var_lset geometry[] :=
ARRAY(SELECT geom FROM ST_Dump(param_mlgeom));
BEGIN

FOR i in 1 .. array_upper(var_pset,1) LOOP -- <co id="co_code_upgis_cutlineatpoints_2" /> --
FOR j in 1 .. array_upper(var_lset,1) LOOP -- <co id="co_code_upgis_cutlineatpoints_3" /> --
IF
ST_DWithin(var_lset[j],var_pset[i],param_tol) AND -- <co id="co_code_upgis_cutlineatpoints_4" />
NOT ST_Intersects(ST_Boundary(var_lset[j]),var_pset[i])
THEN -- <co id="co_code_upgis_cutlineatpoints_5" />
IF ST_NumGeometries(ST_Multi(var_lset[j])) = 1 THEN
var_perc_line :=
ST_Line_Locate_Point(var_lset[j],var_pset[i]);
IF var_perc_line BETWEEN 0.0001 and 0.9999 THEN
var_sline :=
ST_Line_Substring(var_lset[j],0,var_perc_line);
var_eline :=
ST_Line_Substring(var_lset[j],var_perc_line,1);
var_eline :=
ST_SetPoint(var_eline,0,ST_EndPoint(var_sline));
var_lset[j] := ST_Collect(var_sline,var_eline);
END IF;
ELSE
var_lset[j] := -- <co id="co_code_upgis_cutlineatpoints_6" />
upgis_cutlineatpoints(var_lset[j],var_pset[i]);
END IF;
END IF;
END LOOP;
END LOOP;

RETURN ST_Union(var_lset);

END;
$$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;
-- <end id="code_upgis_cutlineatpoints"/> --

-- <start id="code_cutlinatpoints_use"/> --
SELECT
gid, geom AS orig_geom,
(ST_Dump(
ch11.upgis_cutlineatpoints(geom, foo.the_pt, 100 )
)
).geom AS changed
FROM
ch11.stclines_streets AS s
CROSS JOIN
(SELECT ST_SetSRID(ST_Point(6011200,2113500),2227) AS the_pt) AS x
WHERE ST_DWithin(s.geom,x.the_pt,100);
"""