PostGIS update of Geometry from Java from Polygon object

Question

I am trying to update a PostGIs geometry column with data from an external file. The number of points in some of the polygons is over 35k and the ST_GeomFromText method does not work because the text string is too long. I can read the points into a Polygon object and I would like to do something like:

PreparedStatement ps = conn.preparedStatment("update x set y = ? where z = ?");
ps.setBinary(1, polygon);
ps.setString(2, key);
ps.executeUpdate();

I have not been able to find any examples on how do this. Could someone please point me toward the documentation or give an example I can follow?

Thanks

Answer 1

So, I did some testing and whipped up the following example for your benefit. I also just made it available as a GIST on Github, feel free to clone and explore.

First, here's a DDL for a table I created in a new database gis_data which is built on the template_postgis database template:

CREATE TABLE sample_table
(
  "key" character varying(20) NOT NULL,
  large_poly geometry NOT NULL,
  CONSTRAINT simple_pl PRIMARY KEY (key)
)

This table is used in my Java example, listed below. Be sure to modify the DriverManager.getConnection() line with your credentials.

import java.sql.*;
import java.util.*;
import java.lang.*;
import org.postgis.*;

public class JavaGIS {

    public static void main(String[] args) {
        java.sql.Connection conn;
        try {
            Class.forName("org.postgresql.Driver");
            String url = "jdbc:postgresql://localhost:5432/gis_data";
            conn = DriverManager.getConnection(url, "postgres", "");

            ((org.postgresql.PGConnection)conn).addDataType("geometry",Class.forName("org.postgis.PGgeometry"));
            ((org.postgresql.PGConnection)conn).addDataType("box3d",Class.forName("org.postgis.PGbox3d"));

            Polygon geo = new Polygon(
                            new LinearRing[] {
                                new LinearRing(
                                    new Point[] {
                                        new Point(-1.0d, -1.0d,  0.5d),
                                        new Point( 1.0d, -1.0d,  0.0d),
                                        new Point( 1.0d,  1.0d, -0.5d),
                                        new Point(-1.0d,  1.0d,  0.0d),
                                        new Point(-1.0d, -1.0d,  0.5d)
                                    }
                                )
                            }
                        );
            PreparedStatement s = conn.prepareStatement("INSERT INTO sample_table (key_value, large_poly) VALUES (?, ?)");
            s.setString(1, "poly1");
            s.setObject(2, new PGgeometry(geo));

            int rows = s.executeUpdate();

            if (rows > 0) {
                System.out.println(" Successful insert! ");
            } else {
                System.out.println(" Failed insert!");
            }
            s.close();

            Statement qs = conn.createStatement();
            ResultSet r = qs.executeQuery("SELECT key_value, large_poly FROM sample_table");
            while( r.next() ) {
                /*
                * Retrieve the geometry as an object then cast it to the geometry type.
                * Print things out.
                */
                String key = r.getString(1);
                PGgeometry geom = (PGgeometry)r.getObject(2);

                if (geom.getGeoType() == Geometry.POLYGON) {
                    System.out.println("Found a polygon with key " + key);
                } else {
                    System.out.println("Found a PostGIS geometry object " + geom.getGeoType() + " having key " + key);
                }
            }
            qs.close();
            conn.close();
        }
        catch( Exception e ) {
            e.printStackTrace();
        }
    }
}

Be sure to include a recent Postgresql jdbc driver jar, and the PostGIS jar file in your classpath while compiling and executing this example.

This example is largely based on the PostGIS example which illustrates select so beautifully and simply. Inserts are just as easy, because of the great work PostGIS has done on their extensions.

If you already have your data in a PostGIS Geometry object, just do like in my example and wrap it in a PGgeometry object for insertion into the database. That will let the database driver deal with how exactly to transmit it (likely as binary data). Once I actually followed the instructions on how to construct Polygons, everything worked quite nicely.

Disclaimer: I have only tried this with simple polygons, but the principles are sound. Let me know how it goes with the very large polygons you are dealing with!

Answer 2

Great work.

But i need to specify SRID (4326), otherwise the database is rejecting the polygon.

any hints?

solved it with using strings.

String bbox ="SRID=4326;POLYGON(("+lu+","+lo+","+ro+","+ru+","+lu+"))";
pstmt.setObject(12,new PGgeometry(bbox));