Using GEOMETRY and GEOGRAPHY data types in JDBC

Vertica GEOMETRY and GEOGRAPHY data types are backed by LONG VARBINARY native types and JDBC client applications treat them as binary data. However, these data types have a format that is unique to Vertica. To manipulate this data in your Java application, you must use the functions in Vertica that convert them to a recognized format.

To convert a WKT or WKB to the GEOMETRY or GEOGRAPHY format, use one of the following SQL functions:

• ST_GeographyFromText—Converts a WKT to a GEOGRAPHY type.

• ST_GeographyFromWKB—Converts a WKB to a GEOGRAPHY type.

• ST_GeomFromText—Converts a WKT to a GEOMETRY type.

• ST_GeomFromWKB—Converts a WKB to GEOMETRY type.

To convert a GEOMETRY or GEOGRAPHY object to its corresponding WKT or WKB, use one of the following SQL functions:

• ST_AsText—Converts a GEOMETRY or GEOGRAPHY object to a WKT, returns a LONGVARCHAR.

• ST_AsBinary—Converts a GEOMETRY or GEOGRAPHY object to a WKB, returns a LONG VARBINARY.

The following code example converts WKT and WKB data into GEOMETRY data using ST_GeomFromText and ST_GeomFromWKB and stores it in a table. Later, this example retrieves the GEOMETRY data from the table and converts it to WKT and WKB format using ST_AsText and ST_AsBinary.

import java.io.InputStream;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class GeospatialDemo
{
    public static void main(String [] args) throws Exception
    {
        Class.forName("com.vertica.jdbc.Driver");
        Connection conn =
              DriverManager.getConnection("jdbc:vertica://localhost:5433/db",
                                          "user", "password");
        conn.setAutoCommit(false);

        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE polygons(id INTEGER PRIMARY KEY, poly GEOMETRY)");

        int id = 0;
        int numBatches = 5;
        int rowsPerBatch = 10;

        //batch inserting WKT data
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO polygons
                                  (id, poly) VALUES(?, ST_GeomFromText(?))");
        for(int i = 0; i < numBatches; i++)
        {

            for(int j = 0; j < rowsPerBatch; j++)
            {
                //Insert your own WKT data here
                pstmt.setInt(1, id++);
                pstmt.setString(2, "polygon((1 1, 1 2, 2 2, 2 1, 1 1))");
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }

        conn.commit();
        pstmt.close();
        //batch insert WKB data
        pstmt = conn.prepareStatement("INSERT INTO polygons(id, poly)
                                      VALUES(?, ST_GeomFromWKB(?))");
        for(int i = 0; i < numBatches; i++)
        {
            for(int j = 0; j < rowsPerBatch; j++)
            {
                //Insert your own WKB data here
                byte [] wkb = getWKB();
                pstmt.setInt(1, id++);
                pstmt.setBytes(2, wkb);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }

        conn.commit();
        pstmt.close();
        //selecting data as WKT
        ResultSet rs = stmt.executeQuery("select ST_AsText(poly) from polygons");
        while(rs.next())
        {
            String wkt = rs.getString(1);
            Reader wktReader = rs.getCharacterStream(1);
            //process the wkt as necessary
        }
        rs.close();

        //selecting data as WKB
        rs = stmt.executeQuery("select ST_AsBinary(poly) from polygons");
        while(rs.next())
        {
            byte [] wkb = rs.getBytes(1);
            InputStream wkbStream = rs.getBinaryStream(1);
            //process the wkb as necessary
        }
        rs.close();

        //binding parameters in predicates
        pstmt = conn.prepareStatement("SELECT id FROM polygons WHERE
                                      ST_Contains(ST_GeomFromText(?), poly)");
        pstmt.setString(1, "polygon((1 1, 1 2, 2 2, 2 1, 1 1))");
        rs = pstmt.executeQuery();
        while(rs.next())
        {
            int pk = rs.getInt(1);
            //process the results as necessary
        }
        rs.close();

        conn.close();
    }
}