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