Casos do Dia a Dia – Correios – Atualizar Base CEP – Parte 2 – Várias Tabelas

Fala galerinha,

Nesse post vamos ver a segunda solução para fazer a Atualização da Base de CEP dos Correios.  

Apenas relembrando:

  • Parte 1: O resultado da Rotina será apenas uma tabela com todas as informações do CEP;
  • Parte 2: O resultado da Rotina serão várias tabelas, separando as informações por cidade, bairro, CEP, etc.

Então bora ver o que tem de diferente nessa segunda solução!  #gogogo


YouTube – Vídeo:

Segue abaixo um vídeo que gravei no YouTube mostrando na prática a execução dos scripts desse post:

https://www.youtube.com/watch?v=pCy1F__U9iM&t


Casos do Dia a Dia – Correios – Atualizar Base CEP – Parte 1 – Uma Tabela:

Se você ainda não viu, recomendo fortemente que leia a Parte 1 desse Post, pois aqui vou focar apenas nas diferenças entre as duas soluções OK.

https://luizlima.net/casos-do-dia-a-dia-correios-atualizar-base-cep-parte-1-uma-tabela/


Casos do Dia a Dia – Correios – Atualizar Base CEP – Parte 2 – Várias Tabelas:

Agora sim vamos começar a Parte 2! (Para de enrolar Luiz!!!)

Já adianto que a forma de executar a atualização será exatamente a mesma, você irá executar apenas uma procedure e informar o caminho da pasta dos arquivos (mais detalhes sobre isso na Parte 1):

O que irá mudar aqui será o conteúdo da procedure que irei explicar mais pra frente.

Uma grande mudança em relação a Parte 1 é que dessa vez iremos ter várias tabelas como resultado ao invés de apenas uma. Nessa solução normalizamos algumas informações criando tabelas separadas e relacionamentos entre elas.

Segue abaixo um Diagrama das Tabelas que gerei pelo próprio SSMS (SQL Server Management Studio) para facilitar o entendimento:


Arquivos TXT:

Aqui vamos utilizar alguns arquivos diferentes da Parte 1, pois dessa vez nós vamos criar várias tabelas ao invés de apenas uma. Esses arquivos serão utilizados dentro da procedure e cada um deles possui o seu próprio layout também. Segue abaixo todos os arquivos que serão utilizados:

DNE_GU_UF_LOGRADOUROS.TXT (UM PARA CADA ESTADO)

DNE_GU_GRANDES_USUARIOS.TXT

DNE_GU_CAIXAS_POSTAIS_COMUNIT.TXT

DNE_GU_UNIDADES_OPERACIONAIS.TXT

DNE_GU_LOCALIDADES.TXT

DNE_GU_BAIRROS.TXT

DNE_GU_UNIDADES_FEDERACAO.TXT

DNE_GU_PAISES.TXT

DNE_GU_TIPOS_LOGRADOURO.TXT

DNE_GU_TITULOS_PATENTES.TXT


Layout Arquivos:

Também refiz o Mapeamento dos Arquivos TXT x Tabelas do Banco de Dados, adaptando para essa segunda solução e salvei em uma planilha (arquivo “Mapeamento Tabela CEP x Layout – Parte 2.xlsx”).

Repare que dessa vez vamos ter mais abas, cada uma especificando o layout de um tipo de arquivo:


Scripts Importação – Procedure “[dbo].[stpAtualizacao_CEP]”:

Agora vamos para a implementação do arquivo de atualização!

OBS: Disponibilizei o LINK para DOWNLOAD dos arquivos no final do post OK.

Novamente, vou criar uma base separada chamada “CEP” (ou o nome que você desejar, mas depois será necessário substituir nos próximos passos/arquivos também OK).

Arquivo: “01 – Cria Base [CEP].sql”

OBS: O Ponto de Atenção aqui é no caminho dos arquivos da base.

Depois disso, vamos criar a procedure que será utilizada depois para fazer a Atualização. Basta dar um F5 no arquivo abaixo:

Arquivo: “02 – Cria Procedure [dbo].[stpAtualizacao_CEP].sql”

A procedure segue basicamente a mesma ideia da Parte 1. A diferença é que ao invés de ter apenas uma tabela como resultado, dessa vez teremos várias. Segue abaixo o nome de cada uma delas:

Cep

Localidade

Bairro

Estado

Pais

Tipo_Logradouro

Titulo_Patente


Tabelas Criadas pela rotina:

Aqui vou mostrar rapidamente cada uma das tabelas que serão criadas pela rotina:

Tabela “Cep”:

Dessa vez a tabela “cep_new” será chamada apenas de “cep” e a estrutura dela também foi alterada. Agora ela possui apenas o “Cd_Localidade” e “Cd_Bairro” que estão relacionados com as tabelas “Localidade” e “Bairro”, respectivamente. Essa será a tabela principal da nossa estrutura OK.

