Documentação Banco de Dados

Organização, estrutura e processo de criação do banco de dados demográfico baseado no World Population Prospects 2024

1 Documentação do Banco de Dados Demográfico

1.1 Objetivo

1.1.1 Objetivo Geral

Organizar e integrar, de forma sistemática, os dados demográficos derivados do World Population Prospects 2024 (UN-DESA (2024)), estruturando-os em um banco de dados que preserve a lógica original da base da ONU e, ao mesmo tempo, permita sua utilização ampliada em pesquisas acadêmicas e análises comparativas. O banco tem como finalidade apoiar análises demográficas, assegurando clareza quanto às fontes, cenários e métodos de estimativa, e facilitando consultas por tempo, território, indicador, sexo, idade e variante demográfica.

1.1.2 Objetivos Específicos

1.1.2.1 Organização coerente das dimensões demográficas

Estruturar os dados de modo a distinguir claramente as dimensões demográficas (tempo, território, indicadores, idade, sexo, variantes, fontes etc.) e os valores numéricos associados a cada combinação dessas dimensões.

1.1.2.2 Preservação dos identificadores internacionais

Manter os identificadores oficiais da ONU (por exemplo, locationId, indicatorId, timeId), assegurando compatibilidade com a base internacional e permitindo o diálogo direto com a API do WPP, reduzindo ambiguidades conceituais e operacionais.

1.1.2.3 Integração com bases nacionais

Possibilitar a incorporação de códigos e nomenclaturas alternativas (como códigos do IBGE e outras INE’s, bem como denominações em português ou espanhol), favorecendo análises comparativas sem comprometer a estrutura internacional de referência.

1.1.2.4 Transparência metodológica

Registrar informações sobre fonte, variante de projeção, tipo e método de estimativa, permitindo distinguir claramente:

• estimativas históricas;
• projeções;
• cenários alternativos;
• revisões metodológicas.

1.1.2.5 Reprodutibilidade analítica

Assegurar que o banco possa ser:

• criado automaticamente a partir de script;
• reconstruído integralmente quando necessário;
• atualizado de forma controlada e documentada.

1.1.2.6 Ampliação futura

Estruturar o esquema de modo que ele possa incorporar, no futuro:

• outros conjuntos demográficos (WUP, estimativas nacionais);
• indicadores socioeconômicos;
• dados subnacionais;
• métricas derivadas (taxas, razões, indicadores sintéticos).

1.1.2.7 Apoio à pesquisa e ao ensino

Disponibilizar uma base organizada que facilite:

• análises longitudinais;
• comparação entre cenários demográficos;
• extração eficiente de séries temporais;
• uso em disciplinas de demografia, geografia da população, SIG e áreas afins.

1.1.3 Escopo

O banco armazena dados estruturados e metadados associados, organizando-os para fins analíticos e acadêmicos. Não substitui a documentação metodológica oficial da ONU, mas atua como instrumento de operacionalização e integração dos dados demográficos para pesquisa e ensino.

1.2 Justificativa da modelagem

A modelagem adotada busca organizar os dados demográficos de forma coerente com a lógica analítica do World Population Prospects 2024 (UN-DESA (2024)), permitindo que cada valor seja interpretado como resultado da combinação entre tempo, território, indicador, idade, sexo e variante demográfica.

A separação entre tabelas dimensionais (tempo, localização, indicador, idade, sexo, variante, fonte) e tabela de fatos (valores numéricos) não é apenas uma decisão técnica, mas uma forma de estruturar o fenômeno demográfico como um sistema de medidas observadas em múltiplas dimensões. Essa organização reduz redundâncias, melhora a consistência interna e facilita análises comparativas e longitudinais.

Sempre que possível, preservam-se os identificadores oficiais da ONU (locationId, indicatorId, timeId), garantindo compatibilidade com a API do WPP e evitando ambiguidades decorrentes de variações textuais.

1.2.1 Estratégia tecnológica

Nesta etapa, o banco é implementado em SQLite como ambiente de prototipagem e validação estrutural. A escolha deve-se à portabilidade, simplicidade de uso e integração direta com o R, permitindo reconstrução integral por meio de script.

A arquitetura, entretanto, foi concebida para futura migração para PostgreSQL com PostGIS, possibilitando maior escalabilidade e integração com dados espaciais, especialmente em aplicações subnacionais.

1.2.2 Extensibilidade

O esquema foi projetado para permitir:

• incorporação de novas fontes demográficas;
• integração com bases nacionais;
• expansão para níveis subnacionais;
• inclusão de indicadores derivados;
• futura integração com dados espaciais.

1.3 Estrutura geral do banco de dadados

1.3.1 Arquitetura Geral

O banco de dados foi estruturado para organizar os dados demográficos do WPP segundo uma lógica analítica clara, separando valores quantitativos, classificações e informações auxiliares.

A arquitetura está organizada em três camadas complementares:

• Camada Analítica
• Camada de Metadados
• Camada de Ingestão

Essa separação permite distinguir entre os dados utilizados na análise demográfica, as informações que explicam esses dados e os mecanismos técnicos responsáveis pela sua obtenção e atualização.

1.3.2 Camada Analítica

A Camada Analítica constitui o núcleo do banco.

Ela é composta por:

• uma tabela de fatos, que armazena os valores dos indicadores demográficos;
• um conjunto de tabelas de dimensão, que organizam os principais eixos de análise.

