[postgis-users] postgis JDBC failing to write PGgeometry or PGgeometryLW

[Jeremy Reeve]

Hi there,

I hope someone can assist in pointing a PostGIS newbie in the right direction.

Using the following explicit Maven dependency:

<dependency>
  <groupId>org.postgis</groupId>
  <artifactId>postgis-jdbc</artifactId>
  <version>1.3.3</version>
</dependency>

Where transitive dependencies on postgis-stubs (1.3.3) and postgresql-8.3-603.jdbc4.jar are also pulled in.

Executing the following code yields an exception thrown, the following is executed once during construction:

Class.forName("org.postgresql.Driver");
mConnection = DriverManager.getConnection(URL, USER, PASSWORD);

/*
 * Add the geometry types to the connection. Note that you must cast the
 * connection to the pgsql-specific connection implementation before
 * calling the addDataType() method.PostGIS 2.1.1dev Manual 75 / 672
 */
((org.postgresql.PGConnection) mConnection).addDataType("geometry",org.postgis.PGgeometry.class);
mPreparedStatementInsertObservation = mConnection.prepareStatement(INSERT_OBSERVATION_SQL);


And the following multiple times:

Point point = new Point();
point.x = observation.getLocation().getLatitude();
point.y = observation.getLocation().getLongitude();
point.z = observation.getLocation().getAltitude();
PGgeometry geometry = new PGgeometry(point);
mPreparedStatementInsertObservation.setObject(1, geometry);

The top of stack trace when exception is thrown from setObject:

java.lang.NoSuchMethodError: org.postgresql.core.BaseConnection.getEncoding()Lorg/postgresql/core/Encoding;
			     at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:2025)
			     at org.postgresql.jdbc2.TypeInfoCache.getPGType(TypeInfoCache.java:216)
			     at org.postgresql.jdbc2.AbstractJdbc2Connection.getPGType(AbstractJdbc2Connection.java:971)
			     at org.postgresql.jdbc2.AbstractJdbc2Statement.setPGobject(AbstractJdbc2Statement.java:1542)
			     at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1736)
			     at com.argusat.gjl.observice.repository.postgis.ObservationRepositoryPostGISImpl.storeObservations(ObservationRepositoryPostGISImpl.java:145)
			     at com.argusat.gjl.observice.repository.postgis.ObservationRepositoryPostGISImplTest.testStoreObservations(ObservationRepositoryPostGISImplTest.java:72)


I have read on the mailing list that the root cause is a subtle classpath ordering issue but I'm not sure of exactly what's happening.   It looks to me as though the PGgeometry type isn't being correctly registered with JDBC.  I have seen driverconfig.properties packaged with the postgis-jdbc jar which appears to perform the required registration.  I have tried the explicit registration and assuming that the driverconfig.properties is working but neither seem to work.

Incidentally, is it possible to use PGgeometryLW in place of PGgeometry for better performance and if so, is the following SQL correct?

private static final String INSERT_OBSERVATION_SQL = "insert into observations "
 	      	     + "(location, obs_timestamp, device_id, hdop, vdop, obs_type, value0, "
     		   + "value1, value2, value3, value4 ) VALUES"
	      	   + "(ST_GeomFromEWKB(?),?,?,?,?,?,?,?,?,?,?)";

Should I be using the values without wrapping in a ST_ constructor when using PGgeometry as opposed to PGgeometryLW, for example:

private static final String INSERT_OBSERVATION_SQL = "insert into observations "
 	      	     + "(location, obs_timestamp, device_id, hdop, vdop, obs_type, value0, "
     		   + "value1, value2, value3, value4 ) VALUES"
	      	   + "(?,?,?,?,?,?,?,?,?,?,?)";

I think I'd prefer geography types in my database, is it possible to use these in conjunction with JDBC? 


With kind regards..Jeremy