Re: Working on H2 spatial, built-in or not

[Nicolas Fortin (OrbisGIS)]

We are using H2 in an OSGi environment.

 I search an alternative to CREATE ALIAS in order to provide to h2 the java Class.

Maybe by calling something like org.h2.util.Utils.addUserClass("Class name",Class<?> userClass);

What do you think about it ?

Thank you

 

[Noel Grandin]

On Tuesday, 16 April 2013, Nicolas Fortin (OrbisGIS) wrote:

Using custom datatype and functions through "CREATE DOMAIN" and "CREATE ALIAS" leads to call theses functions to all new local H2 DataBase and existing H2 DataBase connection with admin rights..

That should not be a problem because they will only be defined for a specific db file. 

 

 

I think about creating a custom Driver for spatial ex:"org.h2spatial.Driver" then catch admin connection on local h2 DataBase, but it must not be a great idea ?

For now, it might be easier for you to simply wrap h2 in a new osgi bundle and deploy it separately from normal h2. 

But it's not serious which way you chose now, can always be changed later. 

 

You were talking about MVStore and custom data type (de)serialisation, but I don't find samples, and I don't find a way to feed a Class or object factory. But anyway it seems that this storage is not yet used in H2. May be I should work on the snapshot release ?

This stuff is still very new. I don't know that much about it, Thomas is still working on it. But if you want use it, you should probably be using the nightly builds. 

 

Final words are, what do you think is the best strategy ? Working with you on the trunk to incorporate h2 spatial in native or continue to stick with version 1.3.171 and build a overloaded Driver for spatial 

I can't say for certain because Thomas is project leader, but I think it would be nice to integrate this in trunk :)

There is one problem, your license (GPL) is not compatible with ours, so you need to relic ensue before contributing the code. 

Great to have you aboard, always nice to see h2 being used in new and interesting ways!

 

--
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 h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Noel Grandin]

On Thursday, 18 April 2013, Nicolas Fortin (OrbisGIS) wrote:

We are using H2 in an OSGi environment.

 I search an alternative to CREATE ALIAS in order to provide to h2 the java Class.

Maybe by calling something like org.h2.util.Utils.addUserClass("Class name",Class<?> userClass);

Are yes, I see your problem. In an osgi context the db does not know which class loader to use when loading the alias. 

For now I suggest you patch such a method in yourself. If you can't go that, I will send you a patch next week. (I'm at a conference this week)

 

 

 

[Nicolas Fortin (OrbisGIS)]

Thank your for the answer. I will work on the patch, trying to not break anything. And I will send another patch for maven local build that do not read the good version of the library (got stuck on home made build system).

I will release also H2 Spatial OSGi integration and unit testing projects also.

For license I will see with my boss Erwan. Spatial feature would require at least one library dependency JTS topology suite, is this a problem for trunk development ? May be an optional build to enable spatial feature ?

Best regards,

-Nicolas

[Nicolas Fortin (OrbisGIS)]

Ok, the patch is done for OSGi and it works well.

I should write Unit test but I don't find unit test on OSGi activator. I'm using IntelliJ, how do you create a valid project with H2 source ?

How it works :

The H2 Bundle Activator register into org.h2.Util.Utils an object that handle a specific kind of binary class name. This specific binary class name follow this structure

"BundleSymbolicName:BundleVersion:JavaBinaryName"

I don't find a standard way to describe such kind of structure. I could use Bundle Id (int) but this identifier can change after framework restart, and it is too opaque.

This extended name allow OSGi developers to still using the "CREATE ALIAS" Sql query without doing any Java operation, neither deal with h2 packages.

I'm working now on H2 trunk, still trying to figure out what is the best strategy for h2 spatial.

Thank for you support.

-Nicolas

[Noel Grandin]

On Thursday, 18 April 2013, Nicolas Fortin (OrbisGIS) wrote:

For license I will see with my boss Erwan. Spatial feature would require at least one library dependency JTS topology suite, is this a problem for trunk development ? May be an optional build to enable spatial feature ?

