Вопрос по postgresql – PostgreSQL запрос широты и долготы

21

я имеюlatitude а такжеlongitude столбцы вlocation таблица в базе данных PostgreSQL, и я пытаюсь выполнить дистанционный запрос с помощью функции PostgreSQL.

Я прочитал эту главу руководства:

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

но я думаю, что я что-то там упустил.

Как мне это сделать? Есть ли еще примеры

Ваш Ответ

4   ответа
33

Initial setup (нужно запустить только один раз):

create extension cube;
create extension earthdistance;

И тогда запрос:

select (point(-0.1277,51.5073) <@> point(-74.006,40.7144)) as distance;

     distance     
------------------
 3461.10547602474
(1 row)

Обратите внимание, чтоpoints созданы сLONGITUDE FIRST, Подокументация:

Points are taken as (longitude, latitude) and not vice versa because longitude is closer to the intuitive idea of x-axis and latitude to y-axis.

Какой ужасный дизайн ... но так оно и есть.

Ваш вывод будет вmiles.

Gives the distance in statute miles between two points on the Earth's surface.

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceededearthdistance
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
8

что вы правильно установили модуль заземления, это даст вам расстояние в милях между двумя городами. Этот метод использует более простые точечные расстояния от Земли. Обратите внимание, что аргументами для point () являются сначала долгота, а затем широта.

create table lat_lon (
  city varchar(50) primary key,
  lat float8 not null,
  lon float8 not null
);

insert into lat_lon values
('London, GB', 51.67234320, 0.14787970),
('New York, NY', 40.91524130, -73.7002720);

select 
  (
  (select point(lon,lat) from lat_lon where city = 'London, GB') <@>
  (select point(lon,lat) from lat_lon where city = 'New York, NY')
  ) as distance_miles

distance_miles
--
3447.58672105301
Error: User Rate Limit ExceededCREATE EXTENSION cube; CREATE EXTENSION earthdistance;Error: User Rate Limit Exceeded
5

Формула Haversine

CREATE OR REPLACE FUNCTION distance(
    lat1 double precision,
    lon1 double precision,
    lat2 double precision,
    lon2 double precision)
  RETURNS double precision AS
$BODY$
DECLARE
    R integer = 6371e3; -- Meters
    rad double precision = 0.01745329252;

    φ1 double precision = lat1 * rad;
    φ2 double precision = lat2 * rad;
    Δφ double precision = (lat2-lat1) * rad;
    Δλ double precision = (lon2-lon1) * rad;

    a double precision = sin(Δφ/2) * sin(Δφ/2) + cos(φ1) * cos(φ2) * sin(Δλ/2) * sin(Δλ/2);
    c double precision = 2 * atan2(sqrt(a), sqrt(1-a));    
BEGIN                                                     
    RETURN R * c;        
END  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Входные данные указаны в градусах (например, 52,34273489, 6,23847), а выходные - в метрах.

21

й установке PostgreSQL. Вы должны установить его из каталога contrib.

Вы можете использовать следующую функцию для расчета приблизительного расстояния между координатами (в милях):

 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;
Чтобы использовать KM вместо миль, используйте константы 111.12 & amp; 92.215 для замены 69.1 & amp; 57,3
Можете ли вы объяснить здесь математику? Что представляют собой 69.1 и 57.3?
Я полагаю, что они являются константами, используемыми для преобразования широты и долготы в мили.
Почему подход сделать это с Postgresql, а не с Java, например, в функции
@FernandoPie Когда есть много строк, и расстояние является частьюWHERE В этом случае база данных фильтрует результаты быстрее, чем сначала переносит все строки в Java.

Похожие вопросы