It is more complex than computed column index. It requires to use an index that can manage more than < > = operators with plugable type.
PostGis first define operators related to GIST (with storage of bounding box) : http://www.postgresql.org/docs/9.2/static/sql-createopclass.html FOR TYPE geometry USING GIST AS STORAGE box2df, OPERATOR 3 && , and define GIST API Implementation for this DataType (Geometry) : FUNCTION 1 geometry_gist_consistent_2d (internal, geometry, int4), FUNCTION 2 geometry_gist_union_2d (bytea, internal), FUNCTION 3 geometry_gist_compress_2d (internal), FUNCTION 4 geometry_gist_decompress_2d (internal), FUNCTION 5 geometry_gist_penalty_2d (internal, internal, internal), FUNCTION 6 geometry_gist_picksplit_2d (internal, internal), FUNCTION 7 geometry_gist_same_2d (geom1 geometry, geom2 geometry, internal); Gist would allow us to plug several kind of index into H2. Then to link a predicate function to a spatial index, this is an alias that rewrite original call : > CREATE ALIAS ST_Intersects AS '$1 && $2 AND _ST_Intersects($1,$2)' > GIST Operator overlap && is then recognized (as $1==$2 should be) and applied in Parser. I don't read/understand whole h2 sources then I'm not currently able to visualize the rework to be done for this feature. Le jeudi 13 juin 2013 15:54:43 UTC+2, Noel Grandin a écrit : > > I would suggest working towards improve the current function-based-index > functionality: > http://h2database.com/html/features.html#computed_columns<http://h2database.com/html/features.html?highlight=computed&search=computed#computed_columns> > so that we could do away with the need to have computed columns, so that > we could do something like: > CREATE TABLE ADDRESS( > ID INT PRIMARY KEY, > NAME VARCHAR > ); > CREATE INDEX IDX_U_NAME ON ADDRESS(UPPER(NAME)); > > The code you'd need to modify would live in the following classes and > methods: > org.h2.command.Parser > org.h2.command.ddl.CreateIndex > org.h2.table.RegularTable#addIndex > org.h2.index.PageBtreeIndex > org.h2.index.PageDelegateIndex (optional, only needed if the PK has to > be a functional index) > > From there, you should be able to map your geometry indexes onto our > regular indexes using Hilbert curves ( > http://en.wikipedia.org/wiki/Hilbert_R-tree) > and implement your geometry primitives on top of that. > > > > On 2013-06-13 14:55, bocher wrote: > > > Do you have a plan to support "function based indexes" ? I known you are > very busy but we are ready to help you and propose a patch to H2. So if you > haven't any plan maybe you can give some ways to do that (suggestions or > best approach...). > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out.
