OpenJPA and PostGIS or other spatial extensions

[Harald Wellmann <Ha...@multi-m.de>]

I'm currently evaluating OpenJPA as a replacement for Hibernate. I'm rather
frustrated by the lack of JPA 2.0 compliance in Hibernate, and I'm glad to
say I haven't run into any such problems with OpenJPA so far.

On the other hand, there are some extensions beyond the scope of JPA which
work with Hibernate and which would have to be replaced as well.

Our project uses geodata stored in PostGIS. Hibernate Spatial provides type
mappings for the geometry JDBC types and custom HQL operators like WITHIN()
corresponding to PostGIS stored procedures.

Would it be possible to hook into OpenJPA in a similar manner to support
spatial extensions without resorting to native SQL?

Best regards,

Harald

-- 
View this message in context: http://openjpa.208410.n2.nabble.com/OpenJPA-and-PostGIS-or-other-spatial-extensions-tp5302323p5302323.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: OpenJPA and PostGIS or other spatial extensions

[Harald Wellmann <Ha...@multi-m.de>]

Thanks for your feedback so far. By now, I've converted my complete entity
model from Hibernate to JPA (on a trial branch...) and the experience has
been rather painless. Finally, certain things that never worked with
Hibernate are not a problem any longer. I discovered inconsistencies in my
model that Hibernate would let pass, and so far just one potential bug in
OpenJPA regarding @MapKey.

Anyway, converting all the geospatial extensions will be the hardest part.
For sure, this is not part of the JPA spec, but still I need to solve this
somehow.

I'm currently using an extension named Hibernate Spatial (which is not
_from_ Hibernate/RedHat, but _for_ Hibernate), and from what I've seen in
their sources, Hibernate offers you hooks to register functions at SQL and
even HQL/JPQL level. This is what Hibernate Spatial uses internally to make
things rather easy for their users. 


Example:

        jpql = "select p from GeoPosition p where within(p.point, :filter) =
true";
        query = em.createQuery(jpql, GeoPosition.class);
        Geometry filter = factory.toGeometry(new Envelope(9.9, 10.1, 53.4,
53.6));

        CustomType geomType = new CustomType(GeometryUserType.class, null);
        Query hibernateQuery =
query.unwrap(QueryImpl.class).getHibernateQuery();
        hibernateQuery.setParameter("filter", filter, geomType);
        
        positions = query.getResultList();

The corresponding entity is

@Entity
public class GeoPosition {

    @Id
    private long id;

    @Type(type = "org.hibernatespatial.GeometryUserType")
    private Point point;
}


"Point" is a Geometry type from the JTS topology suite, not from PostGIS.

I think you get the idea. This is all rather nifty, and most of the work is
hidden in Hibernate Spatial, but to be fair on Hibernate, this would not
have been possible without suitable extension points in Hibernate Core.

Now with OpenJPA, I understand it should be rather easy to write the
equivalent of a Hibernate UserType to persist JTS Geometry fields. Of course
I could always fall back to a native SQL query, which would not hurt too
much in the above example, but sooner or later I will also need a
combination of joins along associations with geospatial filters, so this is
more of a workaround than a solution: you really want to be able to use
spatial predicates at JPQL level.

@Jerry: Can you explain a bit more about the Criteria API approach? I'm not
bent on JQPL if there is an easier way of representing spatial predicates as
criteria, but I don't see how to do it...

@Kevin: Yes, "impossible is nothing", but after my first week with OpenJPA,
I wouldn't really like to start hacking the parser ;-) It seems in Hibernate
the operators are not all hard-coded in the parser, they are just
identifiers, and Hibernate extensions are able to plug in additional
handlers for additional operators. 

Thanks for pointing me to the data dictionary - yes, I'll take a look at
that.

Best regards,

Harald








-- 
View this message in context: http://openjpa.208410.n2.nabble.com/OpenJPA-and-PostGIS-or-other-spatial-extensions-tp5302323p5311362.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: OpenJPA and PostGIS or other spatial extensions

[Kevin Sutter <kw...@gmail.com>]

Hi Jerry and Harald,
It's always good to hear positive remarks about our OpenJPA efforts...

Besides Jerry's useful experiences, the other thing to point out is that you
should be able to extend the OpenJPA implementation to use the PostGIS
features more directly (vs native queries).  But, this would come with some
development costs.  It sounds like PostGIS has extended Postgres for the
spatial capabilities.  Would extending the Postgres data dictionary be
helpful with the additional PostGIS JDBC extensions?

Enhancing JPQL with some new operators similar to HQL may be a bit more
work.  You would have to modify the jjtree parser to allow the new
functions, and then map these to appropriate processing within the runtime
and dictionary to accomplish your goals.

