Postgres - ERROR: operator does not exist
Again, I have a function that works fine locally, but moving it around the web gives a big bold error ... Accepting the answer from an answer where someone pointed out the number of arguments I was passing was not accurate, I double checked in this situation to be sure I pass 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 request is like 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 as follows:
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;
a source to share
The operator <@>
provides the extension earthdistance
. You must call create extension earthdistance;
on the production database.
- More information on Earthdistance: http://www.postgresql.org/docs/9.1/static/earthdistance.html
- Checking for available extensions: http://www.postgresql.org/docs/9.1/static/view-pg-available-extensions.html
a source to share
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)
Please note that points
are created with LONGITUDE FIRST . In the documentation:
Points are taken as (longitude, latitude) and not vice versa, because longitude is closer to the intuitive idea of the x-axis and latitude along the y-axis.
What a terrible design ... but just the way it is.
a source to share
The point of error is the select statement in the stored procedure:
SELECT r.zip, point($2,$3) <@> point(g.lat, g.lon) AS distance
^
An operator is expected at the marked position, but your operator is either undefined or has different argumet types. Postgres itself only knows about the containment operators <@
and @>
. Look here for an explanation.
Could you please tell us what you are trying to achieve.
a source to share