ArcSDE 10: How to read/write to Oracle Geodatabase with Java

[G_L_]

I found the following in the deprecation plan of ArcGIS 10.2:

ArcGIS 10.2 will be the last major release to support the ArcSDE SDK with the ArcSDE C and Java APIs. Today many other options are available for developers, including SQL, which is available as a result of the widespread adoption of spatial types...

In our recent Java/Oracle project, we want to write/read Features to an Oracle DB, which uses ST_GEOMETRY instead of Oracle's SDO_GEOMETRY.

So my questions is:

1. Is it a bad idea to use the Java SDE API when starting from scratch?
2. If SQL is the alternative - how can I do this? Shouldn't there be JDBC Support for ST_GEOMETRY in Oracle? I could not find anything like that.
3. If there is no JDBC Support - isn't the SDE API the only path to go currently? I don't see "many other options".

[VinceAngelo · Accepted solution]

1. Is it a bad idea to use the Java SDE API when starting from scratch?

As usual, it depends, but if you plan to remain current on your ArcGIS release, you'd
be setting yourself up for difficulty later.

2. If SQL is the alternative - how can I do this? Shouldn't there be JDBC Support for
ST_GEOMETRY in Oracle? I could not find anything like that.

JDBC is a least common denominator approach, but you can work around the lack of a geometry
type by making use of Well-Known Text and/or Well-Known Binary (any of the SQL constructors,
really).

3. If there is no JDBC Support - isn't the SDE API the only path to go currently? I don't
see "many other options".

Some of the many options may not be available, since they include using SDO_GEOMETRY
(Oracle has tweaked JDBC to support geometry insert), or another database, but depending on
the size of the geometries involved, Well-Known Text is a viable way to access ST_GEOMETRY
across JDBC.

- V

[SachinKanaujia]

ST_GEOMETRY is ESRI's format while SDO_GEOMETRY is Oracle's so if you want pure SQL then you would need to goto SDO_GEOMETRY. If you want to use ST_GEOMETRY then you can look at ArcObjects Java API or use Geoprocessing (calling it from java).

I found the following in the deprecation plan of ArcGIS 10.2:



In our recent Java/Oracle project, we want to write/read Features to an Oracle DB, which uses ST_GEOMETRY instead of Oracle's SDO_GEOMETRY.

So my questions is:

1. Is it a bad idea to use the Java SDE API when starting from scratch?
2. If SQL is the alternative - how can I do this? Shouldn't there be JDBC Support for ST_GEOMETRY in Oracle? I could not find anything like that.
3. If there is no JDBC Support - isn't the SDE API the only path to go currently? I don't see "many other options".

[G_L_]

Thx vangelo, that was helpful. Am I right - you mean doing something like this?:

    ResultSet rs = stmt.executeQuery("SELECT sde.ST_AsText(shape) as wkt FROM EXAMPLE_POLYGON_TABLE"); 
    String shapeAsWellKnownText =  rs.next().getString("wkt");
    Geometry geometry = wktParser.parse(shapeAsWellKnownText );    

But do you know if there is a way to use Hibernate or other ORM Mapper?

[VinceAngelo]

Yes, that's what I meant; you can do the same on insert.

I've always dealt with my databases directly, so I can't speak to access
through Hibernate and its ilk.

- V

[MarcoBoeringa]

I think you should also have a look at the ESRI Geometry API for Java, as it is OGC ST_Geometry based. It is used in ESRI's Open Source samples for Hadoop that may give you ideas too, an implementation including python Geoprocessing tools as well (also see my PDF here for some brief introduction to the Spatial Framework for Hadoop)

http://esri.github.io/

https://github.com/Esri/geometry-api-java