“Pô Luiz, mas agora como que eu vou conseguir trazer o endereço completo???”

Agora você vai precisar fazer alguns JOINs para retornar o ENDEREÇO COMPLETO. Segue abaixo um exemplo:

VIEW “vwCep”:

DICA: Para não ter que digitar esse SELECT inteiro toda vez que precisar do ENDEREÇO COMPLETO, aqui você também poderia criar uma VIEW (Ex: vwCep) para retornar as mesmas informações, mas com um código muito mais simples. Além disso você também consegue filtrar os campos normalmente no WHERE beleza. =)

Tabela “Localidade”:

Essa tabela contêm todas as Localidades (ou cidades).

Tabela “Bairro”:

Essa tabela contêm todos os Bairros. Repare que aqui também temos um campo “Cd_Localidade” que se relaciona com a tabela “Localidade”.

Estado:

Nessa tabela temos todos os estados do Brasil.

País:

Essa tabela possui todos os países, inclusive com o nome em inglês e francês.

Tipo_Logradouro:

Essa tabela têm todos os Tipos de Logradouro. Ex: Rua, Avenida, Travessa, etc…

Titulo_Patente:

Essa tabela contêm todos os Títulos de Patentes. Ex: Coronel, Senador, Presidente, etc…


Cep Geral (Localidades):

Em algumas localidades (“cidades”) os Ceps não são individuais, ou seja, um único cep irá representar vários endereços. Isso pode acontecer em cidades pequenas ou no interior.

Segue abaixo um exemplo do site dos Correios. Aqui os campos Logradouro e Bairro ficam em branco.

Aqui temos outra diferença em relação à Parte 1, pois dessa vez as Localidades estão em uma tabela separada chamada “Localidade”. O CEP Geral serão os registros de Localidade que possuem o campo “Nr_Cep” preenchido, conforme o SELECT abaixo:

A Recomendação dos Correios nesses casos é que você deixe o campo do endereço aberto para edição na aplicação/sistema, pois como falei anteriormente, um mesmo CEP Geral será utilizado para vários endereços distintos.


Resumindo:

De forma resumida, você deve fazer o seguinte:

OBS: Antes de executar os scripts abaixo, validar os parâmetros que precisam ser alterados (caminho dos arquivos).

  1. Executar o arquivo “01 – Cria Base [CEP].sql”.
  2. Executar o arquivo “02 – Cria Procedure [dbo].[stpAtualizacao_CEP].sql”.
  3. Executar o arquivo “03 – Executa Procedure [dbo].[stpAtualizacao_CEP].sql”. Nos meus testes o tempo de execução foi de menos de 30 segundos! Bem rápido!!!
  4. Executar o arquivo “04 – Cria VIEW – [dbo].[vwCep].sql”. Esse é OPCIONAL, você cria só se quiser utilizar a VIEW.

Pronto! Depois desses passos vamos ter as tabelas da base CEP atualizadas!

Tamanho Database “Cep”:

Na Parte 1 a tabela “cep_new” ficou com 150 MB.

Na Parte 2 o tamanho total da base “CEP” (com todas as tabelas que mostrei anteriormente) ficou com 131 MB. Aqui tivemos uma redução pois dessa vez nós normalizamos os dados das Localidades e Bairros que antes ficavam repetidos na tabela “cep_new”.


Atualizando a Base de Produção:

Como falei anteriormente, o resultado da Rotina de Atualização do CEP serão várias tabelas. Como não sei como cada ambiente utiliza a tabela de CEP, dessa vez optei por gerar todas as tabelas em uma base separada para que você mesmo possa definir a melhor forma de atualizar o seu ambiente.

Segue abaixo algumas sugestões:

  • OBS: Recomendo que você agende uma Janela de Manutenção para fazer essa alteração no horário de menor movimento no seu ambiente.

1) Se você já utiliza uma base separada para o CEP (somente para consultas), você poderia renomear a base atual e trocar com a base nova com os dados atualizados.

2) Você pode também deletar os dados das suas tabelas atuais e importar novamente com os dados atualizados dessa nova base.

Essas são apenas algumas sugestões. Fique à vontade para fazer da melhor maneira para o seu ambiente.


Parte 2 – Vantagens x Desvantagens:

Por fim, vou listar aqui algumas vantagens e desvantagens dessa segunda solução:

Vantagens:

O que mais gosto nessa segunda solução é ter os dados separados por tabelas, pois dessa forma conseguimos listar facilmente todas as cidades ou bairros por exemplo.

Como podemos ver, essa solução normalizada também ocupa menos espaço do que na Parte 1.

Desvantagens:

A desvantagem é que para trazer o endereço completo vamos precisar fazer JOINs com mais de uma tabela, mas acredito que possamos resolver isso utilizando a VIEW “vwCep”.


