-- crea geotabella vuota che conterrà i punti casuali
CREATE TABLE punti
(id INTEGER PRIMARY KEY AUTOINCREMENT, cir_id INTEGER);
-- popola il campo ID della geotabella
INSERT INTO punti
WITH RECURSIVE
  cnt (id) AS (SELECT 1 UNION ALL SELECT id + 1 FROM cnt LIMIT 100)
SELECT id, 0 FROM cnt;
SELECT AddGeometryColumn ('punti', 'geom', 4326, 'POINT', 'XY');
-- aggiorna la tabella con le coordinate random all''interno del bbox
UPDATE punti SET geom =
(SELECT makepoint (
	CAST (
	(select (0.5 - RANDOM ()/CAST(-9223372036854775808 AS REAL)/2)*(extent_max_x - extent_min_x) + extent_min_x
	FROM "vector_layers_statistics" WHERE id = punti.id AND table_name = 'poly') AS REAL),
	CAST (
	(select (0.5 - RANDOM ()/CAST (-9223372036854775808 AS REAL)/2)*(extent_max_y - extent_min_y) + extent_min_y
	FROM "vector_layers_statistics" WHERE id = punti.id AND table_name ='poly') AS REAL), 4326)
WHERE id = punti.id);

CREATE TABLE cerchi AS
  SELECT id, ST_Buffer(geom, 0.01) AS geom 
    FROM punti;
SELECT RecoverGeometryColumn ('cerchi', 'geom', 4326, 'POLYGON', 'XY');

-- estrai i perimetri e li smonta
CREATE TABLE perimetri AS
  SELECT ST_Union(ST_ExteriorRing(geom)) AS geom
    FROM cerchi;
SELECT RecoverGeometryColumn ('perimetri', 'geom', 4326, 'MULTILINESTRING', 'XY');

-- crea geotabella polygonize
CREATE TABLE polygonize AS
SELECT St_Polygonize(geom) as geom
FROM perimetri;
SELECT RecoverGeometryColumn('polygonize','geom',4326,'MULTIPOLYGON','XY');

-- crea geotabella dalle componenti elementari della geotabella polygonize
SELECT DropGeoTable('elementi');
SELECT ElementaryGeometries( 'polygonize' ,
                             'geom' , 'elementi' ,'out_pk' , 'out_multi_id', 1 ) as num, 'poligoni splitted' as label;

-- crea poligoni di output con attributi
SELECT DropGeoTable( "OUTPUT");
CREATE TABLE OUTPUT AS
SELECT Group_Concat(id) as id, e.geom
FROM cerchi p, elementi e
where st_intersects (ST_PointOnSurface(e.geom), p.geom) = 1
GROUP BY e.geom;
SELECT RecoverGeometryColumn('OUTPUT','geom',4326,'POLYGON','XY');