ETL para retorna o endereço com coordenadas pelo CEP e número

Este post tem o objetivo de mostrar através de consultas aos sites do correiocontrol e do google maps como retornar os campos de endereço e sua georeferência, passando apenas os parâmetros de CEP e número. Para este post foi utilizado a ferramenta Pentaho Data Integration (PDI), ferramenta que pode ser baixada sem custo na versão da comunidade. Let’s Go!!!


Baixe o PDI no endereço: http://community.pentaho.com/projects/data-integration/. O único pré-requisito para executar o pentaho é ter um JDK instalado na sua máquina. O PDI vem compactado e não precisa de instalação descompacte em uma pasta e execute o arquivo “Spoon.bat” (no Windows) ou “Spoon.sh” (no Linux) localizado no raiz da pasta descompactada.



Irá abrir a tela de login dos repositórios do pentaho, como o objetivo desse post não é demonstrar a utilização de recursos do pentaho, vamos focar apenas no que o post se propõem, portanto clique no botão cancelar para abrir a ferramenta:



Esta é a tela inicial do Pentaho:



Clique no menu "File-> Novo -> Transformação", irá abrir uma nova aba ao lado da aba Bem Vindo, chamada “Transformação 1” de acordo com a tela:



Pronto, uma vez o ambiente ok, “vamu bota pá muê“, como diz o matuto!

Caso não esteja na aba “Design” no canto superior esquerdo, abaixo do menu, clique na mesma, pois é nessa aba que selecionamos os objetos que irão fazer a mágica que queremos. Já na aba “Design” clique na pasta “Input” e arraste o objeto “Generate Rows” para o centro da página assim como na imagem:



Dê dois cliques em cima do objeto arrastado para abrir a tela de configuração deste objeto, nela vamos configurar os campos cep e número; deixe-os com o tipo: String. Na coluna valor adicione um cep e um número válidos de sua escolha. Altere o valor do campo “Limit” para 1. Por questões de organização renomeie o nome do objeto para “Entrada do CEP”, após essas configurações as propriedades desse objeto deve estar igual a imagem:



O próximo objeto é o “Modified Java Script Value” para montar a URL que vai acessar os correios adicionando o parâmetro cep do passo anterior. Para ligar os dois objetos pressione a tecla “Shift”, clique sobre o objeto “Entrada do CEP” e arraste o mouse até o objeto “Modified Java Script Value” enquanto você arrasta o pentaho vai exibindo uma seta. A localização desse objeto é na pasta “Scripting”. Abaixo segue a tela mostrando a localização e a ligação entre os objetos da transformação.



Abra o objeto do Java Script e adicione o seguinte texto:

var cep_correiocontrol = 'http://cep.correiocontrol.com.br/'+cep+'.json';

Este objeto recebe conteúdo javascript, neste caso o utilizamos para adicionar uma variável “cep_correiocontrol” ao fluxo, este passo poderia ser executado de outras maneiras, e com outros objetos, escolhi o objeto javascript por ficar mais simples. Após unir os objetos clique no botão “Get variables” e note que ele adicionará um campo “cep_correiocontrol” do tipo string de acordo com a imagem abaixo, por questão de organização altere o campo “Step Name” para “URL CorreioControl”:



O próximo passo é fazer a chamada HTTP que retorna o JSON com o endereço do CEP, o objeto utilizado para isso é o “HTTP Client” que está localizado na pasta “Lookup”, adicione o mesmo ao fluxo já existente, ligando com o objeto “URL CorreioControl”. De um duplo clique no objeto adicionado para abrir as propriedades do mesmo e marque o check “Accept URL From field”. No campo “URL Field name” selecione o campo do passo anterior “cep_correiocontrol”. Mude para 0 os campos “Connection timeout”, “Socket timeout” e “Connection close wait time”. Caso a sua rede tenha proxy configure-o nos dois últimos campos da tela. Por questões de organização altere o nome do objeto para “Consulta CEP” ao final deste passo devemos ter as seguintes configurações:



