【SpatiaLite】WITH RECURSIVEを使いGEOMETRYCOLLECTION内の要素をイテレートする

動作確認は下図のようなspatialite_guiで行いました。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/122210/0aa273a1-3fb2-1959-c29d-fa068f835d7b.png)

以下のテーブルを例に説明します。

```sql
CREATE TABLE geom_collections(
	geom_id INTEGER PRIMARY KEY
);
SELECT AddGeometryColumn(
    'geom_collections',  		-- table name
    'geom',          		-- column name
    4326,            		-- SRID: WGS84
    'GEOMETRYCOLLECTION',	-- geometry type
    'XY',            		-- dimension
    1                		-- not null
);

INSERT INTO geom_collections (geom)
VALUES
	(GeomFromText(
		'GEOMETRYCOLLECTION(POINT(0 1), POLYGON((1 1, 2 3, 2 2, 1 1)))',
		4326
	)),
	(GeomFromText(
		'GEOMETRYCOLLECTION(LINESTRING(4 4, 5 5, 6 0), POINT(2.2 2.3), POINT(1 9))',
		4326
	)),
	(GeomFromText(
		'GEOMETRYCOLLECTION(POLYGON((-1 -1, 0 0, 0 4, -1 -1)))',
		4326
	))
;
```

`geom_collections`の中身を確認してみましょう。

```sql
SELECT
    geom_id,
    NumGeometries(geom) AS geom_count,
    AsText(geom) AS geom_text
FROM
    geom_collections;
```

| geom_id | geom_count | geom_text |
|-----|-----|--------------------------------------------|
| 1 | 2 | GEOMETRYCOLLECTION(POINT(0 1), POLYGON((1 1, 2 3, 2 2, 1 1))) |
| 2 | 3 | GEOMETRYCOLLECTION(POINT(2.2 2.3), POINT(1 9), LINESTRING(4 4, 5 5, 6 0)) |
| 3 | 1 | GEOMETRYCOLLECTION(POLYGON((-1 -1, 0 0, 0 4, -1 -1))) |

1行目のGEOMETRYCOLLECTIONにはジオメトリが2つ、2行目には3つ、3行目には1つ入っています。
このコレクション内の要素をイテレートして取り出すには、以下のように`WITH RECURSIVE`を使います。

```sql
-- 注）この方法はパフォーマンスが良くない（追記参照）
WITH RECURSIVE g(geom_id, collection_idx, geom_in_collection) AS
(
	SELECT
		geom_id,
		1 AS collection_idx,
		GeometryN(geom, 1) AS geom_in_collection
	FROM
		geom_collections
UNION
	SELECT
		geom_collections.geom_id,
		collection_idx + 1 AS collection_idx,
		GeometryN(geom, collection_idx + 1) AS geom_in_collection
	FROM
		g, geom_collections
	WHERE
		collection_idx + 1 <= NumGeometries(geom)
)
SELECT
	geom_id,
	collection_idx,
	AsText(geom_in_collection) AS geom_text
FROM
	g
ORDER BY
	geom_id,
	collection_idx
;
```

| geom_id | collection_idx | geom_text |
|-----|-----|--------------------------|
| 1 | 1 | POINT(0 1) |
| 1 | 2 | POLYGON((1 1, 2 3, 2 2, 1 1)) |
| 2 | 1 | POINT(2.2 2.3) |
| 2 | 2 | POINT(1 9) |
| 2 | 3 | LINESTRING(4 4, 5 5, 6 0) |
| 3 | 1 | POLYGON((-1 -1, 0 0, 0 4, -1 -1)) |

`AsText(geom_in_collection)`のところを任意の関数に置き換えれば、GEOMETRYCOLLECTION内の各要素について操作ができます。

## 追記

上記のSQLだと、WITH RECURSIVE句の処理内で、同じ値の行が大量に生成され、それをUNIONによって1行に縮退させる処理が走ります。
そのため、処理件数が多いときに、同じ処理が繰り返され非常にパフォーマンスが悪くなります。
以下の方がパフォーマンス的に良いようです。


```sql
WITH RECURSIVE g(collection_idx, cnt) AS
(
		SELECT
			1 AS collection_idx,
			MAX(NumGeometries(geom)) AS cnt
		FROM
			geom_collections
	UNION ALL
		SELECT
			collection_idx + 1 AS collection_idx,
			cnt
		FROM
			g
		WHERE
			collection_idx + 1 <= cnt
)
SELECT
	geom_id,
	collection_idx,
	AsText(geom) AS geom_text
FROM (
	SELECT
		geom_collections.geom_id AS geom_id,
		g.collection_idx AS collection_idx,
		GeometryN(geom_collections.geom, g.collection_idx) AS geom
	FROM
		geom_collections, g
)
WHERE
	geom IS NOT NULL
ORDER BY
	geom_id,
	collection_idx
;
```

ここで、`GeometryN`関数に、GEOMETRYCOLLECTIONが持つ要素数よりも大きなインデックスを与えた場合に`NULL`が返されることを前提としています。