Hibernate, Posgis and Geotools

eladm · **Joined:** Tue May 10, 2005 4:18 am **Posts:** 29

Hello All
I hope this is not OT.
I couldn't find anywhere a small explanation of how I can handle the postgis Spatial types ("geometry" for example) in a hibernate mapped class.

I understand that I need to provide a UserType. But what should happen there ? a type case ?
I am missing the bigger picture.

I tried couple of things but none of them worked. I also tried searching here, in PostGis forums and in GeoTools forum. Nada.

Maybe any of you guys did it already ?

Thank you.

Elad

baliukas · **Posted:** Thu Oct 20, 2005 5:02 am

Custom type must work without problems for this stuff, you need "org.postgresql.geometric" package to implement custom types and use functions insted of operators in queries ( custom dialect is used to add more functions)

eladm · **Joined:** Tue May 10, 2005 4:18 am **Posts:** 29

Thanks baliukas , for the quick reply.
I have "org.postgresql.geometric" included.
Should I do it like this? :

Code:

  public Object nullSafeGet(ResultSet rs, String[] names, Object owner) 
    throws HibernateException, SQLException 
  {
      if (rs.wasNull()) return null;
      
      org.postgresql.geometric.PGbox v=(org.postgresql.geometric.PGbox) rs.getObject(names[0]);
      return v; 
  }

And what should I return in the sqlTypes() ? maybe Types.BLOB ?

Thanks again !

max · **Posted:** Thu Oct 20, 2005 5:27 am

you need to read from the rs BEFORE you call wasNull() otherwise you will get faulty values if the previous read (from another column) was null

eladm · **Joined:** Tue May 10, 2005 4:18 am **Posts:** 29

Ok , I think I have a general clue.
What do you think about this solution :
Creating a view in Postgres, the uses the "astext(geom)" function to return the geometry column as text.
Than map this column to a custom UserType, and in its "nullSafeGet" use a class I found that is provided with JTS (comes with Geotools ) : WKTReader.
It can create a JTS Geometry type from this text.

Is this a good way to go ?

Thank you

Elad

baliukas · **Posted:** Thu Oct 20, 2005 10:25 am

I am not sure postgress will able to use index (it supports R-tree index for this stuff). Just try to implement custom type using "geometric" API and "Types.OTHER"

eladm · **Joined:** Tue May 10, 2005 4:18 am **Posts:** 29

baliukas, I am not sure I understand what you mean by "use geometric API".
Could you please supply the core of the nullSafeGet needed ?

Thank you !

Elad

baliukas · **Posted:** Thu Oct 20, 2005 11:20 am

Copy, modify and test some hibernate type implementation "BigDecimalType" for example:

Code:

public Object get(ResultSet rs, String name)
   throws HibernateException, SQLException {
      return rs.getObject(name);
   }

   public void set(PreparedStatement st, Object value, int index)
   throws HibernateException, SQLException {
      st.setObject(index, value,Types.OTHER);
   }

   public int sqlType() {
      return Types.OTHER;
   }


public Class getReturnedClass() {
      return PGObject.class;
   }

eladm · **Joined:** Tue May 10, 2005 4:18 am **Posts:** 29

Thank you, baliukas . I will have a close on the example.

In the meantime - I implemented the solution with the

Code:

geometry = new WKTReader().read(rs.getString(names[0]));

and it is working quite fine (at least for "get") !

I understood that you didn't encouraged this pattern. Why ?
What did you mean by "doesn't support index" ?

Thanks again ..

baliukas · **Posted:** Fri Oct 21, 2005 3:16 am

I am not sure, but I think it will be a problem with queries. Test it using exotic operators like "&<" , postgres can use R-Tree index for this stuff (with some limitations) and it very important for GIS, but probably function indexes for strings can help too.