We are using ArcSDE 10.0 to export data to the Oracle database, and the spatial property are saved using the SDE.st_geometry format.

Now in our J2EE application we tried to manage these data by hibernate spatial.

And hs support Oracle Spatial out of box, but it have no documents about the st_geometry for ArcSDE.

So I wonder if we can use it for st_geometry ? If not, is it possible to extend the functions of hs?


This is my dialect, and I register two functions overlaps and length:

public class Oracle11gSTGeometrySpatialDialect extends Oracle10gDialect {
    public Oracle11gSTGeometrySpatialDialect() {
        registerFunction("overlaps", new STFunction(new BooleanType(), "sde.st_overlaps(?1,sde.st_geometry(?2,3))"));
        registerFunction("length", new SQLFunctionTemplate(new DoubleType(), "sde.st_length(?1)"));

And the query:

Query q=em.createQuery("from HBDS h where length(h.shape) > -1");

However the generated sql is invalid:

select hbds0_.objectId as objectId1_0_, st_astext(hbds0_.shape) as shape4_0_ from TEST_BDS hbds0_ where sde.st_length(st_astext(hbds0_.shape))>-1

While the valid sql should be:

select hbds0_.objectId as objectId1_0_, st_astext(hbds0_.shape) as shape4_0_ from TEST_BDS hbds0_ where sde.st_length(hbds0_.shape)>-1

It seems that this is caused by the ColumnTransformer annotation.

I am surprised that your example works as expected?

Now I can use the native query:

Query q = em.createNativeQuery("select st_astext(shape) from TEST_BDS h where sde.st_overlaps(h.shape, st_geometry(?,3)) = 1");
q.setParameter(1, JTS.defaultGeometryFactory.createPoint(new Coordinate(x, y)).toText());

It works, but there are some cons:

1 I have to write the select fields manually since I can not use select * which will be a problem once I have a lot of fileds in the entity.

2 I can not count the row count for the specified query.


Hy, I just created a project for Hibernate which adds support for ST_geometry. Currently Postgresql and Oracle are supported.

You can find it here: http://bitbucket.schuller.lu/hibernatestgeometry

It's still beta, but first tests are very promising. I'm using the WKT/WKB representation to read or update the geometry in the database.

It looks like this:

@Table(name = "dau_track")
public class DauTrack {

    @Column(columnDefinition = "st_geometry")
    @Type(type = "lu.schuller.arcgisStGeometry.WkbStGeometryType")
    @ColumnTransformer(read = "st_asbinary(shape)", write = "st_geomfromwkb(?,2169)")
    private Polyline shape;

I created my own Wkb-HibernateType to convert the wkb-data betweent database and geometry-api. I have already implemented JTS and esri-geomeotry as geo-libraries.

The 'columnTransformers' are modifiing the generated hibernate-sql to read and write under wkb-form in the database.

It's currently running under postgresql and underOracle.

  • Nice job, but the link is dead. – giser Feb 25 '15 at 11:31
  • I forked it and make it work locally, and I have made a small fix which I have created a pull request. And one more thing, how about if I want to use some spatial function like st_overlap in the query ? – giser Feb 26 '15 at 3:22
  • Is it possible to make it support the spatial functions as I mentioned in the previous comment? – giser Feb 26 '15 at 5:39
  • Would be possible with native query. Or defining them as hibernate-functions, similar as in the hibernate-spatial project – Schuller Tom Feb 26 '15 at 18:52

Just added simple support for ST_geometry functions to: http://bitbucket.schuller.lu/hibernatestgeometry

Here a possible hibernate query using the 'st_length' function

em.createQuery("from MyLine where st_length(shape) > 2000").getResultList();

  • I update my post – giser Feb 27 '15 at 1:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.