Does hibernate spatial support st_geometry created by ArcSDE?

Question

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?

---

Update:

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

public class Oracle11gSTGeometrySpatialDialect extends Oracle10gDialect {
    public Oracle11gSTGeometrySpatialDialect() {
        super();
        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.

Answer 1

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:

@Entity
@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.

Answer 2

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();