Criar AFAcodes científico para El Salvador

[0e1]

Disponibilizar em https://test.afa.codes AFAcodes científico para El Salvador, utilizando EPSG:5399 e geometria do OpenStreeMap.

Descrever as alterações feitas para criar a cobertura L0 e o que foi feito para o país estar disponível na interface de testes.

[0e1]

A seguir, é mostrado como criar Afacodes científico para SV na base de teste DL06t_main (http://test.afa.codes).

No código em bash abaixo obtemos a geometria do país do OpenStreetMap, importamos para um base de dados Ingest, cobrimos o país e disponibilizamos a geometria do país nas bases DL.

# Baixar arquivo da Geofabrik
mkdir elsalvador
cd elsalvador/
wget https://download.geofabrik.de/central-america/el-salvador-latest.osm.pbf


# Fazer a ingestão do pbf
cd /var/gits/_dg/preserv/src/
make ini_ingest pg_db=ingest99
osm2pgsql -E 4326 -c -d ingest99 -U postgres -H localhost --slim --hstore --extra-attributes --hstore-add-index --multi-geometry --number-processes 4 --style /usr/share/osm2pgsql/empty.style ~/elsalvador/el-salvador-latest.osm.pbf


# Funções auxiliares para criar grid
psql postgres://postgres@localhost/ingest99 -c "
-- /home/x/work/a4a/gits/GGeohash/src/step02def-libGGeohash.sql
CREATE SCHEMA IF NOT EXISTS ggeohash;

CREATE or replace FUNCTION ggeohash.draw_cell_bycenter(
  cx int,  -- Center X
  cy int,  -- Center Y
  r int,   -- halfside ou raio do circulo inscrito
  p_translate boolean DEFAULT false, -- true para converter em LatLong (WGS84 sem projeção)
  p_srid int DEFAULT 4326          -- WGS84
) RETURNS geometry AS $f$
SELECT CASE WHEN p_translate THEN ST_Transform(geom,4326) ELSE geom END
FROM (
  SELECT ST_GeomFromText( format(
    'POLYGON((%s %s,%s %s,%s %s,%s %s,%s %s))',
    cx-r,cy-r, cx-r,cy+r, cx+r,cy+r, cx+r,cy-r, cx-r,cy-r
  ), p_srid) AS geom
) t
$f$ LANGUAGE SQL IMMUTABLE;
COMMENT ON FUNCTION ggeohash.draw_cell_bycenter(int,int,int,boolean,int)
  IS 'Draws a square-cell centered on the requested point, with requested radius (half side) and optional translation and SRID.'
;

-- /home/x/work/a4a/gits/GGeohash/src/step03def-lib.sql
DROP SCHEMA IF EXISTS osmc CASCADE;
CREATE SCHEMA osmc;

CREATE or replace FUNCTION osmc.ij_to_xy(
  i  int, -- coluna
  j  int, -- linha
  x0 int, -- referencia de inicio do eixo x [x0,y0]
  y0 int, -- referencia de inicio do eixo y [x0,y0]
  s  int  -- lado da célula
) RETURNS int[] AS $f$
  SELECT array[
    x0 + i*s,
    y0 + j*s
  ]
$f$ LANGUAGE SQL IMMUTABLE;
COMMENT ON FUNCTION osmc.ij_to_xy(int,int,int,int,int)
 IS 'Retorna canto inferior esquerdo de célula na matriz.'
;

CREATE or replace FUNCTION osmc.ij_to_geom(
  i  int, -- coluna
  j  int, -- linha
  x0 int, -- referencia de inicio do eixo x [x0,y0]
  y0 int, -- referencia de inicio do eixo y [x0,y0]
  s  int, -- lado da célula
  sr int  -- srid
) RETURNS geometry AS $f$
  SELECT ggeohash.draw_cell_bycenter(v[1]+s/2,v[2]+s/2,s/2,false,sr)
  FROM
  (
    SELECT osmc.ij_to_xy(i,j,x0,y0,s) v
  ) t
"

# Definir cobertura
psql postgres://postgres@localhost/ingest99 -c "
DROP   SCHEMA IF EXISTS el CASCADE;
CREATE SCHEMA           el;

DROP VIEW el.jurisd1;
CREATE VIEW el.jurisd1 AS
  SELECT osm_id*-1 AS osm_id, tags, way AS geom
  FROM jplanet_osm_polygon
  WHERE tags->>'admin_level'='2'
  -- SELECT *
  -- FROM optim.vw01full_jurisdiction_geom g
  -- WHERE isolabel_ext = 'SV'
;

DROP VIEW IF EXISTS el.origem;
CREATE VIEW el.origem AS
SELECT 1 AS id, ST_X(geom), ST_Y(geom), geom
FROM ST_Transform(ST_SetSRID(ST_MakePoint(-90.22350,13.13846),4326),5399) AS geom;


DROP VIEW IF EXISTS el.grid CASCADE;
CREATE VIEW el.grid AS
  SELECT i, j,
         lpad((j*10+i)::text,2,'0') AS ij,
         (j*10+i) AS ij_int,
        osmc.ij_to_geom(i,j,360000,195000,65536,5399) as geom
  FROM generate_series(0,2) as j, generate_series(0,4) as i
;

DROP VIEW IF EXISTS el.l0;
CREATE VIEW el.l0 AS
  SELECT ROW_NUMBER() OVER(ORDER BY j DESC, i ASC) as gid,
         i,
         j,
         ij,
         ij_int,
         geom
  FROM el.grid
  WHERE ST_Intersects(geom, (SELECT ST_Transform(geom,5399) FROM el.jurisd1) )
;"


# Mover geometria para bases DL
psql postgres://postgres@localhost/ingest99 -c "
DROP TABLE newcountry;
CREATE TABLE newcountry AS
  SELECT osm_id*-1 AS osm_id, tags, way AS geom
  FROM jplanet_osm_polygon
  WHERE tags->>'admin_level'='2'
;"

pg_dump -t newcountry postgres://postgres@localhost/ingest99 | psql postgres://postgres@localhost/dl06t_main
pg_dump -t newcountry postgres://postgres@localhost/ingest99 | psql postgres://postgres@localhost/dl05s_main


# Atualizar geometrias
psql postgres://postgres@localhost/dl06t_main -c "
INSERT INTO optim.jurisdiction_geom(osm_id,isolabel_ext,geom)
SELECT osm_id, tags->>'ISO3166-1:alpha2' AS isolabel_ext, geom
FROM newcountry
ON CONFLICT (osm_id)
DO UPDATE
SET geom = EXCLUDED.geom;"

psql postgres://postgres@localhost/dl05s_main -c "
INSERT INTO optim.jurisdiction_geom(osm_id,isolabel_ext,geom)
SELECT osm_id, tags->>'ISO3166-1:alpha2' AS isolabel_ext, geom
FROM newcountry
ON CONFLICT (osm_id)
DO UPDATE
SET geom = EXCLUDED.geom;"


# Excluir tabela
psql postgres://postgres@localhost/dl06t_main -c "DROP TABLE newcountry;"
psql postgres://postgres@localhost/dl05s_main -c "DROP TABLE newcountry;"


# Adicionar int_country_id
psql postgres://postgres@localhost/dl06t_main -c "
UPDATE optim.jurisdiction
SET int_country_id = 6
WHERE osm_id = 1520612;"

psql postgres://postgres@localhost/dl05s_main -c "
UPDATE optim.jurisdiction
SET int_country_id = 6
WHERE osm_id = 1520612;"

Após definir a cobertura:

1. preencher o conf.yaml, conforme eb588bc
   1.1. Procurar seguir a curva Z na definição do L0 em grid_l0_cell_sci_base
2. Ajustar a função osmc.L0cover_upsert, conforme https://github.com/osm-codes/GGeohash/commit/9a2e07e074f25d5589eed95819ace1a0c3ef5502
   2.1. Executar a linha adicionada na base DL06t_main
3. Criar uma função de encode especifica para o país conforme https://github.com/osm-codes/GGeohash/commit/e000aea7c7d58309d7100891e6442da44b56e723
4. Criar synonym.csv conforme 8b8d4b7
5. Ajustar apis conforme osm-codes/WS@211134b
6. Recriar views materializadas executando psql postgres://postgres@localhost/dl06t_main < /var/gits/_dg/preserv/src/optim-step4-api.sql
7. Adicionar o país ao dicionário em def.js conforme osm-codes/gridMap-draftPages@780ed2e

Caso EPSG:5399 não conste spatial_ref_sys executar:

INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) VALUES
(
  5399,
  'El Salvador Lambert',ON ggeohash.draw_cell_bycenter(
  5399,
  '+proj=lcc +lat_1=13.7833333333333 +lat_0=13.7833333333333 +lon_0=-89 +k_0=0.99996704 +x_0=500000 +y_0=295809.184',
  $$CONVERSION["El Salvador Lambert",culo inscrito
    METHOD["Lambert Conic Conformal (1SP)",converter em LatLong (WGS84 sem projeção)
        ID["EPSG",9801]],
)   PARAMETER["Latitude of natural origin",13.7833333333333,
        ANGLEUNIT["degree",0.0174532925199433],geom,4326) ELSE geom END
        ID["EPSG",8801]],
    PARAMETER["Longitude of natural origin",-89,
        ANGLEUNIT["degree",0.0174532925199433],
        ID["EPSG",8802]], cx-r,cy-r
  ),PARAMETER["Scale factor at natural origin",0.99996704,
)       SCALEUNIT["unity",1],
        ID["EPSG",8805]],E;
    PARAMETER["False easting",500000,l_bycenter(int,int,int,boolean,int)
        LENGTHUNIT["metre",1],d radius (half side) and optional translation and SRID.'
;       ID["EPSG",8806]],
    PARAMETER["False northing",295809.184,
        LENGTHUNIT["metre",1],ash/src/step03def-lib.sql
        ID["EPSG",8807]],
    ID["EPSG",5399]]$$
);