2

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.

2
  • 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. Commented Mar 6, 2019 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. Commented Mar 7, 2019 at 1:46

1 Answer 1

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:

8
  • 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! Commented Mar 6, 2019 at 2:19
  • Hrmpf. So anything suspicious from this query maybe? SELECT oprnamespace::regnamespace, oprleft::regtype, oprname, oprright::regtype FROM pg_operator WHERE oprname = '<@>'; Commented Mar 6, 2019 at 3:07
  • Yeah that comes back with temp point <@> point so the operator is loaded/setup. No clue why it's not working. Commented Mar 6, 2019 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? Commented Mar 6, 2019 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. Commented Mar 6, 2019 at 13:06

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.