geocode_google_api.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "purple-apple",
   "metadata": {},
   "source": [
    "# Geocodificando dados com a API do Google\n",
    "\n",
    "Frequentemente, nos deparamos com bases de dados que contêm informações espaciais, como endereços ou CEP's, por exemplo. Entretanto, para fazer análises de dados espaciais, essas informações não bastam e é necessário conhecermos a latitude e a longitude correspondentes. Assim, é comum que seja necessário converter enderços ou CEP's em latitudes e longitudes. Esse processo de conversão é chamado de **geocodificação** e, para realizá-lo, podemos utilizar a [API de Geocodificação do Google](https://developers.google.com/maps/documentation/geocoding/overview). Neste tutorial, faremos isso com Python e utilizaremos uma lista de CEP's como exemplo, entretanto, você pode utilizar uma lista de endereços e aplicar o mesmo código apresentando aqui.\n",
    "\n",
    "## Conexão com o PostgreSQL\n",
    "No nosso caso, a lista de CEP's está armazenada em uma tabela PostgreSQL. Por isso, primeiro precisamos realizar a conexão com o banco de dados e fazer com que o Python tenha acesso à lista de CEP's.\n",
    "\n",
    "Para fazer essa conexão, estamos usando a biblioteca [Psycopg](https://www.psycopg.org/docs/), que permite que o Python conecte-se a um banco de dados PostgreSQL. A função `sql_conection()` realiza a conexão e será utilizada em todos os momento em que for necessário interagir com o banco de dados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "attempted-milton",
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "def sql_connection():\n",
    "    # Realiza a conexão com o banco de dados PostgreSQL e retorna a variável que será utilizada para interagir com o banco\n",
    "    database = ''\n",
    "    user = ''\n",
    "    password = ''\n",
    "    host = ''\n",
    "    port = ''\n",
    "    con = psycopg2.connect(database=database,\n",
    "                           user=user,\n",
    "                           password=password,\n",
    "                           host=host,\n",
    "                           port=port)\n",
    "    return con"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "incorrect-matter",
   "metadata": {},
   "source": [
    "Vamos fazer uma query para o banco de dados e armazenar os CEP's em um dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "suited-constitution",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pandas.io.sql as sqlio\n",
    "def query_to_df(query):\n",
    "    # Dada uma query PostgreSQL, realiza a query no banco de dados e armazena o resultado em um dataframe\n",
    "    con = sql_connection()\n",
    "    data_set = sqlio.read_sql_query(query, con)\n",
    "    return data_set\n",
    "\n",
    "\n",
    "query = \"select distinct(cd_cep), muni, uf from ligacoes_gd lg where muni = 'São Paulo' and uf = 'SP'\"\n",
    "df_ceps = query_to_df(query)\n",
    "print('Total de CEPs únicos:', len(df_ceps))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "distinct-vancouver",
   "metadata": {},
   "source": [
    "Assim, nossa query retornou 1.352 CEP's no município de São Paulo. Agora iremos fazer requisições à API do Google e obter as latitudes e longitudes correspondentes a cada um desses CEP's.\n",
    "\n",
    "## Fazendo requisições para a API do Google\n",
    "A função `get_lat_long(cep)` utiliza a biblioteca [Requests](https://requests.readthedocs.io/en/master/) para realizar uma requisição à API, procurando por um CEP, e retorna os dados encontrados.  \n",
    "  \n",
    "Para utilizá-la, você deve inserir a sua chave de API no dicionário de parâmetros. Para obter uma chave da API de Geocodificação do Google, siga [este link](https://developers.google.com/maps/documentation/geocoding/get-api-key)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "infrared-evening",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "def get_lat_long(cep):\n",
    "    # Dado um CEP, faz uma requisição para a API de Geocodificação do Google. Caso a requisição seja bem sucedida, retorna o conteúdo da requisição em formato json\n",
    "    params = {\n",
    "        'address': cep,\n",
    "        'key': ''\n",
    "    }\n",
    "    geocode_api_url = 'https://maps.googleapis.com/maps/api/geocode/json'\n",
    "    r = requests.post(geocode_api_url, params=params)\n",
    "    if r.status_code == 200:\n",
    "        return json.loads(r.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "plastic-lyric",
   "metadata": {},
   "source": [
    "Para nos familiarizarmos com a resposta da API, vamos fazer uma requisição de exemplo e utilizar a função `jprint()` para gerar uma visualização amigável do objeto json retornado pela API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "necessary-magic",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "def jprint(obj):\n",
    "    # Cria visualização amigável de um objeto json\n",
    "    text = json.dumps(obj, sort_keys=True, indent=4, ensure_ascii=False)\n",
    "    print(text)\n",
    "\n",
    "\n",
    "cep_example = '05508-065'\n",
    "response_example = get_lat_long(cep_example)\n",
    "jprint(response_example)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sustainable-climate",
   "metadata": {},
   "source": [
    "## Obtendo latitudes e longitudes\n",
    "Agora que fizemos uma requisição de exemplo e estamos familiarizados com a resposta da API, podemos seguir para a parte divertida e finalmente obter as latitudes e longitudes correspondentes aos 1.352 CEP's em nossa tabela.  \n",
    "  \n",
    "Primeiro, vamos criar uma tabela PostgreSQL para armazenar nossos dados. Utilizamos o método [connection.cursor()](https://www.psycopg.org/docs/cursor.html), que permite que o Python execute comandos em um banco de dados PostgreSQL. Repare que, após executar o comando, é necessário utilizar também o método [connection.commit()](https://www.psycopg.org/docs/connection.html#connection.commit) para garantir que as mudanças no banco de dados sejam feitas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "outdoor-bargain",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_sql_table():\n",
    "    # Cria uma tabela PostgreSQL para armazenar os dados dos CEP's\n",
    "    con = sql_connection()\n",
    "    with con.cursor() as cur:\n",
    "        create_table_command = 'create table if not exists cep_data('\\\n",
    "            'cep varchar(50),'\\\n",
    "            'muni varchar(250),'\\\n",
    "            'uf varchar(2),'\\\n",
    "            'lat float,'\\\n",
    "            'long float'\\\n",
    "            ')'\n",
    "        cur.execute(create_table_command)\n",
    "        con.commit()\n",
    "create_sql_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "living-opera",
   "metadata": {},
   "source": [
    "Uma vez criada a tabela, podemos começar a alimentá-la com os dados dos CEP's. Para isso, vamos considerar a orientação da [documentação do PostgreSQL](https://www.postgresql.org/docs/current/populate.html#POPULATE-COPY-FROM) de que a melhor forma de alimentar uma tabela é usando o comando COPY FROM. Uma demonstração sobre o melhor desempenho do COPY FROM em relação a outros métodos pode ser encontrada [nesta análise](https://hakibenita.com/fast-load-data-python-postgresql), que avalia a performance de diferentes formas de importar dados de uma fonte remota para uma tabela PostgreSQL.  \n",
    "  \n",
    "Vamos começar definindo as funções que utilizaremos para armazenar os dados da resposta da API em um dataframe. Repare que, na função `cep_data_to_df()`, caso o CEP não seja encontrado e a resposta da API volte com valor nulo, iremos adicionar o CEP ao dataframe mesmo assim, porém com valores nulos nas colunas de informações. Isso nos ajudará a ter controle sobre quais CEP's foram encontrados e quais não foram."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "educational-ethiopia",
   "metadata": {},
   "outputs": [],
   "source": [
    "def cep_data_to_df(df_ceps):\n",
    "    # Recebe um dataframe contendo os CEP's e, a partir das requisições à API de Geocodificação do Google, estrutura um dataframe contendo os dados do CEP\n",
    "    data_set = pd.DataFrame(columns=[\n",
    "        'cep',\n",
    "        'muni',\n",
    "        'uf',\n",
    "        'lat',\n",
    "        'long'\n",
    "    ])\n",
    "    for cep in df_ceps['cd_cep']:\n",
    "        cep_data = get_lat_long(cep)\n",
    "        if len(cep_data['results']) != 0:\n",
    "            new_row = {\n",
    "                'cep': cep,\n",
    "                'muni': df_ceps.loc[df_ceps['cd_cep'] == cep, 'muni'].iloc[0],\n",
    "                'uf': df_ceps.loc[df_ceps['cd_cep'] == cep, 'uf'].iloc[0],\n",
    "                'lat': cep_data['results'][0]['geometry']['location']['lat'],\n",
    "                'long': cep_data['results'][0]['geometry']['location']['lng']\n",
    "            }\n",
    "        else:\n",
    "            new_row = {'cep': cep}\n",
    "        data_set = data_set.append(new_row, ignore_index=True)\n",
    "    return data_set"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "focal-qualification",
   "metadata": {},
   "source": [
    "Agora podemos definir a função `df_to_csv()`, que exporta o dataframe para um csv temporário, e a função `csv_to_sql_table()`, que utiliza o COPY FROM para alimentar a tabela PostgreSQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "helpful-craft",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "def df_to_csv(csv_path, data_set):\n",
    "    # Exporta um dataframe para um arquivo csv\n",
    "    data_set.to_csv(csv_path, header=False, index=False, sep=';',\n",
    "                    encoding='utf-8', quoting=csv.QUOTE_MINIMAL)\n",
    "\n",
    "\n",
    "def csv_to_sql_table(csv_path):\n",
    "    # Alimenta a tabela PostgreSQL com os dados dos CEP's\n",
    "    con = sql_connection()\n",
    "    with con.cursor() as cur:\n",
    "        cur.execute('copy cep_data from %s delimiter %s csv', [csv_path, ';'])\n",
    "        con.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "selected-undergraduate",
   "metadata": {},
   "source": [
    "Com todas as funções definidas, podemos agora iterar sobre o dataframe `df_ceps`, que contém os CEP's, e fazer requisições à API de Geocodificação do Google. Vamos armazenar as respostas na tabela PostgreSQL em lotes de 1000 CNPJ's para garantir que, caso ocorra um erro enquanto o código roda, não perderemos os dados das requisições já realizadas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "meaningful-session",
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_path = r'\\temp_cep_data.csv'\n",
    "df_ceps_data = pd.DataFrame()\n",
    "for i in range(0, len(df_ceps), 1000):\n",
    "    new_batch = cep_data_to_df(df_ceps[i:i+1000])\n",
    "    df_to_csv(csv_path, new_batch)\n",
    "    csv_to_sql_table(csv_path)\n",
    "    df_ceps_data = df_ceps_data.append(new_batch, ignore_index=True)\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "composite-thunder",
   "metadata": {},
   "source": [
    "Para finalizar, vamos checar quantos CNPJ's foram encontrados e quantos não foram."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "instrumental-racing",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = 'select * from cep_data'\n",
    "df_cep_data = query_to_df(query)\n",
    "\n",
    "empty_columns = ['muni',\n",
    "                 'uf',\n",
    "                 'lat',\n",
    "                 'long'\n",
    "                 ]\n",
    "\n",
    "df_found_cep = df_cep_data.dropna(axis=0, how='all', subset=empty_columns)\n",
    "percentage_not_found_cep = 100*(1 - (len(df_found_cep)/len(df_cep_data)))\n",
    "print('De', len(df_cep_data), 'CEPs, foram encontrados', len(df_found_cep))\n",
    "print('Não foram encontrados', round(percentage_not_found_cep, 2), '% dos CEPs buscados.')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}