How to retrieve SDO_GEOMETRY from database using JDBC?

Question

I am new to Java.I need to retrieve SDO_GEOMETRY from database and convert it into string using jdbc.If any one has sample or idea please share with me.

Help would be appreciated.

Best Regards,
Sanjay.

SDO_GEOMETRY is an object type. What do you mean by "I need to retrieve SDO_GEOMETRY from database and convert it into string"? — Vineet Reynolds, Aug 26 '11 at 12:01
Unless the application layer will be doing geometry calculations of the data (eg distance, area), then why not convert the SDO_GEOMETRY to a string in the database ? How large and complex is the shape [2D/3D ?] and what sort of string are you looking for ? — Gary Myers, Aug 27 '11 at 03:46

score 3 · Answer 1 · answered Aug 26 '11 at 12:08

Have a look into JGeometry class. There is an example to call the spatial type:

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

     // ... manipulate the geometry or create a new JGeometry ...

     /// writing a geometry back to database
     PreparedStatement ps = connection.prepareStatement("UPDATE states set geometry=? where name='Florida'");
     //convert JGeometry instance to DB STRUCT
     STRUCT obj = JGeometry.store(j_geom, connection);
     ps.setObject(1, obj);
     ps.execute();

Thanks for your quick reply.But i tried the above code it seems that not working.I think i am doing wrong some where.I need to retrieve the SDO_GEOMETRY values and i have to show on map. If you have any sample please share with me. — sanjay, Aug 26 '11 at 12:23
I cannot provide you with a fully working example but you can write what's your problem with the above example and then maybe I can help — Kris, Aug 26 '11 at 12:57

score 0 · Answer 2 · answered Jun 19 '20 at 14:19

If the Oracle Spatial API is not available for you, e.g. because you use OpenJDK (cf. Oracle FAQs), there is also a more rudimentary way to access an SDO_GEOMETRY object from a ResultSet r:

import java.sql.Struct;
import java.sql.Array;

//...

// access SDO_GEOMETRY of row 1
Object[] geometryArray = ((Struct) r.getObject(1)).getAttributes();
// read its components
int gtype = ((Integer) geometryArray[0]).intValue();
int srid = ((Integer) geometryArray[1]).intValue();
Double[] pointArray = ((Double[]) ((Array) geometryArray[2]).getArray());
Integer[] elemInfoArray = ((Integer[]) ((Array) geometryArray[3]).getArray());
Double[] ordinateArray = ((Double[]) ((Array) geometryArray[4]).getArray());

You can also unwrap the number types by a simple loop, e.g.,

double[] ordinates = new double[ordinateArray.length];
for (int i = 0; i < ordinates.length; i++)
     ordinates[i] = ordinateArray[i].doubleValue();

or using an equivalent more convenient method (cf. StackOverflow: How do I convert Double[] to double[]?).

How to retrieve SDO_GEOMETRY from database using JDBC?

2 Answers2

Linked