Обсуждение: R-Trees in PostgreSQL

Hi,

I'd like to create an R-Tree index on two numeric columns.  As far as I
know, PostgreSQL supports R-Trees via the GiST index class for some
spatial types (box and the like).  When I create a GiST index on two
numeric columns, I get the error message:

  ERROR:  data type numeric has no default operator class for access
  method "gist"
  HINT:  You must specify an operator class for the index or define a
  default operator class for the data type.

I'd like to know what kind of functions I have to implement for a R-Tree
index on numeric columns, particularly if that can be done in PL/PGSQL
or if I have to fall back to C.

Or maybe there already exists a solution?  From web searches I gather
that PostgreSQL at one time supported R-Trees natively, but that it was
dropped in favor of GiST.  I couldn't find anything in the contrib
directory.

Thanks,
Viktor

On Tue, 2009-11-03 at 00:25 +0100, Viktor Rosenfeld wrote:
> I'd like to know what kind of functions I have to implement for a R-Tree
> index on numeric columns,

NUMERIC is scalar, so an R-Tree doesn't make much sense. You can install
btree_gist (a contrib module) to be able to use numeric columns as part
of a GiST index.

If you have more complex spatial data, you should look into PostGIS.

Regards,
    Jeff Davis

Also for one-dimensional ranges, consider contrib/seg

P.

On Mon, Nov 2, 2009 at 4:41 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> On Tue, 2009-11-03 at 00:25 +0100, Viktor Rosenfeld wrote:
>> I'd like to know what kind of functions I have to implement for a R-Tree
>> index on numeric columns,
>
> NUMERIC is scalar, so an R-Tree doesn't make much sense. You can install
> btree_gist (a contrib module) to be able to use numeric columns as part
> of a GiST index.
>
> If you have more complex spatial data, you should look into PostGIS.
>
> Regards,
>        Jeff Davis
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Mon, Nov 2, 2009 at 4:41 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> On Tue, 2009-11-03 at 00:25 +0100, Viktor Rosenfeld wrote:
>> I'd like to know what kind of functions I have to implement for a R-Tree
>> index on numeric columns,
>
> NUMERIC is scalar, so an R-Tree doesn't make much sense. You can install
> btree_gist (a contrib module) to be able to use numeric columns as part
> of a GiST index.

It sounds like what you're trying to do needs an "expression index" so
you can construct a data type which does support gist indexes out of
your two numeric columns. You could do something like create index i
on (point(col1,col2)) though I think you might have to actually make a
"box" instead. Alternatively you could look at the "cube" contrib
module. As far as i know all of these actually work with doubles
though, so you'll lose precision.

--
greg

On Mon, Nov 02, 2009 at 08:10:47PM -0800, Greg Stark wrote:
> As far as i know all of these actually work with doubles
> though, so you'll lose precision.

IEEE 754 floating point numbers (i.e. float8 or "double precision" in
PG) are defined to have a 52 bit significand and hence can store integer
values up to 2^52 without loss of precision.  How good PG is numerically
I'm not sure, but you should be able to get pretty close to this range.

--
  Sam  http://samason.me.uk/