PostgreSQL earthdistance Installation Mac

Question

The installation of earthdistance module in Postgresql 9.6.5 on OSX El Capitan 10.11.6. PG is already installed, working. I created the extension, which installs cube first:

rich=# CREATE EXTENSION earthdistance CASCADE;
NOTICE:  installing required extension "cube"
CREATE EXTENSION

Restarted my box. Opened up psql, chose the proper database, asked about functions (\df):

    rich=# \df
                                                                                     List of functions
     Schema |        Name        | Result data type |           Argument data types            |  Type
    --------+--------------------+------------------+------------------------------------------+--------
     public | cube               | cube             | cube, double precision                   | normal
     public | cube               | cube             | cube, double precision, double precision | normal
     public | cube               | cube             | double precision                         | normal
     public | cube               | cube             | double precision, double precision       | normal
     public | cube               | cube             | double precision[]                       | normal
     public | cube               | cube             | double precision[], double precision[]   | normal
     public | cube_cmp           | integer          | cube, cube                               | normal
     public | cube_contained     | boolean          | cube, cube                               | normal
     public | cube_contains      | boolean          | cube, cube                               | normal
     public | cube_coord         | double precision | cube, integer                            | normal
     public | cube_coord_llur    | double precision | cube, integer                            | normal
     public | cube_dim           | integer          | cube                                     | normal
     public | cube_distance      | double precision | cube, cube                               | normal
     public | cube_enlarge       | cube             | cube, double precision, integer          | normal
     public | cube_eq            | boolean          | cube, cube                               | normal
     public | cube_ge            | boolean          | cube, cube                               | normal
     public | cube_gt            | boolean          | cube, cube                               | normal
     public | cube_in            | cube             | cstring                                  | normal
     public | cube_inter         | cube             | cube, cube                               | normal
     public | cube_is_point      | boolean          | cube                                     | normal
     public | cube_le            | boolean          | cube, cube                               | normal
     public | cube_ll_coord      | double precision | cube, integer                            | normal
     public | cube_lt            | boolean          | cube, cube                               | normal
     public | cube_ne            | boolean          | cube, cube                               | normal
     public | cube_out           | cstring          | cube                                     | normal
     public | cube_overlap       | boolean          | cube, cube                               | normal
     public | cube_size          | double precision | cube                                     | normal
     public | cube_subset        | cube             | cube, integer[]                          | normal
     public | cube_union         | cube             | cube, cube                               | normal
     public | cube_ur_coord      | double precision | cube, integer                            | normal
     public | distance_chebyshev | double precision | cube, cube                               | normal
     public | distance_taxicab   | double precision | cube, cube                               | normal
     public | earth              | double precision |                                          | normal
     public | earth_box          | cube             | earth, double precision                  | normal
     public | earth_distance     | double precision | earth, earth                             | normal
     public | g_cube_compress    | internal         | internal                                 | normal
     public | g_cube_consistent  | boolean          | internal, cube, smallint, oid, internal  | normal
     public | g_cube_decompress  | internal         | internal                                 | normal
     public | g_cube_distance    | double precision | internal, cube, smallint, oid, internal  | normal
     public | g_cube_penalty     | internal         | internal, internal, internal             | normal
     public | g_cube_picksplit   | internal         | internal, internal                       | normal
     public | g_cube_same        | internal         | cube, cube, internal                     | normal
     public | g_cube_union       | cube             | internal, internal                       | normal
     public | gc_to_sec          | double precision | double precision                         | normal
     public | geo_distance       | double precision | point, point                             | normal
     public | latitude           | double precision | earth                                    | normal
     public | ll_to_earth        | earth            | double precision, double precision       | normal
     public | longitude          | double precision | earth                                    | normal
     public | sec_to_gc          | double precision | double precision                         | normal
    (49 rows)

So it seems the extension is loaded fine. As a test, I'm using the following simple command:

SELECT point(43.664828,-79.341620) <@> point(44.418390, -80.096013)

In psql I am not receiving any feedback. In Postico 1.3.2 (a PG client), I get the following error:

ERROR:  operator does not exist: point <@> point
LINE 1: SELECT point(43.664828,-79.341620) <@> point(44.418390, -80....
                                           ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

It seems the module is not installed, as per the feedback. I am expecting this to work, as the extension is installed properly, and I am using the proper syntax for the earth_distance function. I have tried other queries with the same type of error, indicating non-recognition of operators.

What am I doing wrong?

Answer 1

Solved. Turns out you have to choose the database for the extension to apply to that specific database. Just entering psql is inside the application level, so the CREATE EXTENSION earthdistance CASCADE is still blind to any database apparently. So enter psql using the psql <dbname> syntax and then apply the CREATE EXTENSION earthdistance CASCADE for it to apply to your chosen database.