21

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

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

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

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

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

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

  • Error: User Rate Limit Exceededearthdistance

    от
  • Error: User Rate Limit Exceeded

    от
  • Error: User Rate Limit ExceededCREATE EXTENSION cube; CREATE EXTENSION earthdistance;Error: User Rate Limit Exceeded

    от
  • Error: User Rate Limit Exceeded

    от
  • Error: User Rate Limit Exceeded

    от
  • Error: User Rate Limit Exceeded

    от
  • Я полагаю, что они являются константами, используемыми для преобразования широты и долготы в мили.

    от
  • Почему подход сделать это с Postgresql, а не с Java, например, в функции

    от
  • @FernandoPie Когда есть много строк, и расстояние является частьюWHERE В этом случае база данных фильтрует результаты быстрее, чем сначала переносит все строки в Java.

    от
  • Чтобы использовать KM вместо миль, используйте константы 111.12 & amp; 92.215 для замены 69.1 & amp; 57,3

    от
  • Можете ли вы объяснить здесь математику? Что представляют собой 69.1 и 57.3?

    от
  • 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;
    

  • 5

    Более точная версия ответа @ strkol с использованием

    Формула 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), а выходные - в метрах.

  • 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
    

  • 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.