[postgis-devel] JDBC: Setting/Getting JTS-geometries using bind-vars

[Aron Olsen]

Hi there,

I'm a newcomer to Postgresql / PostGis but have experience from Oracle.

I was looking for a way to get JTS-geoms from ResultSets and to set
JTS-geoms in PreparedStatements using bind-vars, but what I found was not
very usefull. Usually everybody seems to use WKT, which requires a special
SQL-syntax. This was not acceptable to me.

Using the "plain" JDBC-driver I took a look at what I got from getObject(
int n ) on a geometry column. It was a PGobject.

Then I took a look at the contents of PGobject.getValue(), and found that
it's a hex-encoded WKB.

Then I realized that I could construct a PGobject using:

po.setType( "geometry");
po.setValue( hexedBinaryString );

and that I could bind it to a statement using setObject( 1, po );

By reverse-engineering the BinaryParser and BinaryWriters into
JTSBinaryParser and JTSBinaryWriter I was able to get and set JTS more or
less directly, on a "plain" JDBC-driver.

Heres sample code for getting and setting:

// Getting:
PreparedStatement stmt1 = connection.prepareStatement("select geom from xxx
where id=25");
ResultSet rs = stmt1.executeQuery();
rs.next();
PGobject po = rs.getObject(1);

JTSBinaryParser jbp = new JTSBinaryParser();
Geometry        g   = jbp.parse( s0 );

// Setting
JTSBinaryWriter jbw = new JTSBinaryWriter();
PGobject newPo = new PGobject();
newPo.setType("geometry");
newPo.setValue( jbw.writeHexed( g ) );

PreparedStatement stmt2 = connection.prepareStatement("update xxx set geom=?
where id=25");
stmt2.setObject( 1, newPo );
stmt2.executeUpdate();


What a newcomer like me doesn't know is whether this scheme holds for all
postgresql/postgis implementations/versions. Maybe somebody can give me a
hint here ? :) 

If anybody would like the source-code for JTSBinaryParser and -Writer I'd be
more than happy to post the code somewhere.

Thanks.
Aron.