Escrevo neste blog, códigos e teorias de programação que desenvolvo e são úteis para mim.
Pelo mesmo, espero que seja útil para você. Todo conteúdo aqui é público e gratuito.
Todo trabalho realizado com o GeoIp gira em torno do banco de dados, então para este artigo, não utilizaremos nenhum outro meio além do SQL. Será utilizado o Postgres para os exemplos citados, porém o mesmo sql
pode ser inserido no MySql ou qualquer outro SGBD de sua preferência.
É importante também ter em mãos alguma ferramenta que possa ler documentos de extensão CSV, como por exemplo o Excel. Se você não tem nenhum instalado, recomendo fortemente que baixe o pacote LibreOffice, que faz
o mesmo papel do pacote Office da Microsoft, porém é cem por cento gratuito. Essa ferramenta será necessária para abrirmos arquivos CSV em formato de planilhas e facilitar nosso trabalho.
Aliás, o que é o GeoIp?
Nada mais é do que buscar/encontrar uma localização baseado em um endereço de Ip. Não abordaremos neste tutorial sobre como adquirir o Ip, você pode conferir esta outra matéria que ensina a fazer isso aqui [ainda não publicado]
mesmo no blog. Uma vez que você possui o endereço de Ip, você consegue (através do GeoIp) a cidade, estado, país e até mesmo a latitude e longitude (nem sempre preciso) do endereço que está acessando seu site ou
componente.
Por que utilizar o GeoIp invés do localizador do navegador?
Pelo simples motivo de que o usuário que está navegando em seu site ou utilizando algum de seus serviços ter a opção de negar o pedido do navegador, sendo assim, você não conseguirá obter a localização
do usuário. Entretanto, a localização oferecida pelo navegador é mais precisa, você inclusive consegue encontrar a localização do bairro de onde o usuário está. Nada lhe impede de utilizar o GeoIp e o localizador do
navegador juntos, pois uma vez que o usuário negar seu pedido de localização via navegador, você ainda tem a segunda opção com o GeoIp.
Qual a precisão do GeoIp? E do localizador do navegador? E como pegam a localização do usuário?
O localizador do navegador é mais preciso. O serviço de localização do Google Chrome, por exemplo, localiza o usuário pelo Ip que está acessando seu site ou serviço, exatamente como o GeoIp, porém, o mesmo utiliza
os serviços de rastreamento via radar do Google, o que o torna muito mais preciso, porém dependente da aceitação do usuário para o mesmo.
Já o GeoIp possui armazenado no banco de dados o registro de todos os países de mundo, junto com seus estados e cidades. Exatamente, a tabela de cidades do GeoIp tem mais de 10 mil registros, mas não se preocupe
com isso, pois iremos tratá-lo mais tarde. Como os registros do GeoIp são locais e não dependem de radares ou outras fontes, alguns endereços não serão tão bem localizados. Por exemplo, um Ip registrado na cidade de
Itirapuã, uma cidade com aproximadamente 8 mil habitantes no interior de São Paulo, tem seu registro localizado na cidade de Franca - SP (onde moro atualmente), pois é uma região melhor localizada (aproximadamente
350 mil habitantes). O mesmo não acontece com o localizador do navegador, pois o mesmo consegue encontrar ambas as cidades.
Instalação do GeoIp
Antes de realizar a instalação do GeoIp, é importante dizer o número de registros, tabelas e funções que o mesmo irá gerar. Passarei aqui um código sql para que você execute em seu banco para que todos os registros sejam
criadas devidamente, e com isso, serão criadas 4 tabelas no banco de dados, sendo elas geoip_asn, geoip_city_block, geoip_city_location e geoip_country.
Além disso, também serão criadas outras 9 funções que são utilizadas para manipular os dados contidos nas tabelas.
Dados essas informações, você pode concluir criar as tabelas e funções dentro do seu banco de dados atual que utiliza em seu projeto, ou criar um outro banco de dados (no mesmo servidor de preferência) apenas para
conter os registros do GeoIp. Caso opte pela segunda opção, você poderá buscar informações do banco de GeoIp utilizando um comando individual para este banco no seu back-end, ou se precisar, talvez este artigo seja
útil para você [ainda não publicado], onde ensino como buscar informações de tabelas de bancos de dados diferentes que estão dentro do mesmo servidor.
Execute a seguinte query para criar as tabelas e funções:
CREATE TABLE geoip_country (
begin_ip INET NOT NULL,
end_ip INET NOT NULL,
country CHAR(2) NOT NULL,
name VARCHAR(100) NOT NULL,
CONSTRAINT valid_range CHECK (begin_ip <= end_ip)
);
CREATE TABLE geoip_city_location (
loc_id INTEGER PRIMARY KEY,
country CHAR(2) NOT NULL,
region CHAR(2),
city VARCHAR(100),
postal_code VARCHAR(10),
latitude DOUBLE PRECISION,
longitude DOUBLE PRECISION,
metro_code INT,
area_code INT
);
CREATE TABLE geoip_city_block (
begin_ip INET NOT NULL,
end_ip INET NOT NULL,
loc_id INTEGER NOT NULL REFERENCES geoip_city_location(loc_id)
);
CREATE TABLE geoip_asn (
begin_ip INET NOT NULL,
end_ip INET NOT NULL,
name TEXT NOT NULL
);
-- indexes (might be improved to handle index-only scans)
CREATE INDEX geoip_country_ip_idx ON geoip_country (begin_ip DESC);
CREATE INDEX geoip_city_block_ip_idx ON geoip_city_block (begin_ip DESC);
CREATE INDEX geoip_asn_ip_idx ON geoip_asn (begin_ip DESC);
/** functions used to search data by IP **/
-- search country, returns just the country code (2 characters)
CREATE OR REPLACE FUNCTION geoip_country_code(p_ip INET) RETURNS CHAR(2) AS $$
SELECT country
FROM geoip_country
WHERE $1 >= begin_ip AND $1 <= end_ip ORDER BY begin_ip DESC LIMIT 1;
$$ LANGUAGE sql;
-- search city, returns just the location ID (PK of the geoip_city_location)
CREATE OR REPLACE FUNCTION geoip_city_location(p_ip INET) RETURNS INT AS $$
SELECT loc_id
FROM geoip_city_block
WHERE $1 >= begin_ip AND $1 <= end_ip ORDER BY begin_ip DESC LIMIT 1;
$$ LANGUAGE sql;
-- search city, returns all the city details (zipcode, GPS etc.)
CREATE OR REPLACE FUNCTION geoip_city(p_ip INET, OUT loc_id INT, OUT country CHAR(2), OUT region CHAR(2),
OUT city VARCHAR(100), OUT postal_code VARCHAR(10),
OUT latitude DOUBLE PRECISION, OUT longitude DOUBLE PRECISION,
OUT metro_code INT, OUT area_code INT) AS $$
SELECT l.loc_id, country, region, city, postal_code, latitude, longitude, metro_code, area_code
FROM geoip_city_block b JOIN geoip_city_location l ON (b.loc_id = l.loc_id)
WHERE $1 >= begin_ip AND $1 <= end_ip ORDER BY begin_ip DESC LIMIT 1;
$$ LANGUAGE sql;
-- search country, returns all the details
CREATE OR REPLACE FUNCTION geoip_country(p_ip INET, OUT begin_ip INET, OUT end_ip INET,
OUT country CHAR(2), OUT name VARCHAR(100)) AS $$
SELECT begin_ip, end_ip, country, name
FROM geoip_country WHERE $1 >= begin_ip AND $1 <= end_ip ORDER BY begin_ip DESC LIMIT 1;
$$ LANGUAGE sql;
-- search ASN, returns the IP range and ASN name
CREATE OR REPLACE FUNCTION geoip_asn(p_ip INET, OUT begin_ip INET, OUT end_ip INET,
OUT name VARCHAR(100)) AS $$
SELECT begin_ip, end_ip, name
FROM geoip_asn WHERE $1 >= begin_ip AND $1 <= end_ip ORDER BY begin_ip DESC LIMIT 1;
$$ LANGUAGE sql;
/** functions used to search data by IP **/
-- check consistency of the country table
CREATE OR REPLACE FUNCTION geoip_country_check() RETURNS BOOLEAN AS $$
DECLARE
v_previous RECORD;
v_country RECORD;
v_first BOOLEAN := TRUE;
v_valid BOOLEAN := TRUE;
BEGIN
FOR v_country IN SELECT * FROM geoip_country ORDER BY begin_ip ASC LOOP
IF (NOT v_first) THEN
v_first := FALSE;
IF (v_previous.end_ip + 1 != v_country.begin_ip) THEN
RAISE WARNING 'there''s a hole between %-% and %-%',v_previous.begin_ip,
v_previous.end_ip,v_country.begin_ip,v_country.end_ip;
v_valid := FALSE;
END IF;
END IF;
v_previous := v_country;
END LOOP;
RETURN v_valid;
END;
$$ LANGUAGE plpgsql;
-- check consistency of the city table
CREATE OR REPLACE FUNCTION geoip_city_check() RETURNS BOOLEAN AS $$
DECLARE
v_previous RECORD;
v_block RECORD;
v_first BOOLEAN := TRUE;
v_valid BOOLEAN := TRUE;
BEGIN
FOR v_block IN SELECT begin_ip, end_ip FROM geoip_city_block ORDER BY begin_ip ASC LOOP
IF (NOT v_first) THEN
v_first := FALSE;
IF (v_previous.end_ip + 1 != v_block.begin_ip) THEN
RAISE WARNING 'there''s a hole between %-% and %-%',v_previous.begin_ip,
v_previous.end_ip,v_block.begin_ip,v_block.end_ip;
v_valid := FALSE;
END IF;
END IF;
v_previous := v_block;
END LOOP;
RETURN v_valid;
END;
$$ LANGUAGE plpgsql;
-- check consistency of the ASN table
CREATE OR REPLACE FUNCTION geoip_asn_check() RETURNS BOOLEAN AS $$
DECLARE
v_previous RECORD;
v_block RECORD;
v_first BOOLEAN := TRUE;
v_valid BOOLEAN := TRUE;
BEGIN
FOR v_block IN SELECT begin_ip, end_ip FROM geoip_asn ORDER BY begin_ip ASC LOOP
IF (NOT v_first) THEN
v_first := FALSE;
IF (v_previous.end_ip + 1 != v_block.begin_ip) THEN
RAISE WARNING 'there''s a hole between %-% and %-%',v_previous.begin_ip,
v_previous.end_ip,v_block.begin_ip,v_block.end_ip;
v_valid := FALSE;
END IF;
END IF;
v_previous := v_block;
END LOOP;
RETURN v_valid;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION geoip_bigint_to_inet(p_ip BIGINT) RETURNS inet AS $$
SELECT (($1 >> 24 & 255) || '.' || ($1 >> 16 & 255) || '.' || ($1 >> 8 & 255) || '.' || ($1 & 255))::inet
$$ LANGUAGE sql strict immutable;
Uma vez executado, as tabelas estão prontas para receberem dados. Faça o download dos seguintes arquivos:
Extraia-os em algum lugar de fácil acesso e em seguida, aconselho criar uma nova pasta para agrupar todos os arquivos. Após a extração, em cada pasta haverá um arquivo CSV, exceto GeoLite City que contém dois. Recorte
os arquivos CSV de todas as pastas e cole em uma nova pasta como já citado, agrupando todos os CSV em um lugar só.
Antes de iniciarmos a importação desses dados para as tabelas de GeoIp, é necessário realizar algumas alterações nestes arquivos para que possamos importá-los:
GeoIPASNum2
Este arquivo é o único que não precisa de alterações, portanto está correto.
GeoLiteCity-Blocks
Abra o arquivo com o NotePad++ (de preferência) e delete as duas primeiras linhas. Tenha certeza de não ter deixado linhas em branco após deletar. Salve o arquivo.
GeoLiteCity-Location
Execute exatamente o mesmo processo do item acima.
GeoIPCountryWhois
Esse é o maior arquivo a se tratar, pois necessita de mais alterações, muitas linhas estão com endereços de Ip errados e irão resultar em erro caso você tente importá-los para as tabelas do banco.
Primeiramente, abra o arquivo com o Excel ou com qualquer programa de planilhas e em seguida delete a terceira e quarta coluna.
Após isso, precisaremos tratar os campos com Ips incorretos que eu tinha citado anteriormente. Para isso, vou utilizar uma lógica em sql para tratar este problema, mas você pode fazer da maneira que achar melhor.
Para deletar tais linhas utilizando sql, crie uma tabela temporária (no meu caso dei o nome de testes) e insira as colunas ip1, ip2, nome1 e
nome2, todos os campos devem ser VARCHAR (tamanho 300 de preferência).
Após o passo anterior, caso esteja utilizando o Postgres (senão, procure esta opção no seu SGBD) você encontrará (ao clicar com o botão direito) a opção importar..., selecione-a.
Em Browse localize onde está seu arquivo GeoIPCountryWhois.csv. Em format selecione a opção CSV e em Encoding deixe vazio. Clique em import e aguarde todos os dados
serem importados para a tabela.
Agora removeremos as linhas da primeira e da segunda coluna, onde os Ips não foram pontuados e ocasionariam em um erro caso importados na tabela geoip_country mais tarde. Para resolver isso, execute a query:
DELETE FROM {nome da tabela} WHERE {nome da 1° coluna} !~ '\.';
DELETE FROM {nome da tabela} WHERE {nome da 2° coluna} !~ '\.';
Após isso, verifique manualmente de forma rápida se agora todos os Ips estão pontuados, ou seja, se os Ips sem pontuações foram excluídos.
Uma vez que todos os passos anteriores estiverem concluídos, utilize um comando simples de SELECT * FROM {nome da tabela};. Quando todos os dados forem selecionados e exibidos (pode demorar um tempo),
no Postgres, clique em File e selecione Export.
Preencha os campos como a imagem:
Então clique em Ok para finalizar. O novo arquivo gerado deverá substituir o antigo GeoIPCountryWhois.csv que possuia as linhas de Ip erradas.
Se tudo ocorreu bem até aqui, as correções estão feitas e os arquivos já estão prontos para serem importados.
Antes de começar a importação dos dados para as tabelas, é necessário tornar o acesso aos CSVs públicos para que possamos executar a próxima query, caso contrário, ocasionará o seguinte erro: ERROR: could not open file "C:\Users\Conrado\Desktop\GeoIP/GeoIPCountryWhois.csv" for reading: Permission denied
Para que isso não ocorra, vá até a pasta onde estão os arquivos CSV e para cada arquivo faça o seguinte processo: Botão direito -> Propriedades -> Segurança -> Editar... (em Nomes de grupo e usuários) -> Adicionar...
Digite Todos e clique em Verificar Nomes -> Selecione Todos e clique em Ok -> Clique em Ok novamente -> Selecione Todos e clique em Ok -> Selecione Todos novamente e clique em Ok.
PS: Talvez esse procedimento mude alguns passos dependendo da versão do seu Windows.
Faça o mesmo para todos os outros arquivos.
Agora será feita a importação de todos os dados do CSV para as tabelas oficiais.
O mesmo pode ser feito de forma manual (clicando sobre a tabela e selecionando import...), mas o recomendável é seguir a query que passarei agora. Caso utilize outro banco que não seja o Postgres, dê uma revisada
nos códigos, pois alguns comandos não serão compatíveis, basta trocá-los.
Execute a query:
COPY geoip_country FROM '{caminho}/countries.csv'
WITH csv DELIMITER ',' NULL '' QUOTE '"' ENCODING 'ISO-8859-2';
CREATE TEMPORARY TABLE geoip_city_block_tmp (
begin_ip BIGINT NOT NULL,
end_ip BIGINT NOT NULL,
loc_id INTEGER NOT NULL
);
CREATE TEMPORARY TABLE geoip_asn_tmp (
begin_ip BIGINT NOT NULL,
end_ip BIGINT NOT NULL,
name TEXT NOT NULL
);
COPY geoip_city_block_tmp FROM '{caminho}/blocks.csv'
WITH csv DELIMITER ',' NULL '' QUOTE '"' ENCODING 'ISO-8859-2';
COPY geoip_city_location FROM '{caminho}/locations.csv'
WITH csv DELIMITER ',' NULL '' QUOTE '"' ENCODING 'ISO-8859-2';
COPY geoip_asn_tmp FROM '{caminho}/GeoIPASNum2.csv'
WITH csv DELIMITER ',' NULL '' QUOTE '"' ENCODING 'ISO-8859-2';
INSERT INTO geoip_city_block
SELECT geoip_bigint_to_inet(begin_ip),
geoip_bigint_to_inet(end_ip), loc_id
FROM geoip_city_block_tmp;
INSERT INTO geoip_asn
SELECT geoip_bigint_to_inet(begin_ip),
geoip_bigint_to_inet(end_ip), name
FROM geoip_asn_tmp;
ANALYZE;
Uma vez executada a query, poderá demorar alguns minutos até que todos os dados sejam inseridos. Quando o mesmo se concluir, todas as tabelas estarão populadas e prontas para serem utilizadas.
Fim do processo de instalação.
Funções
Todas funções inseridas em functions podem ser utilizadas para extrair dados do banco. O mais comum de se utilizar para extrair o máximo de informações de um Ip é:
SELECT * FROM geoip_city('{ip desejado}'::inet);
Retornando o país, estado, cidade, latitude, longitude e outros campos. O estado (region) virá em forma de código. Para que você converta-o em um nome real do estado, leia o complemento abaixo.
Complementos
Através do tutorial, você terá em seu banco de dados todas as informações de Ips e localidades do Brasil e do mundo. Contudo, se observar bem, através da function que traz os dados completo como
City, Country e Region (seria o estado), o campo Region vem com o ID do estado daquela localização, porém, o mesmo não vem com o conteúdo em String, VARCHAR, ou de forma legível. Mas não se preocupe,
para isso há solução. Há outro artigo no blog ensinando como fazer isso, acesse o artigo clicando aqui.
Como pegar o Estado pelo GeoIp
Por que não inserir tudo junto neste artigo?
Achei melhor separar este conteúdo, pois o tutorial deste artigo (GeoIp), é o tutorial oficial da MaxMind (consulte-o nas referências ao final deste artigo) utilizado para pegar o GeoIp fornecido por eles. Em relação ao estado, eles também fornecem
(que eu ensino como utilizá-lo neste artigo), mas não vem junto com a instalação oficial. Também não há um tutorial oficial ensinando como instalá-lo.
Qualquer outras informações, seguem as referência bibliograficas e fontes originais para a criação deste artigo.