Download Arquivos – Atualização CEP – Parte 2 – Várias Tabelas:

Segue abaixo o link para baixar os arquivos:

https://github.com/luizvitorf/SQLServer/tree/master/Scripts/CEP/Atualiza%C3%A7%C3%A3o%20CEP/Parte%202%20-%20V%C3%A1rias%20Tabelas


Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

12 comentários em “Casos do Dia a Dia – Correios – Atualizar Base CEP – Parte 2 – Várias Tabelas

  1. Hercules Responder

    Pergunta, você já fez algo também com os arquivos de atualizações que o correio disponibiliza também? Eles tem um layout diferente da bse completa, não tem?

    • Luiz Lima Autor do postResponder

      Fala Hercules, blz?

      Os trabalhos que já fiz foram utilizando exatamente esses arquivos que mostrei, não consegui entender muito bem o que você quis dizer com “base completa”.

      Abraço,
      Luiz Vitor

  2. Hercules Responder

    Opa Luiz, bem e vc?

    Os correios vendem o DNE Básico, que são esses arquivos que você importou.
    Mas também vendem uma Renovação (anual ou semestral), que eles disponibilizam mensalmente um conjunto de arquivos com essas atualizações, e esses arquivos possuem um layout um pouco diferente dos arquivos do DNE básico, neles vem só as diferenças, CEPS novos, atualizações de CEPS genéricos…
    espero que tenha me explicado…rs

    Abraço

    • Luiz Lima Autor do postResponder

      Tudo certo amigo. Agora sim consegui entender a sua dúvida. Pelo visto só tive contato com os arquivos do DNE Básico então, ainda não cheguei a ver esses outros arquivos somente com as diferenças. No meu caso, eu sempre excluía os dados que estavam nas minhas tabelas de produção e utilizava os arquivos do DNE Básico para importar TODOS OS DADOS novamente. Esse procedimento era a cada X meses, não tinha uma frequência exata, era quando o pessoal disponibilizava o arquivo para a área de TI fazer a atualização.

      No seu caso, teria que entender como funciona esse outro layout e fazer algumas adaptações na Rotina da Atualização do CEP.

      Abraço,
      Luiz Vitor

  3. Alysson Responder

    Luiz, boa tarde!

    Eu tenho acesso aos arquivos do DNE Básico aqui na empresa, porém eu utilizo a pasta com os arquivos do tipo Delimitado, você tem o passo a passo para importar os arquivos do tipo Delimitado.
    Estou tentando utilizar o seu codigo como referência, mas não está dando certo principalmente na importação dos arquivos logradouros, retirei as linhas da função RTRIM, e tem uns 30 min que está rodando.
    Eu gostaria de adaptar o codigo dos arquivos fixos aos arquivos delimitados.

    Poderia dar uma dica de qual a alteração deve ser feita para importar os arquivos delimitados para o SQL Server?

    Obrigado!

    Alysson

    • Luiz Lima Autor do postResponder

      Fala Alysson, blz?

      Esse script que compartilhei usa o Layout “Fixo” e é diferente do Layout “Delimitado”, por esse motivo ele não deve funcionar mesmo OK.

      O Layout “Delimitado” que você está utilizando deve ser estudado e avaliado a melhor forma de importação, esse ainda nunca utilizei.

      Dando uma olhada rápida, vi que ele utiliza o caractere “@” para separar as informações em cada linha. Com isso, eu tentaria importar os dados quebrando cada coluna quando encontrasse o caractere “@”. Depois disso você conseguiria ter as informações separadas.

      Espero ter ajudado.

      Abraço,
      Luiz Vitor

  4. Elton Ramos Responder

    Boa noite!
    Existe uma forma de atualizar os arquivos deltas que são os complementos?
    Outra coisa você acha que os correios fornecem eles caso tenha esquecido de baixar algum no prazo do plano?

    • Luiz Lima Autor do postResponder

      Fala Elton, blz?

      Eu consegui criar uma rotina para fazer a atualização com os arquivos delta também, está na fila aqui pra postar um dia, mas sem previsão ainda, pois tenho que revisar algumas coisas.

      Sobre a questão dos arquivos que os Correios disponibilizam, normalmente o cliente que é responsável e eu apenas utilizo para fazer as atualizações. Não tenho mais detalhes sobre isso =(

      Abraço,
      Luiz Vitor

  5. Anonymous Responder

    Pense em uma migração de dados em 2 bases distintas, em que eh necessário enviar os dados do cep. E em nenhuma das pontas o cep é validado. e os dois fornecedores jogando a culpa um no outro.

    Este post veio pra resolver esse caso.. Salvou!!!!!!!!!

    Muito obrigado Luiz!

Deixe uma resposta para Luiz LimaCancelar resposta