Dear Chris,

i m subscrided to http://tech.groups.yahoo.com/group/Firebird-Architect/
but i don't see any thread speaking about rtree ?

the thing we are trying to do is a very simple query like :

Select xxx from tableA where FieldA > xxx and FieldA < yyyy and FieldB >
www ....

this kind of query is very common nowadays ... in little table (< 1
millions rows) the perf are acceptable but as soon as the table grow,
for exemple on big table (+20 000 000 rows), just doing where FieldA >
xxx take a very (very) long time to return (around 10 secondes to fews
minutes)

on the same data, on the Sqlite3 rtree index, it's take 10 ms!

i already have done a demo to benchmark the queries :
http://sourceforge.net/projects/alcinoe/files/alsqlbenchmark/1.00/ALSQLBenchmark_1_00.zip/download

this tools will create the table on Firebird or on Sqlite3 using Rtree
(on mysql it's also possible but i don't know how to write the SQL using
their GIS), and permit you to do some benchmark ....

stéphane

On 12/22/2010 10:37 AM, chris.waldmann wrote:
>
>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, Vander Clock Stephane
> <svanderclock@...> wrote:
> >
> >
> > > Hello Ann
> > >
> > > PostGIS and OpenGIS are very complete solutions for spatial data.
> > >
> > > In an abstract way, queries for spatial data are multi dimensional
> > > range queries for areas or points. And R*Tree indexes are well suited
> > > for such queries.
> > >
> > > And multi dimensional range queries are very common on search
> > > platforms, e.g searching for a 3 to 5 year old car with a kilometer
> > > reading of 50'000km to 100'000km.
> > >
> > > So I vote for an additional index kind!
> > >
> > > To be open for the future, R*Tree indexes could be implemented
> > > as a variant of generalized search trees (GiST).
> > >
> > > Is this a feature that can be discussed in Firebird architect list?
> > >
> > > Christian
> > >
> > >
> >
> > I completely agree ! actually i face the need to split my database in 2
> > database engines just because i need RTree indexes ...
> > PostgresSQL is probably right (i not try it yet), but it's a very heavy
> > system, it's hard like oracle without the power of oracle !
> >
> > so this feature in Firebird could be defintivelly be a greatest
> features !
> >
> > and also i thing we can easily found some users to sponsor such
> > developpement
> >
> > to finish, when i see how sqlite3 implemente their rtree index (only
> > with additionnal table), and with few hundreds of lines of code it's
> not
> > look like a "titan" project ...
> >
> > stéphane
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> Bonjour Stéphane
>
> To have some examples and arguments on the architecture list, can you
> give me some descriptive examples of your range queries.
>
> And also the SQL script of the multidimensional Rtree-Query in SQLite
> you use for the benchmark.
> My be there are some hours left during Christmas holiday to do some
> brain gym with stored procedures and bring up the request for
> additional index types (Rtree, with or without GiST) in the
> architecture forum.
>
> Do you follow the Firebird-architecture group?
>
> Happy holidays
> Christian
>
>


[Non-text portions of this message have been removed]