Não é de hoje que vemos uma expansão comercial e um marketing sem limites. Mas como obter isso? Como sabe onde seu cliente está? Quais os hábitos dele? Ele compra de casa ou quando está em trânsito? Essas e outras perguntas são respondidas quando você passa a armazenar a informação dele desde sua chegada. A essa informação chamamos de Business Intelligence, ou inteligência de negócios.
Mas para não perder o foco do artigo vou simplificar um pouco. O cliente chega até você com um endereço IP, proveniente da internet. Se você armazenar ele, pode fazer joins com catálogos de IPs e ter mais informação ainda, sem nenhum artifício adicional, uma vez que o pacote HTTP já carrega esta informação. Com o tráfego do cliente capturado, você pode começar a gerar os seus próprios indicadores, até bem semelhantes a alguns que o Google Analytics já te oferece. Então fui atrás de como realizar a importação da informação que é distribuída.
Como base de partida usei o tutorial da Webstein WebSolutions. Só que o tutorial foi criado usando a versão antiga do GeoLite DB e precisou de algumas alterações.
Para diferenciar esta publicação, com a versão 2 do GeoLite DB, vou indicar uma forma mais automatizada de se fazer a atualização.
- Crie a pasta “c:\geoipupdate”.
- Crie os 2 arquivos indicados abaixo dentro da pasta.
- Faça o download da base de ip de cidade e descompacte nesta pasta. Observe pelo script quais foram utilizados.
- Verifique no batch se o caminho do “sqlcmd” encontra-se na pasta indicada, senão corrija-o.
- Execute seu batch.
geoipupdate.bat
@ECHO OFF @ECHO Atualizando tabela de GeoIP... "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S 127.0.0.1 -i .\geoipupdate.sql
geoipupdate.sql
use [your_database] GO IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GeoIP') BEGIN DROP TABLE GeoIP END GO CREATE TABLE GeoIP ( network nvarchar(18), geoname_id bigint, registered_country_geoname_id bigint, represented_country_geoname_id bigint, is_anonymous_proxy bit, is_satellite_provider bit, postal_code nvarchar(10), latitude nvarchar(15), longitude nvarchar(15), accuracy_radius nvarchar(15) ) GO BULK INSERT GeoIP FROM 'C:/geoipupdate/GeoLite2-City-Blocks-IPv4.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR=',', ROWTERMINATOR = '0x0a') GO IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GeoLoc') BEGIN DROP TABLE GeoLoc END GO CREATE TABLE GeoLoc ( geoname_id bigint, locale_code nvarchar(15), continent_code nvarchar(15), continent_name nvarchar(255), country_iso_code nvarchar(255), country_name nvarchar(255), subdivision_1_iso_code nvarchar(255), subdivision_1_name nvarchar(255), subdivision_2_iso_code nvarchar(255), subdivision_2_name nvarchar(255), city_name nvarchar(255), metro_code nvarchar(15), time_zone nvarchar(255) ) GO BULK INSERT GeoLoc FROM 'C:/geoipupdate/GeoLite2-City-Locations-pt-BR.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR=',', ROWTERMINATOR = '0x0a') GO
A saída de tela do batch deverá ficar algo como:
Atualizando tabela de GeoIP... Changed database context to '<your_database>'. (97590 rows affected)
Na execução podem ocorrer erros porque o arquivo não está normalizado, mas são mensagens posicionais de saída que indicam onde está o problema. Se você não leu o artigo do Webstein, ele indica fazer uma alteração de ” (aspas) para vazio. O Notepad++ executa isso na velocidade de um tiro pelo replace (Ctrl+H).
É claro que não está completa a solução. Ainda falta colocar o “wget” antes da execução do arquivo para baixar a versão atualizada e o descompactador para uma solução mais inteligente, mas isso fica para a próxima edição. Se você fizer, não esqueça de compartilhar a sua saída que incrementamos a base de conhecimento aqui.
Por fim, um join simples para este modelo. Mas a melhor busca seria verificar a máscara da rede com o IP do cliente, mas essa fica como desafio para você. Neste importação fazemos somente a base de cidades. É preciso ainda alterar para receber a base de países, também disponível no site da MaxMind.
SELECT top 10 * FROM GeoIP geo (nolock) INNER JOIN GeoLoc loc (nolock) ON geo.geoname_id = loc.geoname_id WHERE loc.country_iso_code = 'BR'
Um abraço

Carioca, arquiteto de soluções e desenvolvedor de software desde 1996. Atualmente moro em Vila Nova de Gaia, Porto, Portugal.
“Perhaps the greatest challenge of my career is to identify which part of my profession is not a hobby.
The ease of logical understanding and the constant search for knowledge are great drivers of my career.”