PostgreSQL: How to get all points in certain radius

Question

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.

Tomer · Accepted Answer · 2014-10-01 18:36:30Z

6

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);

answered Oct 1, 2014 at 18:36

Tomer

17.9k17 gold badges82 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Johann du Toit Over a year ago

Ah oopsie that's my bad. Will try to update it tonight or this tomorrow.

Joe Freeman Over a year ago

@JohannduToit, you haven't updated your penultimate query (SELECT events.id, ...). Thanks for the tutorial though :)

roconmachine Over a year ago

ll_to_earth not treated as a function. bad luck. :(

anduplats Over a year ago

Please provide us the table creation script. Ii wanna see the datatypes

Tomer Over a year ago

@anduplats You realize this is a 5 years old post?!

Collectives™ on Stack Overflow

PostgreSQL: How to get all points in certain radius

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related