[SOLVED] PostgreSQL: How to get all points in certain radius

PostgreSQL: How to get all points in certain radius

I'm using Postgresql 9.3 and iv'e installed cube and earthdistance extensions.

I'm trying to follow this tutorial, so i have a simple events table with 4 fields: id, name, lat, lng.

Now i'm trying to run this query, to get all events within 1 km radius:

SELECT events.id, events.name FROM events WHERE earth_box(31.789225, 34.789612, 1000) @> ll_to_earth(events.lat, events.lng);

but i keep getting this error:

20:59:34 Kernel error: ERROR:  function earth_box(numeric, numeric, integer) does not exist
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

So i ran the same thing with casting:

  SELECT events.id, events.name FROM events WHERE earth_box(CAST(31.789225 AS float8), CAST(34.789612 AS float8), 1000) @> ll_to_earth(events.lat, events.lng);

and i get :

   21:16:17 Kernel error: ERROR:  function earth_box(double precision, double precision, integer) does not exist
                                                              ^
      HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Solution

O.K, finally after couple of hours i got it :)

Apparently the tutorial i was working with was outdated, the correct syntax is this:

SELECT events.id, events.name FROM events WHERE earth_box(ll_to_earth(31.789225,34.789612), 1000) @> ll_to_earth(events.lat, events.lng);