1

Using PostgreSQL 11 on Windows.

Both cube and earthdistance installed and verified by pg_available_extensions.

Restarted PostgreSQL.

[42883] ERROR: operator does not exist: point <@> point.

Setup:

CREATE EXTENSION IF NOT EXISTS cube SCHEMA temp;
CREATE EXTENSION IF NOT EXISTS earthdistance SCHEMA temp;

Tried the following code from StackOverflow

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

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

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

Throws:

[42883] ERROR: operator does not exist: point <@> point.

Extensions installed and Postgres was restarted.

select * from pg_available_extensions where name IN ('cube', 'earthdistance');

cube            1.4     data type for multidimensional cubes
earthdistance   1.1     calculate great-circle distances on the surface of the Earth

Is this because of PostgreSQL v11 on Windows? Yes, Lat/Long in correct order (long is first).

Note: Table F.6 Point-based Earthdistance Operators

Update in regards to: schema / search path:

SELECT extname, extnamespace::regnamespace FROM pg_extension
WHERE  extname IN ('cube', 'earthdistance');

cube            temp
earthdistance   temp
SHOW search_path;

temp

Note: I CREATE EXTENSION hstore SCHMEA temp; and can use hstore and its operators. So doesn't seem to be all extensions.

  • Just wanted to turn you onto PostGIS if you never heard of it. It does a lot more, and the indexing is better than Earthdistance. – Evan Carroll Mar 6 at 16:52
  • Thanks @EvanCarroll PostGIS is a little too much for this particular usage. Only sparingly need to use earthdistance <@>. If the project evolves to needing more geo, PostGIS is the next option. – thames Mar 7 at 1:46
1

Possible explanation: you installed the extension in a schema that's missing from your current search_path.

Diagnose with:

SELECT extname, extnamespace::regnamespace FROM pg_extension
WHERE  extname IN ('cube', 'earthdistance');

SHOW search_path;

Is the schema of the extensions in your current search_path? If not, there is your explanation. Either install to a different schema or adapt your search_path.

And:

SELECT oprnamespace::regnamespace, oprleft::regtype, oprname, oprright::regtype
FROM   pg_operator
WHERE  oprname = '<@>';

Related:

  • Unfortunately they are in the same schema and search path. There is only one schema and that schema is in the search path where the extension was installed. I wish that was the answer! – thames Mar 6 at 2:19
  • Hrmpf. So anything suspicious from this query maybe? SELECT oprnamespace::regnamespace, oprleft::regtype, oprname, oprright::regtype FROM pg_operator WHERE oprname = '<@>'; – Erwin Brandstetter Mar 6 at 3:07
  • Yeah that comes back with temp point <@> point so the operator is loaded/setup. No clue why it's not working. – thames Mar 6 at 3:50
  • Ok. This is weird. Now it's working. I didn't try it before I tried your pg_operator query, but I did after and now it seems to work. Not sure why now. Pg wasn't restarted. I did drop/create everything from scratch, but when I tried it after drop/create I received the same error as before. The only difference is that I left the computer for about 45 min. Not sure how that would affect anything especially when I restarted Pg before and it still didn't work. Does pg_operator refresh any system metadata? – thames Mar 6 at 3:57
  • pg_operator is a system catalog (a plain table). It does not "do" anything. The most obvious explanation would be that the failing session ran with a different search_path for some reason. Maybe a setting per role? Details in the 2nd link above. – Erwin Brandstetter Mar 6 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.