ST_SquareGrid

Conferir se bate geometrias ST_SquareGrid com as da biblioteca https://github.com/osm-codes/BR_IBGE/blob/main/src/step4_prepareGridLibs.sql

Encaixe do país

Não permite ajuste de origem, portanto não permite compatibilizar a grade. Compare com o original, https://github.com/osm-codes/BR_IBGE/blob/main/data/grid_ibge100km.geojson

DROP table pgis_grid_500km;
create table pgis_grid_500km AS WITH grid AS (
  SELECT size, (ST_SquareGrid(size, ST_Transform(geom,952019))).*
  FROM countries, (SELECT 500000 as size) t0 WHERE iso_a2='BR'
) SELEcT ROW_NUMBER() OVER() as gid,
         (select array[round( (ST_X(g)-2700000.0)/size::float )::int, round( (ST_Y(g)-7350000.0)/size::float)::int ] FROM (SELECT st_centroid(geom) g) t2) AS xy,
         geom
  FROM grid
;
DROP table pgis_grid_100km;
create table pgis_grid_100km AS WITH grid AS (
  SELECT size, (ST_SquareGrid(size, ST_Transform(geom,952019))).*
  FROM countries, (SELECT 100000 as size) t0 WHERE iso_a2='BR'
) SELEcT ROW_NUMBER() OVER() as gid,
         (select array[round( (ST_X(g)-2700000.0)/size::float )::int, round( (ST_Y(g)-7350000.0)/size::float)::int ] FROM (SELECT st_centroid(geom) g) t2) AS xy,
         geom
  FROM grid
;

DROP table pgis_grid_500km;

CREATE TABLE pgis_grid_500km AS
WITH
 ct AS   ( SELECT ST_Transform(geom,952019) AS geom FROM countries WHERE iso_a2='BR' ),
 grid AS (
  SELECT size, (ST_SquareGrid(size,ST_collect(geom, st_buffer(ST_SetSRID( ST_MakePoint(2800000,7350000),952019),1000) ))).*
  FROM ct, (SELECT 500000 as size) t0
)
 SELECT t2.*
 FROM (
   SELEcT ROW_NUMBER() OVER() as gid,
         (select array[round( (ST_X(g)-2800000.0)/size::float )::int, round( (ST_Y(g)-7350000.0)/size::float)::int ] FROM (SELECT st_centroid(geom) g) t2) AS xy,
         geom
   FROM grid
 ) t2, ct
 WHERE xy[1]>=0 AND xy[2]>=0 AND ST_Intersects(ct.geom,t2.geom) 
;
----
DROP table pgis_grid_100km;

CREATE TABLE pgis_grid_100km AS
WITH
 ct AS   ( SELECT ST_Transform(geom,952019) AS geom FROM countries WHERE iso_a2='BR' ),
 grid AS (
  SELECT size, (ST_SquareGrid(size,ST_collect(geom, st_buffer(ST_SetSRID( ST_MakePoint(2800000,7350000),952019),1000) ))).*
  FROM ct, (SELECT 100000 as size) t0
)
 SELECT t2.*
 FROM (
   SELEcT ROW_NUMBER() OVER() as gid,
         (select array[round( (ST_X(g)-2800000.0)/size::float )::int, round( (ST_Y(g)-7350000.0)/size::float)::int ] FROM (SELECT st_centroid(geom) g) t2) AS xy,
         geom
   FROM grid
 ) t2, ct
 WHERE xy[1]>=0 AND xy[2]>=0 AND ST_Intersects(ct.geom,t2.geom) 
;

Encaixe em célula ou grupo de células

Funciona, mas não traz grande vantagem exceto talvez em rapidez da view. Conferir com caso exato se ainda assim acrescenta vizinha externa.

CREATE TABLE pgis_grid_100b AS
WITH
 ct AS   ( SELECT ST_Transform(ST_collect(geom),952019) AS geom FROM grade_id04 where gid<10000 ),
 grid AS (
  SELECT size, (ST_SquareGrid(
    size,
    geom
   )).*
  FROM ct, (SELECT 1000 as size) t0
)
 SELECT t2.*
 FROM (
   SELEcT ROW_NUMBER() OVER() as gid,
         (select array[round( (ST_X(g)-2800000.0)/size::float )::int, round( (ST_Y(g)-7350000.0)/size::float)::int ] FROM (SELECT st_centroid(geom) g) t2) AS xy,
         geom
   FROM grid
 ) t2, ct
 WHERE xy[1]>=0 AND xy[2]>=0 AND ST_Intersects(ct.geom,t2.geom) 
;