Mapping a linestring using PostGIS

[Wouter Souffriau]

Some javascript/style in this post has been disabled (why?)

Hi,

I want to map a linestring from postgis.

The linestring is part of following Route.java class:

**************
import com.vividsolutions.jts.geom.LineString;
import java.io.Serializable;
public class Route implements Serializable {

    private long fromID;
    private long toID;
    private LineString coordinates;
}
**************

I use following mapping file:

**************
<hibernate-mapping auto-import="true" default-lazy="false">
<class name="be.kahosl.tripplanner.common.Route" table="tbl_route">
<composite-id>
<key-property name="fromID" />
<key-property name="toID"/>
</composite-id>
<property name="coordinates" type="org.hibernatespatial.GeometryUserType" column="coordinates"/>
</class>
</hibernate-mapping>
**************

When I try to save a route object, following insert statement is generated by SpatialHibernate:

insert into tbl_route (coordinates, fromID, toID) values (SRID=4326;LINESTRING(51.2048191 3.223716,51.2048191 3.223716), 201436, 169)

This statement has wrong syntax. Could someone tell me what I am doing wrong?

Thanks in advance for your time and answer,

Wouter Souffriau

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

[Karel Maesen]

Hi,

Are you sure you have Hibernate Spatial Postgis provider (hibernate-
spatia-postgis-1.0-M2.jar)  in your classpath? See: http://
www.hibernatespatial.org/hibernate-spatial-postgis/usage.html

Regards,

Karel

On 20 May 2009, at 11:59, <[hidden email]>  
<[hidden email]> wrote:

> Hi,
>
> I want to map a linestring from postgis.
>
> The linestring is part of following Route.java class:
>
> **************
> import com.vividsolutions.jts.geom.LineString;
> import java.io.Serializable;
> public class Route implements Serializable {
>
>     private long fromID;
>     private long toID;
>     private LineString coordinates;
> }
> **************
>
> I use following mapping file:
>
> **************
> <hibernate-mapping auto-import="true" default-lazy="false">
> <class name="be.kahosl.tripplanner.common.Route" table="tbl_route">
> <composite-id>
> <key-property name="fromID" />
> <key-property name="toID"/>
> </composite-id>
> <property name="coordinates"  
> type="org.hibernatespatial.GeometryUserType" column="coordinates"/>
> </class>
> </hibernate-mapping>
> **************
>
> When I try to save a route object, following insert statement is  
> generated by SpatialHibernate:
>
> insert into tbl_route (coordinates, fromID, toID) values  
> (SRID=4326;LINESTRING(51.2048191 3.223716,51.2048191 3.223716),  
> 201436, 169)
>
> This statement has wrong syntax. Could someone tell me what I am  
> doing wrong?
>
> Thanks in advance for your time and answer,
>
> Wouter Souffriau
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/ 
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

[Wouter Souffriau]

Some javascript/style in this post has been disabled (why?)

Hi,

yes, the classpath settings are ok, and the dialect is set to org.hibernatespatial.postgis.PostgisDialect.

In the same project, I have been using hibernatespatial for quite a while now for mapping com.vividsolutions.jts.geom.Point to a geometry column. This works fine.

I try to do exactly the same thing with LineStrings: map com.vividsolutions.jts.geom.LineString to a geometry column, using the org.hibernatespatial.GeometryUserType mapping type, but it seems to generate the wrong insert query...

Best regards,

Wouter.


> Hi,
> Are you sure you have Hibernate Spatial Postgis provider (hibernate-spatia-postgis-1.0-M2.jar)  in your classpath? See: http://www.hibernatespatial.org/hibernate-spatial-postgis/usage.html
> Regards,
> Karel

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

[Karel Maesen]

Wouter,

Could you show the client code that generates the troublesome SQL?

The SQL you got contains a WKT string of your geometry. It should be  
a question mark (because the geometry object is injected by setting a  
parameter of a prepared SQL statement).

Regards,

Karel

On 20 May 2009, at 15:27, <[hidden email]>  
<[hidden email]> wrote:

> Hi,
>
> yes, the classpath settings are ok, and the dialect is set to  
> org.hibernatespatial.postgis.PostgisDialect.
>
> In the same project, I have been using hibernatespatial for quite a  
> while now for mapping com.vividsolutions.jts.geom.Point to a  
> geometry column. This works fine.
>
> I try to do exactly the same thing with LineStrings: map  
> com.vividsolutions.jts.geom.LineString to a geometry column, using  
> the org.hibernatespatial.GeometryUserType mapping type, but it  
> seems to generate the wrong insert query...
>
> Best regards,
>
> Wouter.
>
>
> > Hi,
> > Are you sure you have Hibernate Spatial Postgis provider  
> (hibernate-spatia-postgis-1.0-M2.jar)  in your classpath? See:  
> http://www.hibernatespatial.org/hibernate-spatial-postgis/usage.html
> > Regards,
> > Karel
>
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/ 
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

[Wouter Souffriau]

Some javascript/style in this post has been disabled (why?)

Karel,

following snippet of client code generates the SQL:

Route route12 = new Route();
route12.setFromID(201436l);
route12.setToID(169l);
LineString lineString12 = pointToPointRoute.calculate(p1.getLocation().getCoordinate(), p2.getLocation().getCoordinate());
route12.setCoordinates(lineString12);
Session session = sessionFactory.getCurrentSession();
session.save(route);
session.flush();

Following exception is generated:

Exception in thread "main" org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
        at $Proxy0.flush(Unknown Source)
        at be.kahosl.tripplanner.dao.RouteDAO.save(RouteDAO.java:20)
        at be.kahosl.tripplanner.preprocess.AllP2PRoutePreprocess.main(AllP2PRoutePreprocess.java:58)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into tbl_route (coordinates, fromID, toID) values (SRID=4326;LINESTRING(51.2048191 3.223716,51.2048191 3.223716), 201436, 169) was aborted.  Call getNextException to see the cause.
        at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2512)
        at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:399)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1310)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:347)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2574)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)

Best regards,

Wouter.

> Wouter,
>
> Could you show the client code that generates the troublesome SQL?
>
> The SQL you got contains a WKT string of your geometry. It should be 
> a question mark (because the geometry object is injected by setting a 
> parameter of a prepared SQL statement).
>
> Regards,
>
> Karel

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

[Wouter Souffriau]

Some javascript/style in this post has been disabled (why?)

Karel,

renaming column names in the database table from "fromID" to "fromid" solved the issue.

Thanks for the support!

best regards,

Wouter

> Wouter,
>
> Could you show the client code that generates the troublesome SQL?
>
> The SQL you got contains a WKT string of your geometry. It should be 
> a question mark (because the geometry object is injected by setting a 
> parameter of a prepared SQL statement).
>
> Regards,
>
> Karel

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users