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.
Show quoted text
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/