Adequar a função write_geoJSONb_Features aos requisitos stable

[ppKrauss]

Depois dos resultados dos testes, com diff de coordenadas e diff de propriedades, definir requisitos e efetuar modificações nas funções abaixo. Se aprovado também incluir modificações na lib de origem.

-- originais de https://github.com/AddressForAll/pg_pubLib-v1/blob/main/src/pubLib03-json.sql
CREATE or replace FUNCTION jsonb_pretty(
  jsonb,            -- input
  compact boolean   -- true for compact format
) RETURNS text AS $f$
  SELECT CASE -- warning: incidental behaviour of strip_nulls.
    WHEN $2 THEN  json_strip_nulls($1::json)::text
    ELSE  jsonb_pretty($1)
  END
  -- from https://stackoverflow.com/a/27536804/287948
  -- pg16+ back to https://stackoverflow.com/a/70828187/287948
$f$ LANGUAGE SQL IMMUTABLE;
COMMENT ON FUNCTION jsonb_pretty(jsonb,boolean)
  IS 'Extends jsonb_pretty() to return canonical compact form when true';
-- SELECT jsonb_pretty(  jsonb_build_object('a',1, 'bla','bla bla'), true );

CREATE or replace FUNCTION jsonb_pretty_lines(j_input jsonb, opt int DEFAULT 0) RETURNS text AS $f$
 -- jsonB input
 SELECT CASE opt
   WHEN 0  THEN j_input::text
   WHEN 1  THEN jsonb_pretty(j_input)
   WHEN 2  THEN regexp_replace(regexp_replace(j_input::text, ' ?\{"type": "Feature", "geometry":\n', '{"type": "Feature", "geometry": ', 'g'), ' ?\{"type": "Feature", "geometry":', E'\n{"type": "Feature", "geometry":', 'g') || E'\n'  -- GeoJSON
   WHEN 3  THEN replace(regexp_replace(j_input::text, ' ?\{"type": "Feature", "geometry":\n', '{"type": "Feature", "geometry": ', 'g'), ' ', '') || E'\n'  -- GeoJSON
   WHEN 4  THEN jsonb_pretty(j_input,true)  -- canonical compact form
   END
$f$ language SQL IMMUTABLE;
COMMENT ON FUNCTION jsonb_pretty_lines(jsonB,int)
  IS 'Alternatives for jsonb_pretty() to return one item per line: 0 = to_text with no formating, 1=standard pretty, 2=GeoJSON preserving type, 3=GeoJSON removing type, 4=compact form';


CREATE or replace FUNCTION json_pretty_lines(j_input json, opt int DEFAULT 0) RETURNS text AS $f$
 -- json input (not jsonB!) 
 SELECT CASE opt
   WHEN 0  THEN j_input::text
   WHEN 1  THEN jsonb_pretty(j_input::jsonb)
   WHEN 2  THEN regexp_replace(regexp_replace(j_input::text, ' ?\{"type": "Feature", "geometry":\n', '{"type": "Feature", "geometry": ', 'g'), ' ?\{"type": "Feature", "geometry":', E'\n{"type": "Feature", "geometry":', 'g') || E'\n'  -- GeoJSON
   WHEN 3  THEN replace(regexp_replace(j_input::text, ' ?\{"type": "Feature", "geometry":\n', '{"type": "Feature", "geometry": ', 'g'), ' ', '') || E'\n'  -- GeoJSON
   WHEN 4  THEN json_strip_nulls(j_input)::text -- canonical compact form, same as jsonb_pretty(j,true)
   END
$f$ language SQL IMMUTABLE;
COMMENT ON FUNCTION json_pretty_lines(json,int)
  IS 'Format JSON like jsonB_pretty() to return one item per line: 0 = to_text with no formating, 1=standard Pretty, 2=GeoJSON preserving type, 3=GeoJSON removing type, 4=compact form';



