Spatial data types sample

Download JDBC driver

This Microsoft JDBC Driver for SQL Server sample application demonstrates how to create, insert, and retrieve Spatial Data types (Geometry and Geography).

The code file for this sample is named SpatialDataTypes.java, and it can be found in the following location:

\<installation directory>\sqljdbc_<version>\<language>\samples\datatypes

Requirements

To run this sample application, you must set the classpath to include the mssql-jdbc jar file. For more information about how to set the classpath, see Using the JDBC Driver.

Example

In the following example, the sample code creates a table called SpatialDataTypesTable_JDBC_Sample that contains 'Geometry' and 'Geography' columns.

The sample first creates 'Geometry' and 'Geography' objects from a Well-Known-Text (WKT) representing a POINT. It uses a SQLServerPreparedStatement with a parameterized query to map the data to each column.

Finally, the sample inserts the data into the table, and retrieves it. The data is displayed in the form of WKT.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import com.microsoft.sqlserver.jdbc.Geography;
import com.microsoft.sqlserver.jdbc.Geometry;
import com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement;
import com.microsoft.sqlserver.jdbc.SQLServerResultSet;

public class SpatialDataTypes {

    private static String tableName = "SpatialDataTypesTable_JDBC_Sample";

    public static void main(String[] args) {

        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=<database>;user=<user>;password=<password>";
        // Establish the connection.
        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) {
            dropAndCreateTable(stmt);

            // TODO: Implement Sample code
            String geoWKT = "POINT(3 40 5 6)";
            Geometry geomWKT = Geometry.STGeomFromText(geoWKT, 0);
            Geography geogWKT = Geography.STGeomFromText(geoWKT, 4326);

            try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con
                    .prepareStatement("insert into " + tableName + " values (?, ?)");) {
                pstmt.setGeometry(1, geomWKT);
                pstmt.setGeography(2, geogWKT);
                pstmt.execute();

                SQLServerResultSet rs = (SQLServerResultSet) stmt.executeQuery("select * from " + tableName);
                rs.next();

                System.out.println("Geometry data: " + rs.getGeometry(1));
                System.out.println("Geography data: " + rs.getGeography(2));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void dropAndCreateTable(Statement stmt) throws SQLException {
        stmt.executeUpdate("if object_id('" + tableName + "','U') is not null" + " drop table " + tableName);

        stmt.executeUpdate("Create table " + tableName + " (c1 geometry, c2 geography)");
    }
}

See also

Working with JDBC data types