A tabela de fatos reúne os valores observados ou projetados, enquanto as dimensões definem os recortes analíticos de: - tempo - território - indicador - idade - sexo - variante - tipo de estimativa - método - fonte

Cada registro da tabela de fatos corresponde a um valor demográfico definido por uma combinação específica desses eixos. Essa organização permite análises comparativas entre países, ao longo do tempo, por estrutura etária, sexo e diferentes variantes de projeção.

1.3.3 Camada de Metadados

A Camada de Metadados reúne informações que descrevem, qualificam ou contextualizam os dados analíticos.

Inclui, por exemplo: - definições de indicadores; - descrições conceituais; - unidades de medida; - notas metodológicas; - informações complementares por indicador ou por localização.

Essa camada não contém valores demográficos propriamente ditos, mas fornece o suporte interpretativo necessário para sua correta utilização.

1.3.4 Camada de Ingestão

A Camada de Ingestão é responsável pelo processo de obtenção, registro e atualização dos dados.

Ela pode incluir: - armazenamento das respostas brutas da API; - registros de processamento; - tabelas temporárias de validação.

Essa camada não integra o modelo analítico principal, mas garante reprodutibilidade, rastreabilidade e controle das atualizações.

1.3.5 Organização conceitual

De forma simplificada, a arquitetura pode ser representada como:

Camada de Ingestão -> Camada de Metadados -> Camada Analítica (fatos + dimensões)

A separação em camadas permite que o banco seja utilizado tanto para análise demográfica substantiva quanto para controle metodológico e atualização estruturada.

1.4 Dicionário das Tabelas (Camada Analítica)

Esta seção descreve as tabelas que compõem o núcleo analítico do banco de dados. Elas organizam as dimensões estruturais do fenômeno demográfico e armazenam os valores quantitativos observados ou projetados.

Estrutura mínima recomendada:

• fact_population
• dim_indicators
• dim_topics
• dim_sources
• dim_times
• dim_locations
• dim_ages
• dim_sexes
• dim_variants
• dim_categories

como descrito a seguir

1.4.1 fact_population

O que é esta tabela: Reúne os valores dos indicadores demográficos organizados por tempo, território, idade, sexo e características do cenário demográfico. Cada linha corresponde a um único valor demográfico definido por uma combinação específica dessas dimensões.

Como os registros são identificados: Cada linha possui um identificador interno (factId) utilizado apenas para organização do banco.

Um registro é definido pela combinação de:

• período de referência (timeId)
• localização (locationId)
• indicador (indicatorId)
• grupo etário (ageId)
• sexo (sexId)
• variante de projeção (variantId)
• tipo de estimativa (estimateTypeId)
• método de estimativa (estimateMethodId)
• fonte ou revisão (sourceId)

Em termos substantivos, isso significa que não pode existir mais de um valor para a mesma combinação dessas características.

Relação com outras tabelas: Cada campo que termina em Id corresponde a uma categoria definida em outra tabela do banco.

Por exemplo:

• timeId remete à tabela de períodos (dim_times)
• locationId remete à tabela de localidades (dim_locations)
• indicatorId remete à tabela de indicadores (dim_indicators)
• ageId remete à tabela de grupos etários (dim_ages)
• sexId remete à tabela de sexo (dim_sexes)
• variantId remete à tabela de variantes (dim_variants)
• estimateTypeId remete à tabela de tipos de estimativa
• estimateMethodId remete à tabela de métodos
• sourceId remete à tabela de fontes

Essas tabelas funcionam como dicionários de classificação.

Campos:

Tabela: Estrutura de fact_population
Campo	Tipo	Descrição
fact_id	BIGINT	Identificador técnico do registro
location_id	INTEGER	Unidade territorial
indicator_id	INTEGER	Indicador
source_id	INTEGER	Fonte dos dados
variant_id	INTEGER	Variante de projeção
time_id	INTEGER	Referência temporal
category_id	INTEGER	Categoria do valor (quando aplicável)
estimate_type_id	INTEGER	Tipo de estimativa (Id)
estimate_type	TEXT	Tipo de estimativa (histórica, projeção etc.)
estimate_method_id	INTEGER	Método de estimação (Id)
estimate_method	TEXT	Método de estimação
sex_id	INTEGER	Categoria de sexo
age_id	INTEGER	Categoria do grupo etário
value	REAL	Valor numérico do indicador

1.4.2 dim_indicators

O que é esta tabela: Reúne os atributos próprios dos indicadores disponibilizados pela API. Aqui devem permanecer o nome, a descrição, a forma de apresentação e os parâmetros de formatação e uso do indicador.

Observação de modelagem: Embora o endpoint de indicadores traga também informações de fonte e tema, esses campos devem ser separados em tabelas próprias para evitar repetição.

Campos:

