PostgreSQL纬度经度查询

本文介绍了PostgreSQL纬度经度查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在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:

www.postgresql/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;