1

I need to find is point located in a given radius. Now I have a two choices, first is to write my own algorithm for it(or using existing library) second is use postgresql earth_box utility and I can select it directly from db, using stored procedure. What is pros/cons of both in context of web application?

Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
Slow Harry
  • 1,857
  • 3
  • 24
  • 42

1 Answers1

1

I would think that using the earth_box procedure in postgres would be better for the following reasons:

  • The database already contains the data and procedures to work with it
  • The database server , given a properly indexed table, should be quite efficient at executing a spatial query on its own spatial data
  • Using the server there's no need to query for the spatial information, transfer it to wherever you're processing it, creating a tree structure and other overhead (ties into the first bullet)
  • You're using code that already exists and, presumably, has been thoroughly tested and vetted
  • You could reuse the code in other server-side SQL from a broader number of applications such as reporting

I would definitely suggest trying the earthbox approach first and going with a custom solution only if the earthbox absolutely sucks performance-wise.

Here's a more succinct meta-reasoning from a blog post you may want to check out:

[...] the earthbox function allows us to perform a simple compare to find all records in a certain radius. This is done by the function by returning the great circle distance between the points, a more thorough explanation is located at http://en.wikipedia.org/wiki/Greatcircle.

(By meta-reasoning I mean that the simplicity of the use of earthbox makes using it a no-brainer.)

Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
  • Thank you for your contribution, I am a bit experienced with using earth box, and find it easy and simple. Now I would like to know scala runtime calculation advantages. – Slow Harry Mar 13 '14 at 05:34
  • 1
    @SlowHarry: Ha. I am curious too. I found no head-to-head comparison in Google searches. If you really want to know, you may have to write it yourself. Here's a Java function (I didn't find Scala source) that implements the GreatCircle algo you may want to cehck out: http://introcs.cs.princeton.edu/java/12types/GreatCircle.java.html By the way, had you mentioned that you already knew earth_box and were interested in performance metrics I would not have bothered to try and answer the question. – Paul Sasik Mar 13 '14 at 05:40
  • It is just a distance basic function but, task is more complex, there is many points and brute force all of them will be too slow, most interesting is graph applied to find it faster, this implementation should be applied and compare it with earth_box. – Slow Harry Mar 13 '14 at 05:54
  • This comparison is also will be useful when programmers who choose database, for instance if it is quite good they can use MySQL, Redis or Mongodb, if it totally sucks, they can say: "Ok, prostgresql is perfect for this" – Slow Harry Mar 13 '14 at 06:02
  • @SlowHarry: Sounds like you've got your answer. :-) – Paul Sasik Mar 13 '14 at 06:06