postgres-错误:运算符不存在 [英] postgres - ERROR: operator does not exist

问题描述

同样，我有一个在本地可以正常运行的函数，但是将其在线运行会产生一个严重的错误……从有人指出我所传递的参数数量不正确的响应中得到一个提示，在这种情况下，我进行了仔细检查，以确保将5个参数传递给函数本身...

Again, I have a function that works fine locally, but moving it online yields a big fat error... Taking a cue from a response in which someone had pointed out the number of arguments I was passing wasn't accurate, I double-checked in this situation to be certain that I am passing 5 arguments to the function itself...

Query failed: ERROR: operator does not exist: point <@> point HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.

查询是这样的:

BEGIN; SELECT zip_proximity_sum('zc',                                                                                                                                                                  
    (SELECT g.lat FROM geocoded g                                                                                                                                                                              
    LEFT JOIN masterfile m ON g.recordid = m.id                                                                                                                                                                
    WHERE m.zip = '10050' ORDER BY m.id LIMIT 1),                                                                                                                                                             
    (SELECT g.lon FROM geocoded g                                                                                                                                                                              
    LEFT JOIN masterfile m ON g.recordid = m.id                                                                                                                                                                
    WHERE m.zip = '10050' ORDER BY m.id LIMIT 1),                                                                                                                                                             
    (SELECT m.zip FROM geocoded g                                                                                                                                                                              
    LEFT JOIN masterfile m ON g.recordid = m.id                                                                                                                                                                
    WHERE m.zip = '10050' ORDER BY m.id LIMIT 1)                                                                                                                                                              
    ,10);

PG功能是这样的:

CREATE OR REPLACE FUNCTION zip_proximity_sum(refcursor, numeric, numeric, character, numeric)
  RETURNS refcursor AS
$BODY$ 
    BEGIN 

        OPEN $1 FOR 
            SELECT r.zip, point($2,$3) <@> point(g.lat, g.lon) AS distance
            FROM
            geocoded g LEFT JOIN masterfile r ON g.recordid = r.id 
            WHERE (geo_distance( point($2,$3),point(g.lat,g.lon)) < $5)
            ORDER BY r.zip, distance;
        RETURN $1; 
    END; 
    $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

推荐答案

以下是确切的命令:

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 创建的.根据文档:

Note that points are created with LONGITUDE FIRST. Per the documentation:

将点视为(经度，纬度)，而不是点，因为经度更接近x轴的直观概念，而纬度更接近y轴.

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.

这是一个糟糕的设计...但这就是事实.

Which is terrible design... but that's the way it is.

这篇关于postgres-错误:运算符不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！