osm_users_greedy_map.sql

create or replace procedure trim_osm_users_h3()
    language plpgsql
as
$$
declare
    cur_rec      record;
    counter      integer;
    total_rec    integer;
    last_seen    timestamptz;
    last_cluster timestamptz;
begin
    counter = 0;
    total_rec = ( select count(*) from osm_users_hex_in );
    last_seen = clock_timestamp();
    while true
        loop
            for cur_rec in ( select h3, osm_user, ctid from osm_users_hex_in order by count desc, h3 limit 100000 )
                loop
                    if not exists(select from osm_users_hex_in where ctid = cur_rec.ctid)
                    then
                        continue;
                    end if;
                    counter = counter + 1;
                    if counter % 10000 = 0
                    then
                        raise warning '% %% - % of % (% per block, % left)', 100.0 * counter / total_rec, counter, total_rec, clock_timestamp() - last_seen, (clock_timestamp() - last_seen) * (total_rec - counter) / 10000;
                        last_seen = clock_timestamp();
                        commit;
                    end if;
                    insert into osm_users_hex_out (h3, osm_user)
                    values (cur_rec.h3, cur_rec.osm_user);
                    delete from osm_users_hex_in where h3 = cur_rec.h3;
                    delete
                    from osm_users_hex_in using h3_k_ring(cur_rec.h3, 3) r
                    where h3 = r
                      and osm_user = cur_rec.osm_user;
                    --raise notice '%s %s', cur_rec.osm_user, cur_rec.h3;
                end loop;
            raise warning 'clustering...';
            last_cluster = clock_timestamp();
            cluster osm_users_hex_in using osm_users_hex_in_count_h3_osm_user_idx1;
            raise warning 'clustered in %', clock_timestamp() - last_cluster;
            total_rec = ( select count(*) from osm_users_hex_in ) + counter;
            if total_rec = counter then exit; end if;
        end loop;
end;
$$;
call trim_osm_users_h3();