Geometrias da grade IBGE original

Adaptando a função de Load para criar tabela simples de gid e geometria.

DROP TABLE grid_ibge.tmp_censo2010_geom CASCADE;
CREATE TABLE grid_ibge.tmp_censo2010_geom (
  gid bigint NOT NULL PRIMARY KEY,  -- ID da compacta
  quadrante smallint NOT NULL, -- original
  id_unico text,
  geom geometry(MultiPolygon,952019) -- Célula Albers
);

CREATE or replace FUNCTION grid_ibge.tmp_censo2010_geom_load(p_tabgrade text) RETURNS text AS $f$
DECLARE
  q0 text;
  r0 int;
BEGIN
  RAISE NOTICE ' Processando % ...', p_tabgrade;
  q0 := $$
   coredata AS (
    SELECT quadrante, id_unico,
         substr(id_unico,1,4)='200M' AS is_200m,
         ST_X(geom2)::real gx, ST_Y(geom2)::real gy,
         ST_Simplify(geom) AS geom
    FROM (
     SELECT substr(quadrante,4)::smallint AS quadrante,
            id_unico, geom,
            ST_Centroid(geom) AS geom2
     FROM ( SELECT quadrante, id_unico, ST_Transform( geom, 952019 ) AS geom FROM tg ) t_aux
    ) t
   ),
   ins AS (
   INSERT INTO grid_ibge.tmp_censo2010_geom(gid, quadrante, id_unico, geom)
     SELECT grid_ibge.coordinate_encode(gx,gy,is_200m), quadrante, id_unico, geom
     FROM coredata
     ORDER BY 1
   RETURNING 1
   )
   SELECT COUNT(*) FROM ins
 $$;
 EXECUTE format(
    'WITH tg AS (SELECT * FROM %s), %s'
    ,  p_tabgrade, q0
 ) INTO r0;
 -- ... and EXECUTE DROP!
 RETURN p_tabgrade||': '|| r0::text || ' itens inseridos';
END;
$f$ LANGUAGE PLpgSQL;

------------
CREATE or replace VIEW vw_tmp_ibgetabs AS
  SELECT  table_name
  FROM information_schema.tables
  WHERE table_schema='public' AND table_name LIKE 'grade_id%'
  ORDER BY table_name
;

--- INGESTÃO:

DELETE FROM grid_ibge.tmp_censo2010_geom; -- is a refresh, ignores old data.
SELECT grid_ibge.tmp_censo2010_geom_load(table_name) FROM vw_tmp_ibgetabs;

-- teste:
SELECT round(st_area(t.geom)) area, MAX(t.gid>>60) nivel, COUNT(*) n 
FROM grid_ibge.tmp_censo2010_geom t
-- FROM  grid_ibge.tmp_censo2010_geom t INNER JOIN grid_ibge.censo2010_info c ON t.gid=c.gid
-- WHERE gid > (6::bigint << 60)  -- restringe nivel 6
GROUP BY 1;
--  area   | nivel |    n    
--   40000 |     6 | 4610350
-- 1000000 |     5 | 8676139

Rodando em true:

      resource       | tables | tot_bytes  | tot_size | tot_lines | bytes_per_line 
---------------------+--------+------------+----------+-----------+----------------
 Grade IBGE original |     56 | 4311826432 | 4112 MB  |  13286489 |            325
 Grade compacta      |      1 |  726310912 | 693 MB   |  13919652 |             52
(2 rows)

 nivel | n_compact_cells 
-------+-----------------
     0 |              56
     3 |           90624
     4 |          358069
     5 |         8860553
     6 |         4610350
(5 rows)

REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
 x10_min  | x10_max  
----------+----------
 28095000 | 76205000
(1 row)

 y10_min  |  y10_max  
----------+-----------
 75995000 | 119205000