CODEWITHSUNDEEP
postgresqlpostgis
cino21122
cino21122

Reputation: 73

postgres - ERROR: operator does not exist

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.

The query is this: 

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);

The PG function is this:

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;

Upvotes: 7

Views: 8824

Answers (7)

Java Dev
Java Dev

Reputation: 31

You need to create extensions under pg_catalog schema. So that those can be available globally. Please execute below commands:

  • drop extension if exists earthdistance;
  • drop extension if exists cube;
  • create extension cube schema pg_catalog;
  • create extension earthdistance schema pg_catalog;

Upvotes: 3

nittin aggarwal
nittin aggarwal

Reputation: 31

Here are the steps to solve this problem

  1. Run below command two check the extensions (cube & earthdistance)
SELECT * FROM pg_available_extensions ORDER BY "name"
  1. Run the below command if still both extensions (cube & earthdistance) showing in the list:
CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;
  1. create table and insert values
  • Create Location Table
CREATE TABLE location (
    id serial PRIMARY KEY,
    name varchar(50) NOT NULL,
    longitude double precision NOT NULL,
    latitude double precision NOT NULL
);
  • Insert Us Some Data (Sites around #CHA)
INSERT INTO location(name, latitude, longitude)
VALUES ('SupplyHog HQ', 35.0472780, -85.3071590)
,('Chickamauga Dam', 35.0975557,-85.2197027)
,('Five Points Mtn Biking', 34.851249, -85.423983)
,('Harrison Bay State Park', 35.179631, -85.114359)
,('Mojo Burrito', 35.0094040,-85.3275640)
,('Rock Creek', 35.0556150,-85.2803290);
  1. Run select query you will get the result
  • When creating points, it is point(long, lat)
SELECT *, point(-85.3078294, 35.0609500) <@> point(longitude, latitude)::point as distance 
FROM location
WHERE (point(-85.3078294, 35.0609500) <@> point(longitude, latitude)) < 10
ORDER BY distance;

Upvotes: 1

MaxTryk
MaxTryk

Reputation: 71

You need to qualify <@> operator like this:

OPERATOR(schema.<@>)

if the schema in which the extension is created is not listed in search_path:

SHOW search_path;

https://www.postgresql.org/docs/current/ddl-schemas.html

Upvotes: 1

marcinn
marcinn

Reputation: 546

The <@> operator is provided by earthdistance extension. You need to call create extension earthdistance; on production database.

Upvotes: 4

Steve Tauber
Steve Tauber

Reputation: 10179

Here are the exact commands:

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

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

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

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.

Upvotes: 12

Frank Bollack
Frank Bollack

Reputation: 25196

The point of error is the select statement in your stored procedure:

SELECT r.zip, point($2,$3) <@> point(g.lat, g.lon) AS distance
                            ^

At the marked position an operator is expected, but your operator is either not defined or has other argumet types. Postgres itself only knows of the <@ and @> containment operators. Look here for their explanation.

Can you please elaborate on what you are trying to achieve.

Upvotes: 0

Unreason
Unreason

Reputation: 12704

Are you sure that postgis was properly installed on the online server?

Upvotes: 1

Related Questions