Thread: 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/