-- original de https://github.com/AddressForAll/pg_pubLib-v1/blob/main/src/pubLib06pgis-geoJSON.sql
CREATE or replace FUNCTION write_geoJSONb_Features(
  sql_tablename text, -- ex. 'vw_grid_ibge_l1' or 'SELECT * FROM t WHERE cond'
  p_file text, -- ex. '/tmp/grid_ibge100km.geojson'
  sql_geom text DEFAULT 't1.geom', -- sql using t1 as alias for geom, eg. ST_Transform(t1.geom,4326)
  p_cols text DEFAULT NULL, -- null or list of p_properties. Ex. 'id_unico,pop,pop_fem_perc,dom_ocu'
  p_cols_orderby text[] DEFAULT NULL,
  col_id text default null, -- p_id, expressed as t1.colName. Ex. 't1.gid::text'
  p_pretty_opt int default 0, -- 0=no, 1=jsonb_pretty, 2=jsonb_pretty_lines(s,1), 3=jsonb_pretty_lines(s,2)
  p_decimals int default 6,
  p_options int default 0,  -- 0=better, 1=(implicit WGS84) tham 5 (explicit)
  p_name text default null,
  p_title text default null,
  p_id_as_int boolean default false
)
RETURNS text LANGUAGE 'plpgsql' AS $f$
  DECLARE
    msg text;
    sql_orderby text;
    sql_pre text;
    sql text;
  BEGIN
      IF position(' ' in trim(sql_tablename))>0 THEN
        sql_tablename := '('||sql_tablename||')';
      END IF;
      sql_orderby := CASE
        WHEN p_cols_orderby IS NULL OR array_length(p_cols_orderby,1) IS NULL THEN ''
        ELSE 'ORDER BY '||stragg_prefix('t1.',p_cols_orderby) END;
      sql_pre := format($$
        ST_AsGeoJSONb( %s, %s, %s, %s, %s, %s, %s, %s) %s
        $$,
        sql_geom, p_decimals::text, p_options::text,
        CASE WHEN col_id is null THEN 'NULL' ELSE 't1.'||col_id||'::text' END,
        CASE WHEN p_cols~'::jsonb?$' THEN regexp_replace(p_cols,'::jsonb?$','')
             WHEN p_cols is null THEN 'NULL'
             ELSE 'to_jsonb(t2)'
        END,
        COALESCE(p_name::text,'NULL'),
        COALESCE(p_title::text,'NULL'),
        COALESCE(p_id_as_int::text,'NULL'),
        sql_orderby
      );
      -- RAISE NOTICE '--- DEBUG sql_pre: %', sql_pre
      -- ex. 'ST_AsGeoJSONb( ST_Transform(t1.geom,4326), 6, 0, t1.gid::text, to_jsonb(t2) ) ORDER BY t1.gid'
      -- EXECUTE
      SELECT pg_catalog.pg_file_unlink(p_file)::text INTO sql;
      sql := format($$
        SELECT volat_file_write(
                %L,
                jsonb_pretty_lines( jsonb_build_object('type','FeatureCollection', 'features', gj), %s)
             )
        FROM (
          SELECT jsonb_agg( %s ) AS gj
          FROM %s t1 %s
        ) t3
       $$,
       p_file,
       p_pretty_opt::text,
       sql_pre, sql_tablename,
       CASE WHEN p_cols IS NULL OR p_cols~'::jsonb?$' THEN ''
            ELSE ', LATERAL (SELECT '||p_cols||') t2'
       END
      );
      -- RAISE NOTICE E'--- DEBUG SQL: ---\n%\n', sql
      EXECUTE sql INTO msg;
      RETURN msg;
  END
$f$;
COMMENT ON FUNCTION write_geojsonb_Features
  IS 'run file_write() dynamically to save specified relation as GeoJSONb FeatureCollection.'
;

[ppKrauss]

Primeira dica de solução, partindo de pretty canônico:

create table lixgeo2 as
select t2.*, CASE WHEN substring(t2.x,1,3)!='##[' THEN 0 ELSE 1+t2.id%6 END as mod6 
FROM (  select regexp_replace(
                j::text,
                '\s*\[\s*([\-\d\.]+)\s*,\s*([\-\d\.]+)\s*\](,)?',
                E'\n##[\\1,\\2]\\3',
                'g'
             ) as j2
   from lixgeo) t1,  regexp_split_to_table(t1.j2,E'\n') with ordinality t2(x,id)
;

Resulta em algo como

                         x                         | id  | mod6 
---------------------------------------------------+-----+------

                                                   |   1 |    0
 {                                                 |   2 |    0
     "type": "FeatureCollection",                  |   3 |    0
     "features": [                                 |   4 |    0
         {                                         |   5 |    0
             "type": "Feature",                    |   6 |    0
             "geometry": {                         |   7 |    0
                 "type": "MultiPolygon",           |   8 |    0
                 "coordinates": [                  |   9 |    0
                     [                             |  10 |    0
                         [                         |  11 |    0
 ##[-38.69943,-12.79452],                          |  12 |    1
 ##[-38.69856,-12.80055],                          |  13 |    2
 ##[-38.69744,-12.80823],                          |  14 |    3
 ##[-38.69627,-12.81409],                          |  15 |    4
 ##[-38.69555,-12.8164],                           |  16 |    5
 ##[-38.693,-12.82123],                            |  17 |    6
 ##[-38.69082,-12.82526],                          |  18 |    1
 ##[-38.68594,-12.83339],                          |  19 |    2
 ##[-38.67869,-12.8417],                           |  20 |    3
 ##[-38.67523,-12.84517],                          |  21 |    4
 ##[-38.67051,-12.84869],                          |  22 |    5
 ##[-38.66489,-12.85235],                          |  23 |    6
 ##[-38.66218,-12.85406],                          |  24 |    1
 ##[-38.65869,-12.8557],                           |  25 |    2
 ##[-38.62189,-12.87469],                          |  26 |    3
 ##[-38.61227,-12.87883],                          |  27 |    4
 ##[-38.60375,-12.88327],                          |  28 |    5
 ##[-38.59446,-12.88839],                          |  29 |    6
 ##[-38.58569,-12.8937],                           |  30 |    1
 ##[-38.57494,-12.90502],                          |  31 |    2
...
 ##[-38.69937,-12.78274],                          | 646 |    5
 ##[-38.69935,-12.78864],                          | 647 |    6
 ##[-38.69943,-12.79452]                           | 648 |    1
                         ]                         | 649 |    0
                     ]                             | 650 |    0
                 ]                                 | 651 |    0
             },                                    | 652 |    0
             "properties": {                       | 653 |    0
                 "ddd": 71,                        | 654 |    0
                 "name": "Salvador",               | 655 |    0
                 "abbrev": "SDR",                  | 656 |    0
                 "osm_id": 362362,                 | 657 |    0
                 "name_en": null,                  | 658 |    0
                 "isolevel": "3",                  | 659 |    0
                 "lexlabel": "salvador",           | 660 |    0
                 "parent_id": 362413,              | 661 |    0
                 "admin_level": 8,                 | 662 |    0
                 "wikidata_id": 36947,             | 663 |    0
                 "isolabel_ext": "BR-BA-Salvador", | 664 |    0
                 "jurisd_base_id": 76,             | 665 |    0
                 "jurisd_local_id": 2927408        | 666 |    0
             }                                     | 667 |    0
         }                                         | 668 |    0
     ]                                             | 669 |    0
 }                                                 | 670 |    0
                                                   | 671 |    0