Tabela: Estrutura de dim_indicators
Campo	Tipo	Descrição
indicator_id	INTEGER	Identificador único do indicador na base da ONU
indicator	TEXT	Nome completo do indicador
short_name	TEXT	Nome abreviado ou código curto do indicador
description	TEXT	Descrição conceitual do indicador conforme documentação oficial
indicator_display_name	TEXT	Nome utilizado para exibição em tabelas e interfaces
dim_age	INTEGER	Indica se o indicador admite desagregação por idade (1 = sim, 0 = não)
dim_sex	INTEGER	Indica se o indicador admite desagregação por sexo (1 = sim, 0 = não)
dim_variant	INTEGER	Indica se o indicador admite variantes de projeção (1 = sim, 0 = não)
dim_category	INTEGER	Indica se o indicador utiliza uma categoria substantiva adicional
default_age_id	INTEGER	Grupo etário padrão associado ao indicador
default_sex_id	INTEGER	Categoria de sexo padrão associada ao indicador
default_variant_id	INTEGER	Variante de projeção padrão associada ao indicador
default_category_id	INTEGER	Categoria padrão associada ao indicador, quando aplicável
variable_type	TEXT	Natureza substantiva da variável (ex.: absoluta, relativa, duração)
value_type	TEXT	Tipo de valor apresentado (ex.: número, percentual)
unit_scaling	REAL	Fator de escala aplicado à unidade do indicador
precision	INTEGER	Número de casas decimais recomendado para apresentação
is_thousand_separator_space	INTEGER	Indica se utiliza espaço como separador de milhar
format_string	TEXT	Padrão de formatação numérica recomendado
unit_short_label	TEXT	Rótulo curto da unidade de medida
unit_long_label	TEXT	Rótulo longo ou descritivo da unidade de medida
n_classes_default	INTEGER	Número padrão de classes sugerido para visualizações
download_file_name	TEXT	Nome sugerido para arquivo de download do indicador
topic_id	INTEGER	Identificador do tema ao qual o indicador pertence
indicator_source_id	INTEGER	Identificador da fonte associada à documentação do indicador

1.4.3 dim_topics

O que é esta tabela: agrupa os temas substantivos aos quais os indicadores pertencem.

Campos sugeridos

• topic_id
• name
• short_name
• sort_order

1.4.4 dim_sources

O que é esta tabela: Armazena a informação de fonte ou revisão associada aos indicadores e aos dados observados/projetados.

Campos

• source_id
• name
• source_year
• start_year
• end_year
• citation
• url
• author

1.4.5 dim_times

O que é esta tabela: Define os períodos de referência usados na base.

Campos

• time_id
• label
• time_start
• time_end
• time_mid
• duration

Observação de modelagem: Se for conveniente para uso analítico, pode-se acrescentar um campo derivado year, mas ele não precisa substituir o campo original label.

1.4.6 dim_locations

O que é esta tabela: Reúne as unidades territoriais utilizadas no banco, preservando os identificadores da ONU como referência principal.

Campos: idênticos aos disponíveis em https://population.un.org/wpp/assets/Excel%20Files/4_Metadata/WPP2024_F01_LOCATIONS.xlsx

Observação de modelagem: Nomes em português, espanhol ou códigos nacionais estão em tabelas auxiliares de harmonização, sem alterar a estrutura principal.

1.4.7 dim_ages

O que é esta tabela: Define os grupos etários utilizados pelos indicadores que possuem desagregação por idade.

Campos:

• age_id
• age
• age_start
• age_end

1.4.8 dim_sexes

O que é esta tabela

Armazena as categorias de sexo utilizadas pela API.

Campos

• sex_id
• label

1.4.9 dim_variants

O que é esta tabela

Armazena as variantes de projeção demográfica.

Campos

• variant_id
• variant_name
• variant_description
• variant_sort_order

1.4.10 dim_categories

O que é esta tabela: Armazena categorias substantivas associadas a alguns indicadores. Certos indicadores usam categoria (dimCategory = true) e definem também uma categoria padrão (defaultCategoryId).

Campos

• category_id
• category_name
• category_sort_order

1.5 Processo de criação do banco

library(DBI)
library(RSQLite)
library(here)

db_path <- here::here("data", "pop", "population_data.sqlite")

