Hi, I was read discussion about spatial indexes in H2. I think basic problem is that H2 is missing data structure which can hold spatial data.
Most solutions are using geometry object stored serialized in database. On each query it needs to be deserialized, run logic and thrown away. This of course hurts performance. Other solution is to split area into pixels and store pixel numbers in 1:N relation. SQL WHERE condition is than used to filter data. But it needs HUGE sql queries. I am working on astronomical program which uses H2 database and Healpix sphere pixelization. I was facing problem howto handle sets with 1e10 pixel numbers. Solution was to compress data into RangeSet. RangeSet uses long[] array to store ranges. Even position is first number in range, odd position is last number in range. Values are sorted and binary search is used to find values. There is no object instance overhead, it is just array of primitives and consumes very little memory. To get more details visit project and source code: http://code.google.com/p/healpix-rangeset/ http://healpix-rangeset.googlecode.com/svn/trunk/healpix-rangeset/src/org/asterope/healpix/LongRangeSet.java My plan is to make RangeSet native SQL type in H2. It would make possible to pass RangeSet as JDBC param for WHERE cause. RangeSet would be also table column type. Some examples: This would save thousands of BETWEENs (and query parsing overhead) create table shops(name String, position long); select name from shops where position in ? (RangeSet JDBC param); More advanced, RangeSet is in other table create table towns (name String, area RangeSet); insert into towns(?,?) // String and RangeSet JDBC params //now select shops which are inside any city select s.name from shop s, town t where s.position inside t.area Or select area of countryside (is not covered by town) select complement(union(t.area)) from town t Most important is that RangeSet would operate directly on top of PageStore pages. There would be no deserialization overhead. It can also directly interact with index and query optimizer. Performance should be really good. I would like to know your opinions. Jan -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
