CREATE OR REPLACE FUNCTION "201409"."05_bezier3_points"(float[], float)
RETURNS geometry[] LANGUAGE sql IMMUTABLE AS $BODY$

-- "201409"."03_bezier3" の返し方を点の配列に変更
-- 第一引数（配列, 8要素）起点XY, 起点の制御点XY, 終点の制御点XY, 終点XY
-- 第二引数 細かさパラメータ（最小区間長に近い数値。小さいほど精度大）

WITH a (lab, geom) AS (
	VALUES ('p1', ST_Point($1[1], $1[2]))	-- 起点XY
		, ('c1', ST_Point($1[3], $1[4]))-- 起点の制御点XY
		, ('c2', ST_Point($1[5], $1[6]))-- 終点の制御点XY
		, ('p2', ST_Point($1[7], $1[8]))-- 終点XY
), c (lab, geom) AS (
	-- 三つの直線を作成
	SELECT text 'm1', ST_MakeLine(geom) FROM a WHERE lab IN ('p1', 'c1')
	UNION ALL
	SELECT 'm2', ST_MakeLine(geom) FROM a WHERE lab IN('c1', 'c2')
	UNION ALL
	SELECT 'm3', ST_MakeLine(geom) FROM a WHERE lab IN('c2', 'p2')
), d AS (
	SELECT max(ST_Length(geom)) FROM c
), e AS (
	-- 区間の分割位置を準備
	SELECT generate_series(0, floor(max / $2) :: int) * $2 / max loc
	FROM d
	UNION SELECT 1
), f AS (
	-- 経過点を作成
	SELECT lab, loc, ST_LineInterpolatePoint(geom, loc) geom FROM c, e
), g AS (
	SELECT loc, ST_LineInterpolatePoint(ST_MakeLine(geom), loc) geom
	FROM f WHERE lab <> 'm3' GROUP BY loc
	UNION ALL
	SELECT loc, ST_LineInterpolatePoint(ST_MakeLine(geom), loc)
	FROM f WHERE lab <> 'm1' GROUP BY loc
), h AS (
	SELECT loc, ST_LineInterpolatePoint(ST_MakeLine(geom), loc) geom
	FROM g GROUP BY loc ORDER BY loc
)
SELECT array_agg(geom) FROM h ;
$BODY$ ;

-- Ex
SELECT "201409"."05_bezier3_points"(
	ARRAY[0, 25, 25, 0, 35, 85, 50, 25], 1) ;

CREATE OR REPLACE FUNCTION "201409"."05_svgpath2geom"(text, float DEFAULT 0)
RETURNS geometry LANGUAGE plpgsql IMMUTABLE AS $BODY$

-- "201409"."05_svg_pathcmd_rev" を直接ジオメトリ返すよう修正
-- 第二引数はベジェ曲線を近似する時の細かさパラメータ
-- 第二引数デフォルトなら全て直線と見なす
-- QqTt コマンドは対象外。今後必要なら追加

-- 3次ベジェ処理に呼び出す関数
-- SELECT "201409"."05_bezier3_points"(ARRAY[0, 25, 25, 0, 35, 85, 50, 25], 1) ;

DECLARE str text ;
	cmd text ;
	cmd_prev text ; -- S コマンド用
	ar1 numeric[] ;
	ar2 numeric[] ;
	ar3 numeric[] ; -- S コマンド用
	pxy numeric[] ;
	poi geometry(Point)[] ; -- ジオメトリ種別指定は無視されるが明示的に書いた
	bzp geometry(Point)[] ; -- ベジェ用
	gms geometry[] ; -- 出力用
	cnt int = 0 ; -- path 要素ごとに M コマンドを数える