Agora vamos tratar o JSON retornado pelo objeto HTTP Client, selecionamos o objeto “JSON Input” dentro da pasta “Input”, adicione e o ligue ao fluxo. Após inseri-lo ao fluxo edite suas propriedades e as deixe de acordo com as imagens.

Na aba “File” marque a opção “Source is defined in a field”, e no campo “Get source from field” escolha o campo “result”:



Na aba “Fields” adicione as linhas:

Name	Path	Type
bairro	$..bairro	String
logradouro	$..logradouro	String
cep	$..cep	String
uf	$..uf	String
localidade	$..localidade	String

A tela deverá ficar como a imagem a baixo:



As demais abas desse objeto não serão utilizadas nesse ETL. Com esse passo nós adicionamos ao fluxo os campos: bairro, logradouro, uf e localidade. Por questões de organização altere o nome do objeto para “Endereço em Json” e conclua clicando em “Ok”. Agora nosso fluxo deve ter essa aparência:



Ok, agora já conseguimos pegar os dados do CEP, falta ainda capturar a georeferência deste cep, vamos lá, adicione outro objeto javascript ao fluxo. Só para lembrar ele está na pasta “Scripting”. Nas propriedades do objeto adicione o seguinte JS (este JS remove caracteres indesejados e prepara o endereço para consultar ao Google Maps)

//Script here
var endereco = logradouro +', '+numero +', ' + localidade;

endereco = removeDiacritics(endereco);
endereco = endereco.replace(/ /gi, '%20');

var url_final = 'http://maps.google.com/maps/api/geocode/xml?sensor=false&address=' + endereco;

