hibernate-spatial: sql query to retrieve a geometry

Question

I'm using postgresql, hibernate-spatial and postgis and expected to be able to use a SqlQuery to retrieve a Geometry object.

However whenever I try to query a Point, Polygon or Geometry eg

List list = session.createSQLQuery(
    "select geomfromewkt('SRID=1;POINT(1 1)')").list();

I get the exception:

Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:78)
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:103)
    at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:328)
    at org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType(CustomLoader.java:590)
    at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.performDiscovery(CustomLoader.java:516)
    at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:532)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1962)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)

hibernate.cfg.xml:

<property name="dialect">org.hibernatespatial.postgis.PostgisDialect</property>

Is this not part of the hibernate-spatial offering or am i doing something wrong?

Thanks, Paul.

Relevant libraries/versions:

hibernate-core-3.6.0.Final.jar
postgresql-9.0-801.jdbc4.jar
hibernate-spatial-postgis-1.1.1.jar
hibernate-spatial-1.1.1.jar
postgis-jdbc-1.3.3.jar
jts-1.12.jar

Is the DB spatially enabledand have the correct privileges? From the client, try select * from geometry_columns — Mike T
– Mike T, Commented Jan 21, 2013 at 20:59
Yes the DB is all fine. I can retrieve geometries via hibernate if using mapped classes and non-native-sql type queries (criteria/hsql). — pstanton
– pstanton, Commented Jan 21, 2013 at 21:04
I'm sure I have to apply the 'org.hibernatespatial.GeometryUserType' to the column somehow... — pstanton
– pstanton, Commented Jan 21, 2013 at 22:57

pstanton · Accepted Answer · 2013-01-21 23:05:38Z

6

I've figured it out. I need to apply the 'GeometryUserType' to the column via a scalar.

SQLQuery qry = session.createSQLQuery("select geomfromewkt('SRID=1;POINT(1 1)') as geom");
qry.addScalar("geom", GeometryUserType.TYPE);
List<Geometry> list = qry.list();

answered Jan 21, 2013 at 23:05

pstanton

36.9k28 gold badges130 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

der Michi Over a year ago

According to this answer some types were deprecated and thus removed in Hibernate 4+. Instead of GeometryUserType.TYPE I had to use org.hibernate.spatial.GeometryType.INSTANCE in Hibernate 4.1.4.

Jimmy · Accepted Answer · 2016-04-26 00:07:04Z

1

I'm using Hibernate 5.1.0 Final,

dao.getSession().createSQLQuery("select buffer from puntosdeinteres where puntoid = 13193")
                        .addScalar("buffer", new GeolatteGeometryType(PGGeometryTypeDescriptor.INSTANCE))
                        .uniqueResult();

And it works, but right now I find that long statement uncomfortable.

answered Apr 26, 2016 at 0:07

Jimmy

1114 bronze badges

Collectives™ on Stack Overflow

hibernate-spatial: sql query to retrieve a geometry

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related