Postgres Pro
Downloads
Home   >   mailing lists

Re: earthdistance - Mailing list pgsql-general

From Olivier Chaussavoine
Subject Re: earthdistance
Date
Msg-id CAKJCXwqba+7JWEZQzhYxn8qnGq0xBou8TdUo6hFpsVupJndQFw@mail.gmail.com
Whole thread Raw
In response to Re: earthdistance  (Bruno Wolff III <bruno@wolff.to>)
Responses Re: earthdistance  (Bruno Wolff III <bruno@wolff.to>)
List pgsql-general
Tree view
I also look at cube extension, but the built in type box - a couple of points - does not require any extension and has a GIST index. It can be used to represent a rectangle on the domain [-PI/2,+PI/2[*[-PI,PI[. If the extension was providing a function get_rect_from_cap() giving the smallest rectangle of this domain containing a spherical cap, this rectangle could be used as you pointed out to reduce the set of rows where the earth distance need to be computed to know if a point A belongs to the cap. The operator && (box overlaps box) could be used if the point A is converted to box(A,A). Do you think this function get_rect_from_cap() could be usefull?


2013/8/11 Bruno Wolff III <bruno@wolff.to>
On Sat, Aug 10, 2013 at 12:18:48 +0200,
  Olivier Chaussavoine <olivier.chaussavoine@gmail.com> wrote:
I did not found any geographic indexing with earthdistance, and need it.

Some of the earthdistance stuff is based on cube which does have indexing. I don't know how well that indexing works and it might be pretty bad in practice.


The need I have is simple:
"is the distance between two (lat,long) positions less than X km?"
the model used for the shape of the earth should be related to the
precision of lat,lon, and most sources are imprecise. The spherical model
should be enough.

You might just be looking at this wrong. You don't have an index on the distance. What you want is to find points within a cube that is big enough to include all of the points of interest and then double check the returned points to make sure they are really within the expected range. You can calculate the size of the cube needed based on the distance and the radius of the earth. I don't remember if there was a built in function for that, since it's been such a long time since I looked at it.



--
Olivier Chaussavoine

pgsql-general by date:

Previous
From: Moshe Jacobson
Date:
Subject: pg_extension_config_dump() with a sequence
Next
From: Tom Lane
Date:
Subject: Re: pg_extension_config_dump() with a sequence