BEGIN
FOR str IN SELECT (regexp_matches($1, '(\w[\d\-\.\,]*)', 'g'))[1] LOOP
	cmd = left(str, 1) ;
	ar1 = string_to_array(regexp_replace(right(str, -1)
		, '(\d)-', '\1,-', 'g'), ',') ;

	IF cmd ~* 'M' THEN
		-- 前が閉じない線の場合
		IF poi IS NOT NULL THEN gms = gms || ST_MakeLine(poi) ; END IF ;

		cnt = cnt + 1 ;
		pxy = CASE WHEN cmd = 'M' THEN ar1
			ELSE ARRAY[pxy[1] + ar1[1], pxy[2] + ar1[2]] END ;
		poi = ARRAY[ST_Point(pxy[1], pxy[2])] ;
	ELSEIF cmd ~* 'Z' THEN
		-- きちんと閉じる
		IF NOT poi[1] = poi[array_length(poi, 1)] THEN
			poi = poi || poi[1] ;
		END IF ;

		-- ここでは中マド考慮せずポリゴン化して配列に足す
		-- 中マド処理は最後の出力時に行う
		gms = gms || ST_MakePolygon(ST_MakeLine(poi)) ;

		-- 点群をクリア
		poi = NULL ;
	ELSEIF cmd ~* '[HVL]' THEN
		pxy = CASE WHEN cmd = 'H' THEN ARRAY[ar1[1], pxy[2]]
			WHEN cmd = 'h' THEN ARRAY[pxy[1] + ar1[1], pxy[2]]
			WHEN cmd = 'V' THEN ARRAY[pxy[1], ar1[1]]
			WHEN cmd = 'v' THEN ARRAY[pxy[1], pxy[2] + ar1[1]]
			WHEN cmd = 'L' THEN ar1
			ELSE ARRAY[pxy[1] + ar1[1], pxy[2] + ar1[2]] END ;
		poi = poi || ST_Point(pxy[1], pxy[2]) ;
	ELSEIF cmd ~* 'S' THEN
		IF cmd_prev ~* '[CS]' THEN
			-- 起点の制御点が直前ベジェの終点の制御点の対向
			ar3 = ARRAY[pxy[1] + (pxy[1] - ar2[5])
				, pxy[2] + (pxy[2] - ar2[6])] ;
			ar2 = pxy || ar3 || CASE WHEN cmd = 'S' THEN ar1
				ELSE ARRAY[pxy[1] + ar1[1]
					, pxy[2] + ar1[2]
					, pxy[1] + ar1[3]
					, pxy[2] + ar1[4]] END ;
		ELSE
			-- 制御点二つが同じ位置
			ar2 = pxy || CASE WHEN cmd = 'S' THEN ar1[1:2] || ar1
				ELSE ARRAY[pxy[1] + ar1[1]
					, pxy[2] + ar1[2]
					, pxy[1] + ar1[1]
					, pxy[2] + ar1[2]
					, pxy[1] + ar1[3]
					, pxy[2] + ar1[4]] END ;
		END IF ;
	ELSEIF cmd ~* 'C' THEN
		ar2 = pxy || CASE WHEN cmd = 'C' THEN ar1
			ELSE ARRAY[pxy[1] + ar1[1]
				, pxy[2] + ar1[2]
				, pxy[1] + ar1[3]
				, pxy[2] + ar1[4]
				, pxy[1] + ar1[5]
				, pxy[2] + ar1[6]] END ;
	END IF ;
	cmd_prev = cmd ;

	-- ベジェ共通処理
	IF cmd ~* '[CS]' THEN
		pxy = ar2[7:8] ;
		IF $2 = 0 THEN
			-- 直線に簡略化
			poi = poi || ST_Point(pxy[1], pxy[2]) ;
		ELSE
			bzp = "201409"."05_bezier3_points"(ar2, $2) ;
			poi = poi || bzp[2:array_length(bzp, 1)] ;
			-- 先頭の点が前と重複するので省く
		END IF ;
	END IF ;
	-- RAISE INFO '%, %, %', cnt, pxy, poi ;
END LOOP ;

-- 最後が閉じない線の処理
IF poi IS NOT NULL THEN gms = gms || ST_MakeLine(poi) ; END IF ;

IF array_length(gms, 1) = 1 THEN
	-- ジオメトリ一つの場合
	RETURN gms[1] ;
ELSEIF NOT EXISTS (
	SELECT * FROM unnest(gms) geom
		WHERE ST_GeometryType(geom) <> 'ST_Polygon') THEN
	-- ジオメトリ複数で全てポリゴンなら ST_BuildArea で中マドあるポリゴン化
	RETURN ST_BuildArea(ST_Collect(gms)) ;
ELSE
	-- それ以外はとりあえず ST_Collect して返す
	RETURN ST_Collect(gms) ;
END IF ;
END ;
$BODY$ ;

-- Ex
SELECT *, "201409"."05_svgpath2geom"(d, 0.5)
FROM "201409"."02_svg_path" ;

library(RPostgreSQL)
library(maptools)
library(rgeos)
con = dbConnect(PostgreSQL(), dbname='xxx', port=xxx
	, user='xxx', password='xxx')
df1 = dbGetQuery(con, '
	SELECT fill, ST_AsText(
		ST_Rotate(ST_FlipCoordinates(
			ST_Collect("201409"."05_svgpath2geom"(d, 0.5))), -pi()/2))
		-- Mirror Y
	FROM "201409"."02_svg_path"
	WHERE fill <> $$#FFFFFF$$
	GROUP BY fill
')
invisible(dbDisconnect(con))
if(is.null(dev.list())) windows(height=(340-46)/96, width=(444-8)/96)
# par(bg=NA, plt=rep(c(1,99)/100, 2), xaxs='i', yaxs='i')
# plot(readWKT(df1[1,2]), col=df1[1,1])
par(bg='darkolivegreen1', plt=rep(c(1,99)/100, 2), xaxs='i', yaxs='i')
plot(readWKT(df1[1,2]), border='aquamarine4', col='aquamarine3')