Blog do Bragil

Sabe aquele recurso que algumas redes sociais têm, de exibir os pontos próximos à sua localização? Sim, é possível e bem simples de fazer. A fórmula matemática que faz tudo funcionar chama-se Fórmula de Haversine. Sem mais delongas, vamos ao código:

-- Cálculo da distância entre dois pontos usando a fórmula de Haversine em PostgreSQL
CREATE OR REPLACE FUNCTION distancia_km(lat1 NUMERIC, lng1 NUMERIC, lat2 NUMERIC, lng2 NUMERIC)
RETURNS DOUBLE PRECISION AS
$BODY$
	SELECT 6371 * acos(
		sin( radians($1) ) * sin( radians( $3 ))
	      + cos( radians($1) ) * cos( radians( $3 )) * cos(radians($4) - radians($2))  )
	AS distance;
$BODY$
  LANGUAGE SQL IMMUTABLE
  COST 100;

Suponhamos que exista uma tabela de usuários onde exista os campos latitude e longitude. É fácil criar uma query usando a função acima para trazer os usuários mais próximos do seu local, em um raio de 10 Km:

SELECT * FROM usuarios WHERE distancia_km(minha_latitude, minha_longitude, latitude, longitude) < 10
AND distancia_km(minha_latitude, minha_longitude, latitude, longitude) <> 0

Serão listados os usuários que estão a menos de 10 Km de distância de você.

OBS: Se quiser usar milhas ao invés de quilômetros, use a constante 3959 ao invés de 6371 na função:

-- Cálculo da distância entre dois pontos usando a fórmula de Haversine em PostgreSQL
CREATE OR REPLACE FUNCTION distancia_milhas(lat1 NUMERIC, lng1 NUMERIC, lat2 NUMERIC, lng2 NUMERIC)
RETURNS DOUBLE PRECISION AS
$BODY$
	SELECT 3959 * acos(
		sin( radians($1) ) * sin( radians( $3 ))
	      + cos( radians($1) ) * cos( radians( $3 )) * cos(radians($4) - radians($2))  )
	AS distance;
$BODY$
  LANGUAGE SQL IMMUTABLE
  COST 100;

Trabalhar com paginação de dados no PostgreSQL é muito fácil. As cláusulas usadas para paginação são LIMIT e OFFSET. A fórmula básica seria o seguinte:

SELECT * FROM tabela
    LIMIT <número de itens por página>
    OFFSET(<página> - 1) * <número de itens por página>

Muito fácil!

Uma dica rápida, mas que pode poupar um bom tempinho. Usar ORDER BY em queries que usam a cláusula UNION é bem mais simples do que parece. Suponhamos que temos duas tabelas, tb_cidades e tb_paises, e queremos trazer em uma única query o id e o nome tanto de tb_cidades quanto de tb_paises. Para isso combinamos duas queries SELECT usando a cláusula UNION:

SELECT c.cidade_id, c.nome FROM tb_cidades c
UNION
SELECT p.pais_id, p.nome FROM tb_paises p

Teremos assim, em uma única query, tanto cidades quanto países. Mas, e a ordenação?? É aí que muita gente se atrapalha, mas a solução é bem simples:

1) Atribua um alias em comum para as colunas que serão ordenadas. Se for a coluna de ID, coloque o mesmo alias tanto para cidades quanto para países.

2) Utilize este alias no ORDER BY.

Então vamos alterar a query acima para ordenar pela coluna nome de tb_cidades e tb_paises:

SELECT c.cidade_id, c.nome AS coluna_nome FROM tb_cidades c
UNION
SELECT p.pais_id, p.nome AS coluna_nome FROM tb_paises p
ORDER BY coluna_nome

Simples assim.

O PostgreSQL possui o operador de concatenação de strings || (dois pipes):

Suponhamos que nome = 'Rogério' e sobrenome 'Bragil'

SELECT (nome || ' ' || sobrenome) AS nome_completo FROM tabela


Isto retornará 'Rogério Bragil', pois houve a concatenação do campo nome ('Rogério'), do espaço e do campo sobrenome ('Bragil').

No PostgreSQL a cláusula LIKE é case-sensitive, ou seja, diferencia maiúsculas de minúsculas. A alternativa para consultas case-insensitive é a cláusula ILIKE, que funciona de modo semelhante ao LIKE:

SELECT * FROM tabela WHERE nome ILIKE '%Rogério%'


Há outro problema nesta consulta: tanto o LIKE quanto o ILIKE consideram os acentos, ou seja, se existir um 'Rogerio' (sem acento) na tabela este registro será ignorado. Para que a consulta não considere os acentos e traga tanto 'Rogério' quanto 'Rogerio' ou 'rogerio', podemos usar a função para remover acentos:


SELECT * FROM tabela WHERE sem_acentos(nome) ILIKE sem_acentos('%Rogério%')


Uma outra opção é remover os acentos da string passada no ILIKE antes de montar a query, usando a linguagem que você estiver usando para desenvolver. A consulta então ficaria assim:


SELECT * FROM tabela WHERE sem_acentos(nome) ILIKE '%Rogerio%'