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)
  )
);

Can you provide the DDL statements used to create the `testGeo` table and some test data. — MT0, Feb 02 '16 at 11:19
Possible duplicate of http://stackoverflow.com/questions/7204200/how-to-retrieve-sdo-geometry-from-database-using-jdbc — Sameer Mirji, Feb 05 '16 at 10:55

score 9 · Accepted Answer · answered Feb 05 '16 at 10:29

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);

Hibernate 5 is not supported (yet, I guess), but otherwise there is official support for Spatial from the Hibernate team: http://www.hibernatespatial.org/. — Gergely Bacso, Feb 05 '16 at 14:43

score 5 · Answer 2 · answered Feb 05 '16 at 10:54

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.

score 3 · Answer 3 · answered Feb 03 '16 at 05:12

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.

answered Feb 03 '16 at 05:12

Gary Myers

34,963
3
49
74

The problem is actually that I get "null" as value of the attribute SHAPE of each row of my table, when I try to access my database using JDBC as showed above. – Neo Feb 04 '16 at 08:38

score 2 · Answer 4 · answered Feb 05 '16 at 07:54

I believe the error is with getString() because SDO_GEOMETRY can not be transformed to a string. I have no knowledge of oracleDB to be honest, so it is just a guess.

Have you tried getInt() or getByteArray() or similar options?

There are a whole lot of possibilitys when getting data from the database.

Display value of attribute of type SDO_GEOMETRY

4 Answers4