function removeDiacritics (str) {

  var defaultDiacriticsRemovalMap = [
    {'base':'A', 'letters':/[\u0041\u24B6\uFF21\u00C0\u00C1\u00C2\u1EA6\u1EA4\u1EAA\u1EA8\u00C3\u0100\u0102\u1EB0\u1EAE\u1EB4\u1EB2\u0226\u01E0\u00C4\u01DE\u1EA2\u00C5\u01FA\u01CD\u0200\u0202\u1EA0\u1EAC\u1EB6\u1E00\u0104\u023A\u2C6F]/g},
    {'base':'AA','letters':/[\uA732]/g},
    {'base':'AE','letters':/[\u00C6\u01FC\u01E2]/g},
    {'base':'AO','letters':/[\uA734]/g},
    {'base':'AU','letters':/[\uA736]/g},
    {'base':'AV','letters':/[\uA738\uA73A]/g},
    {'base':'AY','letters':/[\uA73C]/g},
    {'base':'B', 'letters':/[\u0042\u24B7\uFF22\u1E02\u1E04\u1E06\u0243\u0182\u0181]/g},
    {'base':'C', 'letters':/[\u0043\u24B8\uFF23\u0106\u0108\u010A\u010C\u00C7\u1E08\u0187\u023B\uA73E]/g},
    {'base':'D', 'letters':/[\u0044\u24B9\uFF24\u1E0A\u010E\u1E0C\u1E10\u1E12\u1E0E\u0110\u018B\u018A\u0189\uA779]/g},
    {'base':'DZ','letters':/[\u01F1\u01C4]/g},
    {'base':'Dz','letters':/[\u01F2\u01C5]/g},
    {'base':'E', 'letters':/[\u0045\u24BA\uFF25\u00C8\u00C9\u00CA\u1EC0\u1EBE\u1EC4\u1EC2\u1EBC\u0112\u1E14\u1E16\u0114\u0116\u00CB\u1EBA\u011A\u0204\u0206\u1EB8\u1EC6\u0228\u1E1C\u0118\u1E18\u1E1A\u0190\u018E]/g},
    {'base':'F', 'letters':/[\u0046\u24BB\uFF26\u1E1E\u0191\uA77B]/g},
    {'base':'G', 'letters':/[\u0047\u24BC\uFF27\u01F4\u011C\u1E20\u011E\u0120\u01E6\u0122\u01E4\u0193\uA7A0\uA77D\uA77E]/g},
    {'base':'H', 'letters':/[\u0048\u24BD\uFF28\u0124\u1E22\u1E26\u021E\u1E24\u1E28\u1E2A\u0126\u2C67\u2C75\uA78D]/g},
    {'base':'I', 'letters':/[\u0049\u24BE\uFF29\u00CC\u00CD\u00CE\u0128\u012A\u012C\u0130\u00CF\u1E2E\u1EC8\u01CF\u0208\u020A\u1ECA\u012E\u1E2C\u0197]/g},
    {'base':'J', 'letters':/[\u004A\u24BF\uFF2A\u0134\u0248]/g},
    {'base':'K', 'letters':/[\u004B\u24C0\uFF2B\u1E30\u01E8\u1E32\u0136\u1E34\u0198\u2C69\uA740\uA742\uA744\uA7A2]/g},
    {'base':'L', 'letters':/[\u004C\u24C1\uFF2C\u013F\u0139\u013D\u1E36\u1E38\u013B\u1E3C\u1E3A\u0141\u023D\u2C62\u2C60\uA748\uA746\uA780]/g},
    {'base':'LJ','letters':/[\u01C7]/g},
    {'base':'Lj','letters':/[\u01C8]/g},
    {'base':'M', 'letters':/[\u004D\u24C2\uFF2D\u1E3E\u1E40\u1E42\u2C6E\u019C]/g},
    {'base':'N', 'letters':/[\u004E\u24C3\uFF2E\u01F8\u0143\u00D1\u1E44\u0147\u1E46\u0145\u1E4A\u1E48\u0220\u019D\uA790\uA7A4]/g},
    {'base':'NJ','letters':/[\u01CA]/g},
    {'base':'Nj','letters':/[\u01CB]/g},
    {'base':'O', 'letters':/[\u004F\u24C4\uFF2F\u00D2\u00D3\u00D4\u1ED2\u1ED0\u1ED6\u1ED4\u00D5\u1E4C\u022C\u1E4E\u014C\u1E50\u1E52\u014E\u022E\u0230\u00D6\u022A\u1ECE\u0150\u01D1\u020C\u020E\u01A0\u1EDC\u1EDA\u1EE0\u1EDE\u1EE2\u1ECC\u1ED8\u01EA\u01EC\u00D8\u01FE\u0186\u019F\uA74A\uA74C]/g},
    {'base':'OI','letters':/[\u01A2]/g},
    {'base':'OO','letters':/[\uA74E]/g},
    {'base':'OU','letters':/[\u0222]/g},
    {'base':'P', 'letters':/[\u0050\u24C5\uFF30\u1E54\u1E56\u01A4\u2C63\uA750\uA752\uA754]/g},
    {'base':'Q', 'letters':/[\u0051\u24C6\uFF31\uA756\uA758\u024A]/g},
    {'base':'R', 'letters':/[\u0052\u24C7\uFF32\u0154\u1E58\u0158\u0210\u0212\u1E5A\u1E5C\u0156\u1E5E\u024C\u2C64\uA75A\uA7A6\uA782]/g},
    {'base':'S', 'letters':/[\u0053\u24C8\uFF33\u1E9E\u015A\u1E64\u015C\u1E60\u0160\u1E66\u1E62\u1E68\u0218\u015E\u2C7E\uA7A8\uA784]/g},
    {'base':'T', 'letters':/[\u0054\u24C9\uFF34\u1E6A\u0164\u1E6C\u021A\u0162\u1E70\u1E6E\u0166\u01AC\u01AE\u023E\uA786]/g},
    {'base':'TZ','letters':/[\uA728]/g},
    {'base':'U', 'letters':/[\u0055\u24CA\uFF35\u00D9\u00DA\u00DB\u0168\u1E78\u016A\u1E7A\u016C\u00DC\u01DB\u01D7\u01D5\u01D9\u1EE6\u016E\u0170\u01D3\u0214\u0216\u01AF\u1EEA\u1EE8\u1EEE\u1EEC\u1EF0\u1EE4\u1E72\u0172\u1E76\u1E74\u0244]/g},
    {'base':'V', 'letters':/[\u0056\u24CB\uFF36\u1E7C\u1E7E\u01B2\uA75E\u0245]/g},
    {'base':'VY','letters':/[\uA760]/g},
    {'base':'W', 'letters':/[\u0057\u24CC\uFF37\u1E80\u1E82\u0174\u1E86\u1E84\u1E88\u2C72]/g},
    {'base':'X', 'letters':/[\u0058\u24CD\uFF38\u1E8A\u1E8C]/g},
    {'base':'Y', 'letters':/[\u0059\u24CE\uFF39\u1EF2\u00DD\u0176\u1EF8\u0232\u1E8E\u0178\u1EF6\u1EF4\u01B3\u024E\u1EFE]/g},
    {'base':'Z', 'letters':/[\u005A\u24CF\uFF3A\u0179\u1E90\u017B\u017D\u1E92\u1E94\u01B5\u0224\u2C7F\u2C6B\uA762]/g},
    {'base':'a', 'letters':/[\u0061\u24D0\uFF41\u1E9A\u00E0\u00E1\u00E2\u1EA7\u1EA5\u1EAB\u1EA9\u00E3\u0101\u0103\u1EB1\u1EAF\u1EB5\u1EB3\u0227\u01E1\u00E4\u01DF\u1EA3\u00E5\u01FB\u01CE\u0201\u0203\u1EA1\u1EAD\u1EB7\u1E01\u0105\u2C65\u0250]/g},
    {'base':'aa','letters':/[\uA733]/g},
    {'base':'ae','letters':/[\u00E6\u01FD\u01E3]/g},
    {'base':'ao','letters':/[\uA735]/g},
    {'base':'au','letters':/[\uA737]/g},
    {'base':'av','letters':/[\uA739\uA73B]/g},
    {'base':'ay','letters':/[\uA73D]/g},
    {'base':'b', 'letters':/[\u0062\u24D1\uFF42\u1E03\u1E05\u1E07\u0180\u0183\u0253]/g},
    {'base':'c', 'letters':/[\u0063\u24D2\uFF43\u0107\u0109\u010B\u010D\u00E7\u1E09\u0188\u023C\uA73F\u2184]/g},
    {'base':'d', 'letters':/[\u0064\u24D3\uFF44\u1E0B\u010F\u1E0D\u1E11\u1E13\u1E0F\u0111\u018C\u0256\u0257\uA77A]/g},
    {'base':'dz','letters':/[\u01F3\u01C6]/g},
    {'base':'e', 'letters':/[\u0065\u24D4\uFF45\u00E8\u00E9\u00EA\u1EC1\u1EBF\u1EC5\u1EC3\u1EBD\u0113\u1E15\u1E17\u0115\u0117\u00EB\u1EBB\u011B\u0205\u0207\u1EB9\u1EC7\u0229\u1E1D\u0119\u1E19\u1E1B\u0247\u025B\u01DD]/g},
    {'base':'f', 'letters':/[\u0066\u24D5\uFF46\u1E1F\u0192\uA77C]/g},
    {'base':'g', 'letters':/[\u0067\u24D6\uFF47\u01F5\u011D\u1E21\u011F\u0121\u01E7\u0123\u01E5\u0260\uA7A1\u1D79\uA77F]/g},
    {'base':'h', 'letters':/[\u0068\u24D7\uFF48\u0125\u1E23\u1E27\u021F\u1E25\u1E29\u1E2B\u1E96\u0127\u2C68\u2C76\u0265]/g},
    {'base':'hv','letters':/[\u0195]/g},
    {'base':'i', 'letters':/[\u0069\u24D8\uFF49\u00EC\u00ED\u00EE\u0129\u012B\u012D\u00EF\u1E2F\u1EC9\u01D0\u0209\u020B\u1ECB\u012F\u1E2D\u0268\u0131]/g},
    {'base':'j', 'letters':/[\u006A\u24D9\uFF4A\u0135\u01F0\u0249]/g},
    {'base':'k', 'letters':/[\u006B\u24DA\uFF4B\u1E31\u01E9\u1E33\u0137\u1E35\u0199\u2C6A\uA741\uA743\uA745\uA7A3]/g},
    {'base':'l', 'letters':/[\u006C\u24DB\uFF4C\u0140\u013A\u013E\u1E37\u1E39\u013C\u1E3D\u1E3B\u017F\u0142\u019A\u026B\u2C61\uA749\uA781\uA747]/g},
    {'base':'lj','letters':/[\u01C9]/g},
    {'base':'m', 'letters':/[\u006D\u24DC\uFF4D\u1E3F\u1E41\u1E43\u0271\u026F]/g},
    {'base':'n', 'letters':/[\u006E\u24DD\uFF4E\u01F9\u0144\u00F1\u1E45\u0148\u1E47\u0146\u1E4B\u1E49\u019E\u0272\u0149\uA791\uA7A5]/g},
    {'base':'nj','letters':/[\u01CC]/g},
    {'base':'o', 'letters':/[\u006F\u24DE\uFF4F\u00F2\u00F3\u00F4\u1ED3\u1ED1\u1ED7\u1ED5\u00F5\u1E4D\u022D\u1E4F\u014D\u1E51\u1E53\u014F\u022F\u0231\u00F6\u022B\u1ECF\u0151\u01D2\u020D\u020F\u01A1\u1EDD\u1EDB\u1EE1\u1EDF\u1EE3\u1ECD\u1ED9\u01EB\u01ED\u00F8\u01FF\u0254\uA74B\uA74D\u0275]/g},
    {'base':'oi','letters':/[\u01A3]/g},
    {'base':'ou','letters':/[\u0223]/g},
    {'base':'oo','letters':/[\uA74F]/g},
    {'base':'p','letters':/[\u0070\u24DF\uFF50\u1E55\u1E57\u01A5\u1D7D\uA751\uA753\uA755]/g},
    {'base':'q','letters':/[\u0071\u24E0\uFF51\u024B\uA757\uA759]/g},
    {'base':'r','letters':/[\u0072\u24E1\uFF52\u0155\u1E59\u0159\u0211\u0213\u1E5B\u1E5D\u0157\u1E5F\u024D\u027D\uA75B\uA7A7\uA783]/g},
    {'base':'s','letters':/[\u0073\u24E2\uFF53\u00DF\u015B\u1E65\u015D\u1E61\u0161\u1E67\u1E63\u1E69\u0219\u015F\u023F\uA7A9\uA785\u1E9B]/g},
    {'base':'t','letters':/[\u0074\u24E3\uFF54\u1E6B\u1E97\u0165\u1E6D\u021B\u0163\u1E71\u1E6F\u0167\u01AD\u0288\u2C66\uA787]/g},
    {'base':'tz','letters':/[\uA729]/g},
    {'base':'u','letters':/[\u0075\u24E4\uFF55\u00F9\u00FA\u00FB\u0169\u1E79\u016B\u1E7B\u016D\u00FC\u01DC\u01D8\u01D6\u01DA\u1EE7\u016F\u0171\u01D4\u0215\u0217\u01B0\u1EEB\u1EE9\u1EEF\u1EED\u1EF1\u1EE5\u1E73\u0173\u1E77\u1E75\u0289]/g},
    {'base':'v','letters':/[\u0076\u24E5\uFF56\u1E7D\u1E7F\u028B\uA75F\u028C]/g},
    {'base':'vy','letters':/[\uA761]/g},
    {'base':'w','letters':/[\u0077\u24E6\uFF57\u1E81\u1E83\u0175\u1E87\u1E85\u1E98\u1E89\u2C73]/g},
    {'base':'x','letters':/[\u0078\u24E7\uFF58\u1E8B\u1E8D]/g},
    {'base':'y','letters':/[\u0079\u24E8\uFF59\u1EF3\u00FD\u0177\u1EF9\u0233\u1E8F\u00FF\u1EF7\u1E99\u1EF5\u01B4\u024F\u1EFF]/g},
    {'base':'z','letters':/[\u007A\u24E9\uFF5A\u017A\u1E91\u017C\u017E\u1E93\u1E95\u01B6\u0225\u0240\u2C6C\uA763]/g}
  ];

  for(var i=0; i<defaultDiacriticsRemovalMap.length; i++) {
    str = str.replace(defaultDiacriticsRemovalMap[i].letters, defaultDiacriticsRemovalMap[i].base);
  }

  return str;

}