So, yes, anything is doable...  :-)  You'll find that OpenJPA is actually an
open, flexible architecture.  Good luck on your findings and let us know how
it's going for you.

Kevin

On Fri, Jul 16, 2010 at 10:30 AM, No1UNo <je...@jerrycarter.org> wrote:

>
> I've been walking down the same road.  I'm also working with PostGIS.  I
> started with Hibernate and then shifted to OpenJPA and haven't looked back.
>
> I am using the NativeQuery mechanism.  I had an exchange with Pinaki
> regarding the Critera API which was added to JPA 2.0 some number of weeks
> back.  This is one way to invoke PostGIS functions but it's really just
> hiding the native characteristic of what are necessarily native functions.
>  I do, however, specify the queries in a separate XML file and then refer to
> them by name.  In my mind, this will simplify migration to Oracle should it
> become necessary in future.
>
> As for the various geographic types, I build a custom AbstractValueHandler
> for serializing/converting the PostGIS representations into the form used in
> my code.  If you work with the org.postgis.* types, this may not even be
> necessary.
>
> In short, it's doable, but it may take a little work.
>
> -=- Jerry
>
>
> On Jul 16, 2010, at 11:02 AM, Harald Wellmann [via OpenJPA] wrote:
>
> > I'm currently evaluating OpenJPA as a replacement for Hibernate. I'm
> rather frustrated by the lack of JPA 2.0 compliance in Hibernate, and I'm
> glad to say I haven't run into any such problems with OpenJPA so far.
> >
> > On the other hand, there are some extensions beyond the scope of JPA
> which work with Hibernate and which would have to be replaced as well.
> >
> > Our project uses geodata stored in PostGIS. Hibernate Spatial provides
> type mappings for the geometry JDBC types and custom HQL operators like
> WITHIN() corresponding to PostGIS stored procedures.
> >
> > Would it be possible to hook into OpenJPA in a similar manner to support
> spatial extensions without resorting to native SQL?
> >
> > Best regards,
> >
> > Harald
> >
> >
> > View message @
> http://openjpa.208410.n2.nabble.com/OpenJPA-and-PostGIS-or-other-spatial-extensions-tp5302323p5302323.html
> > To start a new topic under OpenJPA Users, email
> ml-node+208411-1595610943-93721@n2.nabble.com<ml...@n2.nabble.com>
> > To unsubscribe from OpenJPA Users, click here.
> >
>
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/OpenJPA-and-PostGIS-or-other-spatial-extensions-tp5302323p5302479.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>

Re: OpenJPA and PostGIS or other spatial extensions

[No1UNo <je...@jerrycarter.org>]

I've been walking down the same road.  I'm also working with PostGIS.  I started with Hibernate and then shifted to OpenJPA and haven't looked back.

I am using the NativeQuery mechanism.  I had an exchange with Pinaki regarding the Critera API which was added to JPA 2.0 some number of weeks back.  This is one way to invoke PostGIS functions but it's really just hiding the native characteristic of what are necessarily native functions.  I do, however, specify the queries in a separate XML file and then refer to them by name.  In my mind, this will simplify migration to Oracle should it become necessary in future.

As for the various geographic types, I build a custom AbstractValueHandler for serializing/converting the PostGIS representations into the form used in my code.  If you work with the org.postgis.* types, this may not even be necessary.

In short, it's doable, but it may take a little work.

-=- Jerry


On Jul 16, 2010, at 11:02 AM, Harald Wellmann [via OpenJPA] wrote:

> I'm currently evaluating OpenJPA as a replacement for Hibernate. I'm rather frustrated by the lack of JPA 2.0 compliance in Hibernate, and I'm glad to say I haven't run into any such problems with OpenJPA so far. 
> 
> On the other hand, there are some extensions beyond the scope of JPA which work with Hibernate and which would have to be replaced as well. 
> 
> Our project uses geodata stored in PostGIS. Hibernate Spatial provides type mappings for the geometry JDBC types and custom HQL operators like WITHIN() corresponding to PostGIS stored procedures. 
> 
> Would it be possible to hook into OpenJPA in a similar manner to support spatial extensions without resorting to native SQL? 
> 
> Best regards, 
> 
> Harald 
> 
> 
> View message @ http://openjpa.208410.n2.nabble.com/OpenJPA-and-PostGIS-or-other-spatial-extensions-tp5302323p5302323.html 
> To start a new topic under OpenJPA Users, email ml-node+208411-1595610943-93721@n2.nabble.com 
> To unsubscribe from OpenJPA Users, click here.
> 


-- 
View this message in context: http://openjpa.208410.n2.nabble.com/OpenJPA-and-PostGIS-or-other-spatial-extensions-tp5302323p5302479.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.