[postgis-users] Postgis and JDBC

[Kevin Neufeld]

Yes, it's possible to set the java object in a PreparedStatement when 
writing to a PostGIS database. Your options are to set the object as a 
String, providing the Well Known Text (WKT) representation of the 
geometry, or alternatively, as a byte[], providing a Well Known Binary 
(WKB) representation of your token geometry. Both are easy with the 
right jars. Keep in mind that WKT is prone to precision loss and 
coordinate shifts simply because you are writing in text format. This 
may or may not be an issue you.

Using the Java Topology Suite (JTS) jar, you can do this:
(In all these examples, replace the srid of "3005" with the srid of the 
geometries in your table)

// --------------------------------
// Using WKT Representation
String wktQuery = "SELECT * FROM thetable
                   WHERE the_geom && geomfromtext(?, 3005)
                   AND Intersects(the_geom, geometryfromtext(?, 3005))";
PreparedStatement pstmt = conn.prepareStatement(wktQuery);
pstmt.setString(1, "POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))");
pstmt.setString(2, "POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))");
pstmt.execute();

// A BETTER WKT query would be:
// This way, you only have to set the geometry once.
String wktQuery = "SELECT a.*
                   FROM thetable AS a,
                        (SELECT geomfromtext(?, 3005) AS the_geom) AS b
                   WHERE a.the_geom && b.the_geom
                   AND Intersects(a.the_geom, b.the_geom)";
PreparedStatement pstmt = conn.prepareStatement(wktQuery);
pstmt.setString(1, "POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))");
pstmt.execute();

// --------------------------------
// Using WKB Representation - This is what I would recommend
// Assuming your polygon is a JTS polygon...

WKBWriter wkbWriter = new WKBWriter();
// Use "new WKBWriter(3)" if writing 3D data.

String wkbQuery = "SELECT a.*
                   FROM thetable AS a,
                        (SELECT setsrid(?, 3005) AS the_geom) AS b
                   WHERE a.the_geom && b.the_geom
                   AND Intersects(a.the_geom, b.the_geom)";
PreparedStatement pstmt = conn.prepareStatement(wkbQuery);
pstmt.setBytes(1, wkbWriter.write(myPoly));
pstmt.execute();


Hope this helps,
-- Kevin


Andy Dale wrote:
> Hi,
>
> I am using Postgis via JDBC (namely spatial 
> EJB).  I just have a quick question about setting geometry parameters in a JDBC query, 
> the question is it possible just to set a java object in the query or does it always parse (binaryParser, 
> binarayWriter) the string representation of the object.
>
> A small example is the following query:
>
> // Create a new Polygon
> Polygon p = new Polygon("((0 0, 0 10, 10 10, 10 0, 0 0))");
>
> String query = "SELECT * FROM thetable WHERE
>                           the_geom && ?
>                           AND Intersects(the_geom, ?)";
>
>
> PreparedStatement prep = mDBConnection.prepareStatement(query);
>
> // not sure what type to set, set blob ????
> prep.setBlob(1, p);
> prep.setBlob (2, p);
>
> prep.execute();
>
> // DO STUFF WITH RESULT
>
> I only ask this question because when using Postgis with EJB3 i cannot 
> just set the object as a parameter as it needs to be contained in 
> single quotation marks, if not the geometry is invalid (parse error - 
> invalid geometry)
>
> Any suggestions ?
>
> Thanks,
>
> Andy
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users