Yeah, that would be a problem for us ( I think, Thomas please correct me if I'm wrong). 

I don't think we're going to be able to accept your code into the core. So you should probably plan on shipping it as something that wraps H2. 

But we're still happy to help if you get stuck :)

 

 

 

[Brian Craft]

This is interesting. Is the entire R tree loaded into memory at once, or can it be partially loaded as needed?

I have seen some blog posts about using mysql spatial features, which found that it loads the whole R tree into memory, and consequently has some very poor performance features for large tables, or large numbers of tables.

UCSC has developed a different technique for genomic spatial queries, where they calculate something like a fixed R tree bin ID (fixed meaning it's the same bins for every dataset, it doesn't create bins based on the data) for each feature, and store the bin ID in a B-tree indexed column. I believe this doesn't require loading the whole index into memory at once, though it does require some application code to compose the queries on the bin column, because they are moderately complex, and can't be encapsulated easily into a sql function or view.

[Thomas Mueller]

Hi,

I'm still catching up on emails as I was on a short vacation the last few days.

> Spatial feature would require at least one library dependency JTS topology suite, is this a problem for trunk development ?

As far as I see, the JTS is LGPL. But as long as the source code of JTS is not included within H2 it should be fine I guess. It would be a runtime dependency only, so the users would have to deal with the license issue. Long term, I would like use the R-Tree in the MVStore; but it doesn't have many features yet and is not yet integrated with the database itself, so I suggest to not use it for your work yet.

About the R-Tree in the MVStore: well, see for yourself: http://h2database.com/html/mvstore.html#r_tree - it's not fully loaded in memory.

Regards,

Thomas

--

[Nicolas Fortin (OrbisGIS)]

Hi,

About my patch published on a previous post, it needs a full OSGi context to be entirely covered by unit test, but by registering a dummy class factory it should cover some additional line of codes. I will try to figure out how to write unit test in h2.

[Nicolas Fortin (OrbisGIS)]

Hi,

When I use CREATE DOMAIN and use this domain when creating the table, using ResultSetMetaData.getColumnTypeName I don't get the domain name but the OTHER (if I use create domain geometry as JAVA_OBJECT). How can I retrieve the domain name of my column ?

Thx

[Noel Grandin]

Interesting.
The PostgreSQL JDBC driver seems to return the domain name from
ResultSetMetaData#getColumnTypeName()
See here:
https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/test/jdbc2/ResultSetMetaDataTest.java#L223

I wonder if we should be doing the same thing?

Hmmm, some quick hacking around inside our code reveals that it is not
an easy fix.
We appear not to have the original domain type anymore by the time the
result set is returning.

[Thomas Mueller]

Hi,

You currently can't as the domain name is not stored.

What you could do as a workaround is add a comment on the column, as you can retrieve that, in the column metadata (information_schema.columns). Or, as an alternative, add a "fake" column constraint on the domain. The column constraint is propagated to the column, and can be read as well.

Regards,

Thomas

--

[Nicolas Fortin (OrbisGIS)]

Hi,

I try to stick with OGC Simple Feature Access using their unit test called Conformance Test. Regarding theses test I should create and/or update some tables when a table contain a geometry column. Creation of table must not be different of this one :

CREATE TABLE lakes (

fid INTEGER NOT NULL PRIMARY KEY,

name CHARACTER VARYING(64),

shore POLYGON);

Then a table geometry_columns should be created/updated automatically by referencing the column in the lakes table here.

There is a way to register a listener on h2 in order to get a call-back when someone call "CREATE TABLE" and "ALTER TABLE", or when a domain is used ?

-Nicolas

[Nicolas Fortin (OrbisGIS)]

For information H2 pass successfully the two first unit test series. Unit test can be seen here

DataSet:
https://github.com/nicolas-f/H2GIS/blob/h2spatial-osgi-snapshot/h2spatial/src/test/resources/org/h2spatial/ogc_conformance_test2.sql
UnitTest:
https://github.com/nicolas-f/H2GIS/blob/h2spatial-osgi-snapshot/h2spatial/src/test/java/org/h2spatial/OGCConformance2Test.java

Le vendredi 26 avril 2013 07:13:59 UTC+2, Thomas Mueller a écrit :

[Nicolas Fortin (OrbisGIS)]

Hi,

I succeed on creating an IntelliJ project for H2, then it is easier to me to to dev in H2.

I have some issues with h2 java object field, after serialization the object size overhead is too high. I think about extending the OTHER type this way :
CREATE DOMAIN Geometry AS OTHER('org.h2spatial.ValueGeometry.FromBytes');

The optional parameter is a static method that return a class that extends 'org.h2.value.Value' in order to convert byte[] array into the appropriate field object.

What do you think about this solution ?

-Nicolas

Le mardi 16 avril 2013 17:13:06 UTC+2, Nicolas Fortin (OrbisGIS) a écrit :

Hi,

I'm a team member of GIS working group at IRSTV. My next task is to cleanly incorporate spatial analysis into or onto H2. Our project repository is here https://github.com/irstv/H2GIS.

Currently I'm trying to learn as most as I can of H2 and all other H2 spatial projects.

Using custom datatype and functions through "CREATE DOMAIN" and "CREATE ALIAS" leads to call theses functions to all new local H2 DataBase and existing H2 DataBase connection with admin rights..

I think about creating a custom Driver for spatial ex:"org.h2spatial.Driver" then catch admin connection on local h2 DataBase, but it must not be a great idea ?

You were talking about MVStore and custom data type (de)serialisation, but I don't find samples, and I don't find a way to feed a Class or object factory. But anyway it seems that this storage is not yet used in H2. May be I should work on the snapshot release ?

Final words are, what do you think is the best strategy ? Working with you on the trunk to incorporate h2 spatial in native or continue to stick with version 1.3.171 and build a overloaded Driver for spatial purposes ?

Thank you for your support

[Nicolas Fortin (OrbisGIS)]

The parameter could be also a class that extend org.h2.api.JavaObjectSerializer however it would be not as flexible as extending 'org.h2.value.Value'

[Thomas Mueller]

Hi,

Is this change needed for it to work, I mean, is it blocking you? It sounds like a potential performance optimization, however I'm not sure if it makes sense to change H2 at this stage. Couldn't you use regular serialization? If serialization is really the (performance) bottleneck, we could do this.

Regards,

Thomas

[Nicolas Fortin (OrbisGIS)]

This is not a performance issue, this is field size cost issue. Using standard Java serialization the field contain additional information like class identifier and some other useless data. This is not blocking, I currently use JavaObjectSerializer however this is the same object serializer for all Java fields.

H2Spatial pass all OGC SFS Conformance test (53 unit tests), I will add some additional unit test then spatial index.

Thanks for support.

[Thomas Mueller]

Hi,

> This is not a performance issue, this is field size cost issue.

I see. What is the class of the object? If it's "your own" class, then I think the serialization could be user defined, or (maybe better) we could use Externalizable (which would also require a change in H2, but it should be straightforward).

Your proposal (that the serializer class is set when declaring the column) works well but I think it requires one field (the serializer) per value. This is a slight memory usage disadvantage. I just like to understand if there are any alternatives, but maybe this is the best solution.

> H2Spatial pass all OGC SFS Conformance test (53 unit tests), I will add some additional unit test then spatial index.

That's really great!

Thanks a lot!

Thomas

[Nicolas F.]

Hi,

The class is this one https://github.com/nicolas-f/H2GIS/blob/h2spatial-osgi-snapshot/h2spatial/src/main/java/org/h2spatial/ValueGeometry.java there is only JTS Geometry inside. The serialised data should be restricted to Well Known Binary.

Using Externalizable the class identifier and some other bytes are still stored in bytes value.

I don't understand why we need to store the serializer on each value. The idea is to use the same serializer on each value of the column (then retrieve the serializer class on the metadata of the column)

A more complex solution is to implement CREATE TYPE instruction..

-Nicolas

2013/5/16 Thomas Mueller