create_newdb <- function(db_path, overwrite = FALSE) {
  if (file.exists(db_path) & overwrite) {
    file.remove(db_path)
  }
  
  db <- dbConnect(RSQLite::SQLite(), db_path)
  dbExecute(db, "PRAGMA foreign_keys = ON;")
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS dim_topics (
      topic_id INTEGER NOT NULL,
      name TEXT NOT NULL,
      short_name TEXT,
      sort_order INTEGER,
      PRIMARY KEY (topic_id)
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS dim_sources (
      source_id INTEGER NOT NULL,
      name TEXT NOT NULL,
      source_year INTEGER,
      start_year INTEGER,
      end_year INTEGER,
      citation TEXT,
      url TEXT,
      author TEXT,
      PRIMARY KEY (source_id)
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS dim_times (
      time_id INTEGER NOT NULL,
      time_label TEXT NOT NULL,
      time_start REAL,
      time_end REAL,
      time_mid REAL,
      duration REAL,
      PRIMARY KEY (time_id)
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS dim_locations (
      idx INTEGER,
      location TEXT NOT NULL,
      notes TEXT,
      location_id INTEGER NOT NULL,
      location_iso3code TEXT,
      location_iso2code TEXT,
      sdmx_code TEXT,
      loc_type INTEGER,
      loc_type_name TEXT,
      parent_id INTEGER,
      world_id INTEGER,
      subreg_id INTEGER,
      subreg_name TEXT,
      georeg_id INTEGER,
      georeg_name TEXT,
      more_dev INTEGER,
      less_dev INTEGER,
      least_dev INTEGER,
      o_less_dev INTEGER,
      less_dev_excluding_china INTEGER,
      PRIMARY KEY (location_id)
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS dim_ages (
      age_id INTEGER NOT NULL,
      age TEXT NOT NULL,
      age_start INTEGER,
      age_end INTEGER,
      PRIMARY KEY (age_id)
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS dim_sexes (
      sex_id INTEGER NOT NULL,
      sex TEXT NOT NULL,
      sex_sort_order INTEGER NOT NULL,
      PRIMARY KEY (sex_id)
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS dim_variants (
      variant_id INTEGER NOT NULL,
      name TEXT NOT NULL,
      label TEXT,
      short_name TEXT,
      uncertainty INTEGER,
      prediction_interval_label TEXT,
      uncertainty_z_index INTEGER,
      sort_order INTEGER,
      description TEXT,
      PRIMARY KEY (variant_id)
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS dim_categories (
      category_id INTEGER NOT NULL,
      category_name TEXT NOT NULL,
      category_sort_order INTEGER,
      PRIMARY KEY (category_id)
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS dim_estimate_types (
      estimate_type_id INTEGER NOT NULL,
      estimate_type_name TEXT NOT NULL,
      PRIMARY KEY (estimate_type_id)
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS dim_estimate_methods (
      estimate_method_id INTEGER NOT NULL,
      estimate_method_name TEXT NOT NULL,
      PRIMARY KEY (estimate_method_id)
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS dim_indicators (
      indicator_id INTEGER NOT NULL,
      indicator TEXT NOT NULL,
      short_name TEXT,
      description TEXT,
      indicator_display_name TEXT,
      dim_age INTEGER,
      dim_sex INTEGER,
      dim_variant INTEGER,
      dim_category INTEGER,
      default_age_id INTEGER,
      default_sex_id INTEGER,
      default_variant_id INTEGER,
      default_category_id INTEGER,
      variable_type TEXT,
      value_type TEXT,
      unit_scaling REAL,
      precision INTEGER,
      is_thousand_separator_space INTEGER,
      format_string TEXT,
      unit_short_label TEXT,
      unit_long_label TEXT,
      n_classes_default INTEGER,
      download_file_name TEXT,
      topic_id INTEGER,
      indicator_source_id INTEGER,
      PRIMARY KEY (indicator_id),
      FOREIGN KEY (topic_id) REFERENCES dim_topics(topic_id),
      FOREIGN KEY (indicator_source_id) REFERENCES dim_sources(source_id),
      FOREIGN KEY (default_age_id) REFERENCES dim_ages(age_id),
      FOREIGN KEY (default_sex_id) REFERENCES dim_sexes(sex_id),
      FOREIGN KEY (default_variant_id) REFERENCES dim_variants(variant_id),
      FOREIGN KEY (default_category_id) REFERENCES dim_categories(category_id)
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS fact_population (
      fact_id INTEGER PRIMARY KEY AUTOINCREMENT,
      location_id INTEGER NOT NULL,
      indicator_id INTEGER NOT NULL,
      source_id INTEGER NOT NULL,
      variant_id INTEGER NOT NULL,
      time_id INTEGER NOT NULL,
      category_id INTEGER NOT NULL,
      estimate_type_id INTEGER NOT NULL,
      estimate_method_id INTEGER NOT NULL,
      sex_id INTEGER NOT NULL,
      age_id INTEGER NOT NULL,
      value REAL NOT NULL,
      FOREIGN KEY (location_id) REFERENCES dim_locations(location_id),
      FOREIGN KEY (indicator_id) REFERENCES dim_indicators(indicator_id),
      FOREIGN KEY (source_id) REFERENCES dim_sources(source_id),
      FOREIGN KEY (variant_id) REFERENCES dim_variants(variant_id),
      FOREIGN KEY (time_id) REFERENCES dim_times(time_id),
      FOREIGN KEY (category_id) REFERENCES dim_categories(category_id),
      FOREIGN KEY (estimate_type_id) REFERENCES dim_estimate_types(estimate_type_id),
      FOREIGN KEY (estimate_method_id) REFERENCES dim_estimate_methods(estimate_method_id),
      FOREIGN KEY (sex_id) REFERENCES dim_sexes(sex_id),
      FOREIGN KEY (age_id) REFERENCES dim_ages(age_id),
      UNIQUE (
        location_id,
        indicator_id,
        source_id,
        variant_id,
        time_id,
        category_id,
        estimate_type_id,
        estimate_method_id,
        sex_id,
        age_id
      )
    );"
  )
  
  dbExecute(
    db,
    "CREATE TABLE IF NOT EXISTS ingestion_log (
      ingestion_id INTEGER PRIMARY KEY AUTOINCREMENT,
      table_name TEXT NOT NULL,
      endpoint TEXT,
      request_time TEXT NOT NULL,
      load_time TEXT,
      status TEXT NOT NULL,
      n_rows_raw INTEGER,
      n_rows_inserted INTEGER,
      script_name TEXT,
      query_params TEXT,
      response_hash TEXT,
      notes TEXT,
      location_id INTEGER,
      time_id INTEGER,
      indicator_id INTEGER
    );"
  )
  tables <- dbListTables(db)
  dbDisconnect(db)
  return(tables)
}

create_newdb(db_path, overwrite = TRUE)

1.6 Populando dimensões

log_ingestion <- function(db,
                          table_name,
                          endpoint = NA_character_,
                          request_time = Sys.time(),
                          load_time = Sys.time(),
                          status = "success",
                          n_rows_raw = NA_integer_,
                          n_rows_inserted = NA_integer_,
                          script_name = NA_character_,
                          query_params = NA_character_,
                          response_hash = NA_character_,
                          notes = NA_character_,
                          location_id = NA_integer_,
                          time_id = NA_integer_,
                          indicator_id = NA_integer_) {
  
  log_entry <- tibble::tibble(
    table_name = table_name,
    endpoint = endpoint,
    request_time = as.character(request_time),
    load_time = as.character(load_time),
    status = status,
    n_rows_raw = n_rows_raw,
    n_rows_inserted = n_rows_inserted,
    script_name = script_name,
    query_params = query_params,
    response_hash = response_hash,
    notes = notes,
    location_id = location_id,
    time_id = time_id,
    indicator_id = indicator_id
  )
  
  DBI::dbWriteTable(
    conn = db,
    name = "ingestion_log",
    value = log_entry,
    append = TRUE
  )
}

1.6.1 dim_indicators

library(digest)
library(dplyr)
library(readr)
library(here)

HEADER <- c("Authorization" = Sys.getenv("WPP_ONU_BEARER"))
endpoint <- "https://population.un.org/dataportalapi/api/v1/indicators"
request_time <- Sys.time()

raw_json <- RCurl::getURL(
  endpoint,
  .opts = list(httpheader = HEADER, followlocation = TRUE)
)

response_hash <- digest(raw_json, algo = "sha256")

indicators =  jsonlite::fromJSON(raw_json)$data |>
  rename(
    indicator_id = id,
    indicator = name,
    indicator_display_name = displayName,
    indicator_source_id = sourceId
  ) |>
  select(
    -sourceName, -sourceYear, -sourceStartYear, -sourceEndYear,
    -sourceCitation, -sourceUrl, -topicName, -topicShortName
  )
names(indicators) <- snakecase::to_snake_case(names(indicators))

db <- dbConnect(RSQLite::SQLite(), DB_PATH)

dbWriteTable(conn = db,
             name = "dim_indicators",
             value = indicators,
             append = TRUE)

# registra processo
log_ingestion(
  db = db,
  table_name = "dim_indicators",
  endpoint = endpoint,
  request_time = request_time,
  load_time = Sys.time(),
  status = "success",
  n_rows_raw = nrow(indicators),
  n_rows_inserted = nrow(indicators),
  script_name = "create_database.mdR chunk dim_indicators",
  response_hash = response_hash,
  notes = "Carga inicial de indicadores"
)

dbDisconnect(db)

1.6.2 dim_locations

destfile_onu <- here::here("data","pop", "WPP2024_F01_LOCATIONS.xlsx")

endpoint <- 'https://population.un.org/wpp/assets/Excel%20Files/4_Metadata/WPP2024_F01_LOCATIONS.xlsx'

request_time <- Sys.time()

# download se necessário
# download.file(endpoint, destfile = destfile_onu, mode = "wb")
response_hash <- digest::digest(file = destfile_onu, algo = "sha256")

locations = readxl::read_xlsx(destfile_onu, sheet = 'DB')
names(locations) = snakecase::to_snake_case(names(locations))

db <- dbConnect(RSQLite::SQLite(), DB_PATH)
locations = locations %>%
  rename(idx= index,
         location_id = loc_id,
         location_iso2code = iso_2_code,
         location_iso3code = iso_3_code,
         subreg_id = sub_reg_id,
         subreg_name = sub_reg_name,
         georeg_id = geo_reg_id,
         georeg_name = geo_reg_name) %>%
  select(all_of(dbListFields(db, 'dim_locations')))


dbWriteTable(conn = db,
             name = "dim_locations",
             value = locations,
             append = TRUE)

log_ingestion(
  db = db,
  table_name = "dim_locations",
  endpoint = endpoint,
  request_time = request_time,
  load_time = Sys.time(),
  status = "success",
  n_rows_raw = nrow(locations),
  n_rows_inserted = nrow(locations),
  script_name = "create_database.mdR chunk dim_locations",
  response_hash = response_hash,
  notes = "Carga inicial dos locais"
)

dbDisconnect(db)

1.6.3 dim_times

endpoint <- 'https://population.un.org/dataportalapi/api/v1/dimensions/times/None'
request_time <- Sys.time()

raw_json <- RCurl::getURL(
  endpoint,
  .opts = list(httpheader = HEADER, followlocation = TRUE)
)

response_hash <- digest(raw_json, algo = "sha256")

times =  jsonlite::fromJSON(raw_json) |>
  rename(time_id = id,
         time_label = label)
names(times) = snakecase::to_snake_case(names(times))

db <- dbConnect(RSQLite::SQLite(), DB_PATH)

dbWriteTable(conn = db,
             name = "dim_times",
             value = times,
             append = TRUE)

# registra processo
log_ingestion(
  db = db,
  table_name = "dim_times",
  endpoint = endpoint,
  request_time = request_time,
  load_time = Sys.time(),
  status = "success",
  n_rows_raw = nrow(times),
  n_rows_inserted = nrow(times),
  script_name = "create_database.mdR chunk dim_times",
  response_hash = response_hash,
  notes = "Carga inicial dos períodos"
)

dbDisconnect(db)

1.6.4 dim_sources

endpoint <- 'https://population.un.org/dataportalapi/api/v1/sources'
request_time <- Sys.time()

raw_json <- RCurl::getURL(
  endpoint,
  .opts = list(httpheader = HEADER, followlocation = TRUE)
)

response_hash <- digest(raw_json, algo = "sha256")

sources =  jsonlite::fromJSON(raw_json)$data |>
  rename(sourceId = id)
names(sources) = snakecase::to_snake_case(names(sources))

db <- dbConnect(RSQLite::SQLite(), DB_PATH)


dbWriteTable(conn = db,
             name = "dim_sources",
             value = sources,
             append = TRUE)

# registra processo
log_ingestion(
  db = db,
  table_name = "dim_sources",
  endpoint = endpoint,
  request_time = request_time,
  load_time = Sys.time(),
  status = "success",
  n_rows_raw = nrow(sources),
  n_rows_inserted = nrow(sources),
  script_name = "create_database.mdR chunk dim_sources",
  response_hash = response_hash,
  notes = "Carga inicial das fontes"
)

dbDisconnect(db)

1.6.5 dim_variants

#create_newdb(DB_PATH)
endpoint <- 'https://population.un.org/dataportalapi/api/v1/dimensions/variants'
request_time <- Sys.time()

raw_json <- RCurl::getURL(
  endpoint,
  .opts = list(httpheader = HEADER, followlocation = TRUE)
)

response_hash <- digest(raw_json, algo = "sha256")

variants =  jsonlite::fromJSON(raw_json) |>
  rename(variantId = id)

names(variants) = snakecase::to_snake_case(names(variants))

db <- dbConnect(RSQLite::SQLite(), DB_PATH)

dbWriteTable(conn = db,
             name = "dim_variants",
             value = variants,
             append = TRUE)

# registra processo
log_ingestion(
  db = db,
  table_name = "dim_variants",
  endpoint = endpoint,
  request_time = request_time,
  load_time = Sys.time(),
  status = "success",
  n_rows_raw = nrow(variants),
  n_rows_inserted = nrow(variants),
  script_name = "create_database.mdR chunk dim_variants",
  response_hash = response_hash,
  notes = "Carga inicial das variantes"
)

dbDisconnect(db)

1.6.6 dim_topics

#create_newdb(DB_PATH)
endpoint <- 'https://population.un.org/dataportalapi/api/v1/topics'
request_time <- Sys.time()

raw_json <- RCurl::getURL(
  endpoint,
  .opts = list(httpheader = HEADER, followlocation = TRUE)
)

response_hash <- digest(raw_json, algo = "sha256")

topics =  jsonlite::fromJSON(raw_json)$data |>
  rename(topicId = id)

names(topics) = snakecase::to_snake_case(names(topics))

db <- dbConnect(RSQLite::SQLite(), DB_PATH)

dbWriteTable(conn = db,
             name = "dim_topics",
             value = topics,
             append = TRUE)

# registra processo
log_ingestion(
  db = db,
  table_name = "dim_variants",
  endpoint = endpoint,
  request_time = request_time,
  load_time = Sys.time(),
  status = "success",
  n_rows_raw = nrow(topics),
  n_rows_inserted = nrow(topics),
  script_name = "create_database.mdR chunk dim_variants",
  response_hash = response_hash,
  notes = "Carga inicial das variantes"
)

dbDisconnect(db)

1.6.7 dim_ages

#create_newdb(DB_PATH)
endpoint <- paste0('https://population.un.org/dataportalapi/api/v1/metadata/ages/',
                   paste(indicators$indicator_id, collapse = ","))
request_time <- Sys.time()

raw_json <- RCurl::getURL(
  endpoint,
  .opts = list(httpheader = HEADER, followlocation = TRUE)
)

response_hash <- digest(raw_json, algo = "sha256")

ages =  jsonlite::fromJSON(raw_json) |>
  select(-indicatorId, -indicatorName)|>
  distinct(ageId, .keep_all = TRUE)

names(ages) = snakecase::to_snake_case(names(ages))

db <- dbConnect(RSQLite::SQLite(), DB_PATH)

dbWriteTable(conn = db,
             name = "dim_ages",
             value = ages,
             append = TRUE)

# registra processo
log_ingestion(
  db = db,
  table_name = "dim_ages",
  endpoint = endpoint,
  request_time = request_time,
  load_time = Sys.time(),
  status = "success",
  n_rows_raw = nrow(ages),
  n_rows_inserted = nrow(ages),
  script_name = "create_database.mdR chunk dim_ages",
  response_hash = response_hash,
  notes = "Carga inicial das idades"
)

dbDisconnect(db)

1.6.8 dim_sexes

#create_newdb(DB_PATH)
endpoint <- 'Tibble gerada Manualmente (muito mais simples)'
request_time <- Sys.time()

sexes =  tibble(sexId = 1:3,
                sex = c('Male','Female','Both sexes'),
                sexSortOrder = c(3,1,2))

names(sexes) = snakecase::to_snake_case(names(sexes))

response_hash <- digest(sexes, algo = "sha256")

db <- dbConnect(RSQLite::SQLite(), DB_PATH)
dbWriteTable(conn = db,
             name = "dim_sexes",
             value = sexes,
             append = TRUE)

# registra processo
log_ingestion(
  db = db,
  table_name = "dim_sexes",
  endpoint = endpoint,
  request_time = request_time,
  load_time = Sys.time(),
  status = "success",
  n_rows_raw = nrow(sexes),
  n_rows_inserted = nrow(sexes),
  script_name = "create_database.mdR chunk dim_sexes",
  response_hash = response_hash,
  notes = "Carga inicial dos sexos"
)

dbDisconnect(db)

1.6.9 dim_categories

#create_newdb(DB_PATH)
endpoint <- paste0('https://population.un.org/dataportalapi/api/v1/metadata/categories/',
                   paste(indicators$indicator_id, collapse = ","))
request_time <- Sys.time()

raw_json <- RCurl::getURL(
  endpoint,
  .opts = list(httpheader = HEADER, followlocation = TRUE)
)

response_hash <- digest(raw_json, algo = "sha256")

categories =  jsonlite::fromJSON(raw_json) |>
  select(-indicatorId, -indicatorName)|>
  distinct(categoryId, .keep_all = TRUE)

names(categories) = snakecase::to_snake_case(names(categories))

db <- dbConnect(RSQLite::SQLite(), DB_PATH)
dbWriteTable(conn = db,
             name = "dim_categories",
             value = categories,
             append = TRUE)

# registra processo
log_ingestion(
  db = db,
  table_name = "dim_categories",
  endpoint = endpoint,
  request_time = request_time,
  load_time = Sys.time(),
  status = "success",
  n_rows_raw = nrow(categories),
  n_rows_inserted = nrow(categories),
  script_name = "create_database.mdR chunk dim_categories",
  response_hash = response_hash,
  notes = "Carga inicial das categorias"
)

dbDisconnect(db)

1.7 Funções auxiliares de entrada do banco de dados

BASE_URL <- "https://population.un.org/dataportalapi/api/v1"
HEADER <- c("Authorization" = Sys.getenv("WPP_ONU_BEARER"))
DB_PATH <- here::here("data", "pop", "population_data.sqlite")
source(here::here('R/download_pages_WPP.R'))
# SQL simples com essas constantes aí em cima
sql_basic = function(query){
  con <- dbConnect(RSQLite::SQLite(), DB_PATH)
  df = dbGetQuery(con, query)
  dbDisconnect(con)
  return(df)
}

get_dim_value <- function(table_name, id_column, id_value, return_column, db_path = DB_PATH) {
  db <- DBI::dbConnect(RSQLite::SQLite(), db_path)
  on.exit(DBI::dbDisconnect(db), add = TRUE)
  
  sql <- paste0(
    "SELECT ", return_column,
    " FROM ", table_name,
    " WHERE ", id_column, " = ", id_value
  )
  
  result <- DBI::dbGetQuery(db, sql)
  
  if (nrow(result) == 0) {
    return(NA)
  }
  
  result[[return_column]][1]
}

# Função para processar dados de um local e ano específico
process_location_data <- function(location_id, time_id, indicator_id) {
  time_label <- get_dim_value("dim_times", "time_id", time_id, "time_label")
  
  target <- paste0(BASE_URL,
                   "/data/indicators/", indicator_id,
                   "/locations/", location_id,
                   "/start/", time_label, "/end/", time_label,
                   "?pagingInHeader=false&format=json")
  # Fazer o download e processar os dados
  download_pages_WPP(target, HEADER)
}

insert_if_not_exists <- function(conn, table_name, data, key_columns) {
  existing_data <- dbReadTable(conn, table_name)
  new_data <- dplyr::anti_join(data, existing_data, by = key_columns)
  
  if (nrow(new_data) > 0) {
    dbWriteTable(conn, table_name, new_data, append = TRUE, row.names = FALSE)
  }
}

check_missing_keys <- function(db, table_name, key_column, values) {
  values <- unique(values[!is.na(values)])
  
  if (length(values) == 0) {
    return(integer(0))
  }
  
  sql <- paste0(
    "SELECT ", key_column, " FROM ", table_name,
    " WHERE ", key_column, " IN (", paste(values, collapse = ","), ")"
  )
  
  existing <- DBI::dbGetQuery(db, sql)[[1]]
  setdiff(values, existing)
}

save_dimensions <- function(data, db) {
  
  # -------------------------
  # 1. Validar dimensões pré-carregadas
  # -------------------------
  missing_sources <- check_missing_keys(db, "dim_sources", "source_id", data$sourceId)
  missing_variants <- check_missing_keys(db, "dim_variants", "variant_id", data$variantId)
  missing_times <- check_missing_keys(db, "dim_times", "time_id", data$timeId)
  missing_sexes <- check_missing_keys(db, "dim_sexes", "sex_id", data$sexId)
  missing_ages <- check_missing_keys(db, "dim_ages", "age_id", data$ageId)
  missing_categories <- check_missing_keys(db, "dim_categories", "category_id", data$categoryId)
  
  if (length(missing_sources) > 0) {
    stop("source_id ausente em dim_sources: ", paste(missing_sources, collapse = ", "))
  }
  if (length(missing_variants) > 0) {
    stop("variant_id ausente em dim_variants: ", paste(missing_variants, collapse = ", "))
  }
  if (length(missing_times) > 0) {
    stop("time_id ausente em dim_times: ", paste(missing_times, collapse = ", "))
  }
  if (length(missing_sexes) > 0) {
    stop("sex_id ausente em dim_sexes: ", paste(missing_sexes, collapse = ", "))
  }
  if (length(missing_ages) > 0) {
    stop("age_id ausente em dim_ages: ", paste(missing_ages, collapse = ", "))
  }
  if (length(missing_categories) > 0) {
    stop("category_id ausente em dim_categories: ", paste(missing_categories, collapse = ", "))
  }
  
  # -------------------------
  # 2. Inserir dimensões residuais
  # -------------------------
  dim_estimate_types <- data |>
    dplyr::select(
      estimate_type_id = estimateTypeId,
      estimate_type_name = estimateType
    ) |>
    dplyr::distinct()
  
  insert_if_not_exists(
    db,
    "dim_estimate_types",
    dim_estimate_types,
    key_columns = "estimate_type_id"
  )
  
  dim_estimate_methods <- data |>
    dplyr::select(
      estimate_method_id = estimateMethodId,
      estimate_method_name = estimateMethod
    ) |>
    dplyr::distinct()
  
  insert_if_not_exists(
    db,
    "dim_estimate_methods",
    dim_estimate_methods,
    key_columns = "estimate_method_id"
  )
}

save_fact_population <- function(data, db) {
  fact_population <- data |>
    select(
      locationId,
      indicatorId,
      sourceId,
      variantId,
      timeId,
      categoryId,
      estimateTypeId,
      estimateMethodId,
      sexId,
      ageId,
      value
    )|>
  dplyr::rename_with(snakecase::to_snake_case)
  
  dbWriteTable(
    conn = db,
    name = "fact_population",
    value = fact_population,
    append = TRUE,
    row.names = FALSE
  )
}

save_processing_log <- function(location_id,
                                time_id,
                                indicatorId,
                                db,
                                endpoint = NA_character_,
                                request_time = Sys.time(),
                                response_hash = NA_character_,
                                status = "success") {
  
  log_ingestion(
    db = db,
    table_name = "fact_population",
    endpoint = endpoint,
    request_time = request_time,
    load_time = Sys.time(),
    status = status,
    n_rows_raw = NA_integer_,
    n_rows_inserted = NA_integer_,
    script_name = "main_insert",
    query_params = paste0(
      "locationId=", location_id,
      ";timeId=", time_id,
      ";indicatorId=", indicator_id
    ),
    response_hash = response_hash,
    notes = "Processamento de recorte analítico",
    location_id = location_id,
    time_id = time_id,
    indicator_id = indicator_id
  )
}

check_processing_log <- function(time_id, location_id, indicator_id, db) {
  sql_query <- paste0(
    "SELECT COUNT(*) AS count 
     FROM ingestion_log 
     WHERE table_name = 'fact_population'
       AND time_id = ", time_id,
    " AND location_id = ", location_id,
    " AND indicator_id = ", indicator_id,
    " AND status = 'success'"
  )
  
  query <- DBI::dbGetQuery(db, sql_query)
  
  if (is.null(query$count) || is.na(query$count)) {
    return(FALSE)
  }
  
  query$count > 0
}

insert_all_data <- function(locations, times, indicators, db) {
  for (t in seq_len(nrow(times))) {
    time_label <- times[t, ]$time_label
    time_id <- times[t, ]$time_id
    
    for (l in seq_len(nrow(locations))) {
      location <- locations[l, ]$location
      location_id <- locations[l, ]$location_id
      
      for (i in seq_len(nrow(indicators))) {
        indicator <- indicators[i, ]$indicator
        indicator_id <- indicators[i, ]$indicator_id
        
        cat(
          paste0(
            "\nProcessing Year: ", time_label, " (", time_id, ")",
            " - Location: ", location, " (", location_id, ")",
            " - Indicator: ", indicator, " (", indicator_id, ")\n"
          )
        )
        
        if (check_processing_log(time_id, location_id, indicator_id, db)) {
          cat(
            paste0(
              ">>>>> Skipping: Year: ", time_label, " (", time_id, ")",
              " - Location: ", location, " (", location_id, ")",
              " - Indicator: ", indicator, " (", indicator_id, ")\n"
            )
          )
          next
        }
        
        wpp_raw <- process_location_data(location_id, time_id, indicator_id)
        save_dimensions(wpp_raw, db)
        save_fact_population(wpp_raw, db)
        save_processing_log(location_id, time_id, indicator_id, db)
      }
    }
  }
}

# ----
#EXECUÇÃO DO PROGRAMA
main_insert <- function(locations, times, indicators) {
  db <- dbConnect(RSQLite::SQLite(), DB_PATH)
  
  insert_all_data(locations, times, indicators, db)
  
  dbDisconnect(db)
  cat("\nProcessing completed successfully!\n")
}

1.8 Carga inicial para testes

Location

• Mundo

indicador

• população total
• população por idade

Períodos

• 1950, 2000, 2050 e 2100

source(here('R/download_pages_WPP.R'))

locations <- sql_basic("SELECT * FROM dim_locations WHERE location_id IN (900)")
indicators = sql_basic("SELECT * FROM dim_indicators WHERE indicator_id IN (46,49)")
years <- c(1950, 2000, 2050, 2100)
query = paste0("SELECT * FROM dim_times WHERE time_label IN ('",
               paste(years, collapse = "', '"), "')")
times <- sql_basic(query)
main_insert(locations, times, indicators)

#knitr::purl('data/pop/create_database.Rmd', output = 'data/pop/create_database.r')

1.9 Carga para os alunos

1.10 9. Próximos passos

Referências

UN-DESA. World Population Prospects 2024. [S. l.]: https://population.un.org/wpp/, 2024. Accessed on: 30 May 2023.