Agora só agregar as linhas marcas com "##" em blocos de 6, conforme mostram os contadores da coluna mod6.
Para tanto a sugestão é usar o módulo XML: ele vai manter as demais linhas na ordem e agregar, na mesma ordem, os blocos de 6 coordenadas.

SELECT CASE WHEN open_block THEN '<line>' else '' END || 
       CASE WHEN mod6=0 THEN xmlelement(name line, xmlattributes(id as num),lin)::text
            ELSE xmlelement(name coord, xmlattributes(id as num),lin)::text
            END  ||
       CASE WHEN close_block THEN '</line>' else '' END
       AS xlin,
       *
FROM (
   select *, mod6>LEAD(mod6) over() AND mod6!=0 as close_block,
            (mod6<LAG(mod6) over() AND mod6!=0) or (0=LAG(mod6) over() AND mod6>0) as open_block
   from lixgeo2
) t
;

[ppKrauss]

feito, @flipreis21 testar com a função geojson_repretty(). Ver fonte em https://github.com/AddressForAll/pg_pubLib-v1/blob/main/src/pubLib06pgis-geoJSON.sql#L166

[flipreis21]

feito, @flipreis21 testar com a função geojson_repretty(). Ver fonte em https://github.com/AddressForAll/pg_pubLib-v1/blob/main/src/pubLib06pgis-geoJSON.sql#L166

Foi utilizada editando a função jsonb_pretty_lines() alterando a opção 4 da seguinte forma:

CREATE or replace FUNCTION jsonb_pretty_lines(j_input jsonb, opt int DEFAULT 0) RETURNS text AS $f$
 -- jsonB input
 SELECT CASE opt
   WHEN 0  THEN j_input::text
   WHEN 1  THEN jsonb_pretty(j_input)
   WHEN 2  THEN regexp_replace(regexp_replace(j_input::text, ' ?\{"type": "Feature", "geometry":\n', '{"type": "Feature", "geometry": ', 'g'), ' ?\{"type": "Feature", "geometry":', E'\n{"type": "Feature", "geometry":', 'g') || E'\n'  -- GeoJSON
   WHEN 3  THEN replace(regexp_replace(j_input::text, ' ?\{"type": "Feature", "geometry":\n', '{"type": "Feature", "geometry": ', 'g'), ' ', '') || E'\n'  -- GeoJSON
   WHEN 4  THEN geojson_repretty(j_input)  -- Alternative for jsonb_pretty() to return GeoJSON pretty and coordinates in compact form
   END
$f$ language SQL IMMUTABLE;
COMMENT ON FUNCTION jsonb_pretty_lines(jsonB,int)
  IS 'Alternatives for jsonb_pretty() to return one item per line: 0 = to_text with no formating, 1=standard pretty, 2=GeoJSON preserving type, 3=GeoJSON removing type, 4=Alternative for jsonb_pretty()';

[ppKrauss]

@flipreis21 , demonstrar o uso da nova função em um ou dois municípios e em seguida iniciar Camarões, Brasil e Colômbia, nessa ordem.

[flipreis21]

@flipreis21 , demonstrar o uso da nova função em um ou dois municípios e em seguida iniciar Camarões, Brasil e Colômbia, nessa ordem.

Os testes das funções estão aqui neste repositório data/SE/GeneralMaynard-jurisd.geojson e data/RJ/AngraReis-jurisd.geojson entre outros descritos no whatsapp.

Fiz pull request para os 3 países com geometrias atuais de 2025: Camarões, Brasil e Colômbia

Para o Brasil foi solicitado também as geometrias de 2023, no entanto preciso do suporte do Claiton, pois os script de ingestão usado nas geometrias atuais em todos os países(com sucesso) não deu certo para o dado histórico do Brasil.