PostgreSQL纬度经度查询 [英] PostgreSQL latitude longitude query

问题描述

我在PostgreSQL数据库的location表中有latitude和longitude列， 并且我正在尝试使用PostgreSQL函数执行距离查询.

i have latitude and longitude columns in location table in PostgreSQL database, and I am trying to execute distance query with a PostgreSQL function.

我阅读了本手册的这一章:

I read this chapter of the manual:

https://www.postgresql.org/docs/current/static/earthdistance.html

但是我想我在那里丢失了一些东西.

but I think I'm missing something there.

我该怎么做?是否有更多示例可用

How should I do that? Are there more examples available

推荐答案

此模块是可选的，并且未安装在默认的PostgreSQL安装中.您必须从contrib目录安装它.

This module is optional and is not installed in the default PostgreSQL instalatlion. You must install it from the contrib directory.

您可以使用以下函数来计算坐标之间的近似距离(以英里为单位):

You can use the following function to calculate the approximate distance between coordinates (in miles):

 CREATE OR REPLACE FUNCTION distance(lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT) RETURNS FLOAT AS $$
DECLARE                                                   
    x float = 69.1 * (lat2 - lat1);                           
    y float = 69.1 * (lon2 - lon1) * cos(lat1 / 57.3);        
BEGIN                                                     
    RETURN sqrt(x * x + y * y);                               
END  
$$ LANGUAGE plpgsql;

这篇关于PostgreSQL纬度经度查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！