<div dir="ltr">Hey, you may want to use a function I wrote as a wrapper around ST_Split to allow splitting a (multi-)line with (multi) points.<div><br></div><div>Here it is.<br>It still thinks it should be somewhere is the core.</div>
<div><br></div><div>Cheers,</div><div><br></div><div>Rémi-C</div><div><br></div><div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">--creating a simple wrapper around ST_Split to allow <span class="">splitting</span> line by multipoints</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>DROP FUNCTION IF EXISTS public.Split_multi(input_geom geometry ,blade geometry);</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>CREATE FUNCTION public.Split_multi(input_geom geometry ,blade geometry)</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span> RETURNS geometry AS</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>$BODY$</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>--this function is a wrapper around the function ST_Split to allow <span class="">splitting</span> mutli_lines with multi_points</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>--</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span> DECLARE</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>result geometry;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>simple_blade geometry;</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>blade_geometry_type text := GeometryType(blade); geom_geometry_type text := GeometryType(input_geom);</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>blade_coded_type SMALLINT; geom_coded_type SMALLINT;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span> BEGIN</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><br></div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>--finding type of input : mixed type are not allowed</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>--if type is not multi, simply <span class="">splitting</span> and returning result</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<br></div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>IF blade_geometry_type NOT ILIKE 'MULTI%' THEN</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>--RAISE NOTICE 'input geom is simple, doing regular <span class="">split</span>';</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>RETURN ST_Split(input_geom,blade);</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>ELSIF blade_geometry_type ILIKE '%POINT' THEN</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>blade_coded_type:= 1;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>ELSIF blade_geometry_type ILIKE '%LINESTRING' THEN</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>blade_coded_type:= 2;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>ELSIF blade_geometry_type ILIKE '%POLYGON' THEN</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>blade_coded_type:= 3;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>ELSE</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>RAISE NOTICE 'mutliple input geometry types for the blade : should be homogenous ';</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>RETURN NULL;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>END IF;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<br></div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>IF geom_geometry_type ILIKE '%POINT' THEN</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>geom_coded_type:= 1;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>ELSIF geom_geometry_type ILIKE '%LINESTRING' THEN</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>geom_coded_type:= 2;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>ELSIF geom_geometry_type ILIKE '%POLYGON' THEN</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>geom_coded_type:= 3;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>ELSE</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>RAISE NOTICE 'mutliple input geometry types for the geom: should be homogenous ';</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>RETURN NULL;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>END IF;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<br></div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>result := input_geom;<span style="white-space:pre-wrap"> </span></div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>--Loop on all the geometry in the blade</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>FOR simple_blade IN SELECT (ST_Dump(ST_CollectionExtract(blade, blade_coded_type))).geom</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>LOOP</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>result:= ST_CollectionExtract(ST_Split(result,simple_blade),geom_coded_type);</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>END LOOP;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>RETURN result;</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span> END;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>$BODY$</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>LANGUAGE plpgsql IMMUTABLE;</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>----</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>--Testing the function</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>SELECT ST_AsText(Split_multi(geom, blade))</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>FROM (</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>SELECT ST_GeomFromText('Multilinestring((-3 0, 3 0),(-1 0,1 0))') AS geom,</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>ST_GeomFromText('MULTIPOINT((-0.5 0),(0.5 0))') AS blade</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>--ST_GeomFromText('POINT(-0.5 0)') AS blade</div>
<div style="font-family:arial,sans-serif;font-size:12.800000190734863px"><span style="white-space:pre-wrap"> </span>--ST_GeomFromText('MULTILINESTRING((0 1, 0 -1),(0 2,0 -2))') AS blade</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>--ST_GeomFromText('MULTIPOLYGON(((0 1,0 -1 ,1 -1,0 1)),((0 2,0 -2,1 -2,0 2)))') AS blade</div><div style="font-family:arial,sans-serif;font-size:12.800000190734863px">
<span style="white-space:pre-wrap"> </span>) AS toto</div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2013/10/28 franco base <span dir="ltr"><<a href="mailto:frenk.calza@gmail.com" target="_blank">frenk.calza@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>Hi.</div><div>I have a set of linestring and point on linestring.</div><div>I want cut the linestring on the point.</div>
<div>I can have n points for one linestring, </div><div>so I use ST_Split</div><div>
(insted of ST_Line_Interpolate_Point and ST_Line_Substring)</div><div>but it doesn't work.</div><div> </div><div>line;geom_line;point;geom_point<br>7646;"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE33534123B9FC675020374112143FF6BC335341";11764;"0101000020BB0B00005713362F611F37410D996234AF335341"<br>
7646;"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE33534123B9FC675020374112143FF6BC335341";11769;"0101000020BB0B00005EF0DD584620374139BF2762BC335341"<br>
7646;"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE33534123B9FC675020374112143FF6BC335341";11762;"0101000020BB0B0000A5CBC785841F374156A2A33CB1335341"<br>
</div><div> </div><div>ST_GeometryType(St_Split(geom_line, geom_point))</div><div>give "ST_GeometryCollection"</div><div> </div><div>but </div><div> </div><div>(ST_Dump(ST_Split(geom_line, geom_point))).geom</div>
<div> </div><div>return only 3 linestring (one for record) and the geometry is egual to geom_line </div><div> </div><div> </div><div>thanks.</div><span class="HOEnZb"><font color="#888888"><div> </div><div>fb</div></font></span></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>