Display value of attribute of type SDO_GEOMETRY

Question

I'm trying to use JDBC to display rows in a table created in an Oracle 11g Database, the problem is I don't manage to get the value of the attribute "SHAPE" of type "SDO_GEOMETRY" using the following code, which by the way works fine when it comes to the other attributes of the table :

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("Driver O.K.");

            String url = "jdbc:oracle:thin:@localhost:1521:xe";
            String user = "system";
            String passwd = "isima";

            Connection conn = DriverManager.getConnection(url, user, passwd);
            System.out.println("Connexion effective");

            Statement myStmt = null;
            ResultSet myRs = null;          

            myStmt = conn.createStatement();

            myRs = myStmt.executeQuery("SELECT * FROM testGeo");

            while (myRs.next()) {
            System.out.println(myRs.getString("shape"));

            } 

            } catch (Exception e) {
            e.printStackTrace();
            } 

Here are the DDL statements used to create the testGeo table and some test data.

CREATE TABLE testGeo (
  GeoID NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  shape SDO_GEOMETRY);

INSERT INTO testGeo VALUES(
  1,
  'cola_a',
  SDO_GEOMETRY(
    2003,
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(1,1, 5,7)
  )
);

INSERT INTO testGeo VALUES(
  2,
  'cola_b',
  SDO_GEOMETRY(
    2003,
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(2,1, 6,7)
  )
);

INSERT INTO testGeo VALUES(
  3,
  'cola_c',
  SDO_GEOMETRY(
    2003,
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(1,1, 9,9)
  )
);

Answer 1

The problem itself is caused by the fact that you are trying to retrieve a complex object that is a custom Oracle object which should be handled with its own mapping library. The SDO_GEOMETRY type as used here in you example statement:

SDO_GEOMETRY(
   2003,
   NULL,
   NULL,
   SDO_ELEM_INFO_ARRAY(1,1003,3),
   SDO_ORDINATE_ARRAY(1,1, 5,7)
)

is part of the Oracle Spatial package and relies on custom objects. These cannot be read as easily as basic SQL columns like VARCHAR or NUMBER, that is why your getString("shape") call yields no results.

The solution is to use the JGeometry package provided by Oracle that is designed exactly for this purpose:

A Java class that maps Oracle Spatial's SQL type MDSYS.SDO_GEOMETRY. Supports only Oracle JDBC Driver version 8.1.7 or higher. Provides basic access functions to the geomeries stroed in Oracle Spatial database.

With this library on classpath you would be able to use the custom Java mappings to manipulate your shape object:

/// reading a geometry from database
ResultSet rs = statement.executeQuery("SELECT shape FROM testGeo");
STRUCT st = (oracle.sql.STRUCT) rs.getObject(1);
//convert STRUCT into geometry
JGeometry j_geom = JGeometry.load(st);

Answer 2

You should use JGeometry class in the Oracle Spatial Java package for this. You can call the spatial type, like:

// Read a geometry from the DB.
ResultSet rs = statement.executeQuery("SELECT shape FROM testGeo where name='cola_a'");
STRUCT stGeo = (oracle.sql.STRUCT) rs.getObject(1);
JGeometry jGeo = JGeometry.load(stGeo);
// Use jGeo to fetch the required data.

If you need to write the Geometry back to Database, you can try this:

 // Write a geometry back to the DB.
 PreparedStatement ps = connection.prepareStatement("UPDATE testGeo set shape=? where name='cola_a'");
 STRUCT stGeo = JGeometry.store(jGeom, connection);
 ps.setObject(1, stGeo);
 ps.execute();

Note: Refer the JGeometry documentation for the usage of the class APIs.

Answer 3

You can use something like

select g.geoid, g.name, go.column_value
from testgeo g, table(g.shape.SDO_ORDINATES) go;

but you will get one row for each number in the shape.