Despite CartoDB appearing to run PostGIS 2.2 (see below), it still doesn't support multi-point geometries in ST_Split.
SELECT postgis_full_version();
POSTGIS="2.2.0" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012"
GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.7.8"
LIBJSON="UNKNOWN" (core procs from "2.2.0" need upgrade)
RASTER (raster procs from "2.2.0" need upgrade)
So I tried to use the workaround function from this answer. However, though creating that function works on my local machine, I get the following error when I create the function in cartoDB:
syntax error at or near "result"``
Here's the function I'm creating:
DROP FUNCTION IF EXISTS split_line_multipoint(input_geom geometry, blade geometry);
CREATE FUNCTION split_line_multipoint(input_geom geometry, blade geometry)
RETURNS geometry AS
$BODY$
-- this function is a wrapper around the function ST_Split
-- to allow splitting multilines with multipoints
--
DECLARE
result geometry;
simple_blade geometry;
blade_geometry_type text := GeometryType(blade);
geom_geometry_type text := GeometryType(input_geom);
BEGIN
IF blade_geometry_type NOT ILIKE 'MULTI%' THEN
RETURN ST_Split(input_geom, blade);
ELSIF blade_geometry_type NOT ILIKE '%POINT' THEN
RAISE NOTICE 'Need a Point/MultiPoint blade';
RETURN NULL;
END IF;
IF geom_geometry_type NOT ILIKE '%LINESTRING' THEN
RAISE NOTICE 'Need a LineString/MultiLineString input_geom';
RETURN NULL;
END IF;
result := input_geom;
-- Loop on all the points in the blade
FOR simple_blade IN SELECT (ST_Dump(ST_CollectionExtract(blade, 1))).geom
LOOP
-- keep splitting the previous result
result := ST_CollectionExtract(ST_Split(result, simple_blade), 2);
END LOOP;
RETURN result;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
SELECT PostGIS_full_version()– Andy Eschbacher Mar 29 '16 at 19:38I added the function I was trying to create to the question
– raphael Mar 29 '16 at 19:45