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.
Best Answer
Possible explanation: you installed the extension in a schema that's missing from your current
search_path
.Diagnose with:
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 yoursearch_path
.And:
Related: