Transform a geometry table in feasible (smaller) sizes to be processed in chunks and parallel - Postgis

prepara_insumo.sql

-- FUNCTION: public.prepara_insumo(character varying, character varying)

-- DROP FUNCTION public.prepara_insumo(character varying, character varying);

CREATE OR REPLACE FUNCTION public.prepara_insumo(
	tabela character varying,
	srid character varying DEFAULT NULL::character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 1
    VOLATILE 
AS $BODY$

	DECLARE
        sqlstatement character varying;
    BEGIN

		SET statement_timeout TO '1000min';	
		
		if srid is not null then
			execute 'select UpdateGeometrySRID(''public'',''' || tabela || ''', ''geom'','|| srid || ')';
		end if;
													
		execute 'alter TABLE public.' || tabela || ' rename to ' || tabela || '_old';
		
		SELECT 'create table public.' || tabela || ' as select row_number() over()::int insumo_id, a.* from (select '  
		|| array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
        FROM information_schema.columns As c
            WHERE table_name = tabela || '_old' 
            AND  c.column_name NOT IN('geom', 'gid', 'fid', 'ogc_fid', 'insumo_id', 'id', 'id1')
    ), ',') || ', st_makevalid(ST_Subdivide(ST_MAKEVALID(ST_TRANSFORM(o.geom, 900918)), 1200)) geom FROM public.' || tabela || '_old as o) a' into sqlstatement;
		
		execute sqlstatement;
		
		execute 'drop TABLE public.' || tabela || '_old CASCADE';
		
		execute 'alter TABLE public.' || tabela || ' rename to ' || tabela || '_old';
		
		--5.000 km²
		select 'create table public.' || tabela || ' as select row_number() over()::int insumo_id,' || array_to_string(ARRAY(SELECT 't' || '.' || c.column_name
									  FROM information_schema.columns As c
									WHERE table_name = tabela || '_old' AND c.column_name != 'insumo_id'
									  ), ',') || ' from ((
with input as (
	select ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
									  FROM information_schema.columns As c
									WHERE table_name = tabela || '_old'
									  ), ',') || '
	from public.' || tabela || '_old o where st_area(geom) > 5000000000), 
	unlabeled_cloud as (
        select
            ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
									  FROM information_schema.columns As c
									WHERE table_name = tabela || '_old'
									  ), ',') || ',
            (ST_Dump(ST_GeneratePoints(geom, 1000 * (ST_AREA(geom) / 2500000000) :: int))).geom pt,
            (ST_Area(geom) / 250000000) :: int                  k
        from input o
        group by insumo_id, ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
									  FROM information_schema.columns As c
									WHERE table_name = tabela || '_old' AND c.column_name != 'insumo_id'
									  ), ',') || '
        order by geom
    ),
	labeled_cloud as (
        select
            ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
									  FROM information_schema.columns As c
									WHERE table_name = tabela || '_old' AND c.column_name != 'geom'
									  ), ',') || ',
            pt,
            ST_ClusterKMeans(pt, k)
            over (
                partition by insumo_id
                ) cluster_id
        from
            unlabeled_cloud o
    ),
	labeled_centers as (
        select
            ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
									  FROM information_schema.columns As c
									WHERE table_name = tabela || '_old' AND c.column_name != 'geom'
									  ), ',') || ',
            cluster_id,
            ST_Centroid(ST_Collect(pt)) center
        from labeled_cloud o
        group by insumo_id, cluster_id, ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
									  FROM information_schema.columns As c
									WHERE table_name = tabela || '_old' AND c.column_name not in('insumo_id', 'geom')
									  ), ',') || '
	),
	voronoi_poly as (
        select
            ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
									  FROM information_schema.columns As c
									WHERE table_name = tabela || '_old' AND c.column_name != 'geom'
									  ), ',') || ',
            (ST_Dump(ST_VoronoiPolygons(ST_Collect(center)))).geom
        from labeled_centers o
        group by insumo_id, ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
									  FROM information_schema.columns As c
									WHERE table_name = tabela || '_old' AND c.column_name not in('insumo_id', 'geom')
									  ), ',') || '
    )
select 
' || array_to_string(ARRAY(SELECT 'v' || '.' || c.column_name
									  FROM information_schema.columns As c
									WHERE table_name = tabela || '_old' AND c.column_name != 'geom'
									  ), ',') || ', 
									  ST_Intersection(v.geom, i.geom) geom
from voronoi_poly v join input i on i.insumo_id = v.insumo_id)
union all
select ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
									  FROM information_schema.columns As c
									WHERE table_name = tabela || '_old'
									  ), ',') || '
from public.' || tabela || '_old o where st_area(geom) <= 5000000000) t' into sqlstatement;
		
		execute sqlstatement;
		
		--execute 'with T as (select insumo_id, row_number() over()::int rn from public.' || tabela || ') update T set insumo_id = rn';
		
		execute 'drop TABLE public.' || tabela || '_old CASCADE';
		
		drop index if exists  public.idx_insumo_order;
		
		execute 'create index idx_insumo_order
	  		ON public.' || tabela || '
			USING btree
			(insumo_id)';
			
		execute 'select UpdateGeometrySRID(''public'',''' || tabela || ''', ''geom'', 900918)';
		
		execute 'CLUSTER public.' || tabela || ' USING idx_insumo_order';
		
		execute 'Analyze public.'  || tabela;
		
		return;
    END;

$BODY$;

ALTER FUNCTION public.prepara_insumo(character varying, character varying)
    OWNER TO public;