Hace algun tiempo me topé con la necesidad de realizar un ordenamiento con base a la distancia entre la ubicación del visitante al sitio y los resultados de una busqueda. Esto fue desarrollado para los estados unidos, utilizando una base de datos de codigos postales (zipcodes) donde se tiene ,adicionalmente a la ciudad y estado a los que pertenece cada codigo postal, el geoposicionamiento en coordenadas de latitud y longitud.
Esto se trabajó utilizando Postgres 8.1 y se dejo como un procedimiento almacenado en PL/PGSQL (pl/pgsql store procedure).
La distancia entre dos coordenadas es la base para encontrar esa distancia, antes de utilizar la información hay que tomar en cuenta si la coordenada está dada en radianes o en grados. Vamos a dejar para algun blog matematico esa referencia, y veremos entonces la realización de este cálculo.
Al aquirir una base de datos con información de codigos postales, habran muchos campos por cada zipcode, pero tomemos como base una tabla con al menos codigo postal y su cordenada, es decir, la latitud y longitud que le corresponde en el globo terraqueo.
CREATE TABLE zipcodes(
zipcode text,
latitude double precision,
longitude double precision,
CONSTRAINT pk_zipcodes PRIMARY KEY (zipcode)
);
A este punto cargamos la información de la base de datos con que se cuente y entonces, como consejo, es buena idea entonces crear una tabla precargando la combinacion de codigo postal origen y codigo postal destino junto con la distancia existente entre ambos. Para ello utilizaremos la misma tabla para obtener los datos de ambos codigos postales y para ello utilizaremos la facilidad de los aliases para simular dos tablas, una con los codigos postales origen y otra con los codigos postales destino.
Adicionalmente vale la pena, segun la necesidad del caso, aplicar la restricción de que no se calcule la distancia si en ambos casos es el mismo codigo postal, así como establecer un radio o distancia máximo para calcular. Claro estas condiciones eran practicas para el enfoque que se necesitaba, sin embargo, cada caso puede variar y lo importante es la simulacion de ambas tablas y la formula del calculo de distancia que es de la siguente forma:
- a es la tabla de codigos postales de destino.
- b es la tabla de codigos postales de origen.
La formula es:
(3963*acos(sin(a.latitude/57.2958)*sin(b.latitude/57.2958)+cos(a.latitude/57.2958)*cos(b.latitude/57.2958)*cos(a.longitude/57.2958-b.longitude/57.2958)))
Ya con estas consideraciones entonces podemos preparar la consulta:
select a.zipcode as destination_zipcode, b.zipcode as origin_zipcode, (3963*acos(sin(a.latitude/57.2958)*sin(b.latitude/57.2958)+cos(a.latitude/57.2958)*cos(b.latitude/57.2958)*cos(a.longitude/57.2958-b.longitude/57.2958))) as distance
from zipcode a, zipcode b
where NOT (a.zipcode = b.zipcode) AND (3963*acos(sin(a.latitude/57.2958)*sin(b.latitude/57.2958)+cos(a.latitude/57.2958)*cos(b.latitude/57.2958)*cos(a.longitude/57.2958-b.longitude/57.2958))) <= 50
Y por ultimo generar la tabla final con el pre-calculo de distancias lista para utilizarse:
SELECT
a.zipcode as destination_zipcode,
b.zipcode as origin_zipcode,
(3963*acos(sin(a.latitude/57.2958)*sin(b.latitude/57.2958)+cos(a.latitude/57.2958)*cos(b.latitude/57.2958)*cos(a.longitude/57.2958-b.longitude/57.2958))) as distance
INTO TABLE zipcode_distances
FROM zipcode a, zipcode b
WHERE NOT (a.zipcode = b.zipcode)
AND (3963*acos(sin(a.latitude/57.2958)*sin(b.latitude/57.2958)+cos(a.latitude/57.2958)*cos(b.latitude/57.2958)*cos(a.longitude/57.2958-b.longitude/57.2958))) <= 50
Agregamos un par de índices para evitar recorridos secuenciales en la información
CREATE INDEX idx_zipcode_origin ON zipcode_distances (zipcode_origin);
CREATE INDEX idx_zipcode_destination ON zipcode_distances (zipcode_destination);
y con ello ya podemos realizar el cálculo de las distancias, espero les sea funcional, nos vemos pronto nuevamente!