Após a inclusão deste texto, clique no botão “Get variables” para adicionar as variáveis ao fluxo, porém deixe apenas a variável url_final, removendo as outras variáveis. Renomeie o JS para “URL Maps”. No final o objeto terá essa aparência:



Clique em Ok e vamos ao próximo passo. Vamos agora fazer a consulta ao Google Maps passando a String “url_final” que montamos no passo anterior. Escolha novamente o objeto “HTTP Client” e o configure de acordo com a imagem abaixo, não esquecendo de mudar o nome do objeto para “Consulta MAPS”:



O retorno do google é em formato de XML, iremos utilizar o objeto “Get data from XML” encontrado dentro da pasta “Input”, ligue-o ao fluxo. Renomeio para “Coordenadas”. Faça as seguintes configurações:

Na aba “File”: marque o campo “XML source is defined in a field” e escolha no campo “get XML source from a field” o campo “result_1”.

Na aba “Content”: no campo Loop XPath adicione o texto /GeocodeResponse/result/geometry/location.

E por último na aba “Fields”: adicione os campos:

Name	XPath	Element	Result type	Type
lat	lat	Node	Value of	String
lng	lng	Node	Value of	String

Pronto com isso já conseguimos pegar as coordenadas do seu endereço. Para concluir o processo vamos escolher os campos que queremos e depois criar um arquivo de texto com o resultado. Para escolher os campos que queremos salvar adicione ao fluxo o objeto chamado “Select values” que está contido na pasta “Transform”. Após adiciona-lo ao fluxo clique em “Get fields to select”, o pentaho irá trazer todos os campos que estão no fluxo, desde o cep e o número fornecidos para consulta até os resultados dos objetos HTTP Cliente, portanto, retire do fluxo as colunas que não desejar removendo as colunas e deixando-as de acordo com a imagem:



Por fim escolha o objeto “Text file output” na pasta “Output” e o insira no fluxo.
Na aba “File” clique no botão “Navega” para escolher o endereço onde o txt será salvo e o nome do arquivo.



Na aba “Fields” clique em “Obtem campos” para pegar os campos que serão salvos no txt e clique em Ok para concluir.



Ok, tudo pronto, hora de salvar o arquivo e testar. Para salvar sua transformação clique no meu “File -> Save”, e salve sua transformação em um local desejado dando um nome para a mesma, no meu caso escolhi o nome “ETL_ENDERECO_CEP”. No final de tudo a transformação ficou com essa aparência:



Para rodar vá no menu "Action->Run". Na tela que abriu clique em "Launch". O resultado final deve ser a tela abaixo:



Bom é isso, ficamos por aqui sobre esse assunto, qualquer dúvida ou sugestão é só dizer!