How to store ArcGIS JSON feature into Oracle SDO Geometry database?

5276
8
01-22-2015 01:16 AM
ab1
by
Occasional Contributor II

Hello,

I'm developing in Java. I retrieve a JSON String from an ArcGIS Online Service Area service. I'd like to store the features returned in the JSON String into an Oracle (SDO Geometry) database.

Is there a fast way of doing that?

0 Kudos
8 Replies
TanuHoque
Esri Regular Contributor

Just wondering whether you checked out "JSON to Features" GP tool?

If you are using ArcObjects, then I believe you can use some of JSON classes and interfaces (like JSONConverterGeometry (ArcObjects Java API)) to do the job.

0 Kudos
ErinBrimhall
Occasional Contributor II

One way would be to convert the JSON geometry definition into a well-known-text (WKT) format, and then use the Oracle SDO_UTIL.FROM_WKTGEOMETRY method to convert the WKT to a Geometry (i.e. inside a stored procedure).

Here is a post on converting JSON geometry text to WKT.

0 Kudos
VinceAngelo
Esri Esteemed Contributor

The problem with passing through WKT is volume.  I've run into limits in SQL statement size using WKT through JDBC.  The JGeometry handle provided by Oracle worked quickly, even for polygons with large numbers of vertices.

- V

0 Kudos
ab1
by
Occasional Contributor II

@Tanu I don't want to use more GP services because retrieving the JSON is already taking a lot of time. I don't use ArcObjects.

@Erin The polygons I'm handling have indeed large number of vertices. Vince says that WKT is not suitable for large amounts of data. So ...

@Vince I tried using JGeometry but I didn't find the library anywhere to download it!

The solution I opted for is parsing the JSON with Java and inserting the SDO_GEOMETRY into the database with SQL.

0 Kudos
ab1
by
Occasional Contributor II

It's not easy!!! Do you know where I can fin JGeometry library ?

0 Kudos
VinceAngelo
Esri Esteemed Contributor

A google resulted in this link: oracle - jgeometry cannot be resolved to a type - Stack Overflow

You can fashion simple SQL in the form:

INSERT INTO foo(geometry) VALUES(

  MDSYS.SDO_GEOMETRY(!gtype!,!srid!,NULL,

      MDSYS.SDO_ELEM_INFO_ARRAY(

          !int_eleminfo_array_members!),

      MDSYS.SDO_ORDINATE_ARRAY(

          !double_ordinate_array_members!)

)  );

but you quickly run out of the maximum number of comma-delimited terms.  Which is where the JGeometry object comes in.  You can plug the gtype, srid, eleminfo array, and ordinate array into the JGeometry constructor, then pass the STRUCT into JDBC.

The code is simple enough, but be sure to validate multi-ring shapes (simple polygon with one exterior ring, polygon with one exterior ring and one interior ring, polygon with one exterior and two interior rings, polygon with two exterior rings, polygon with one interior ring in one of three exterior rings, polygon with multiple interior rings in multiple exterior rings,...).  They're all formatted the same in JSON (array of array of x,y pairs), but manifest differently in the resulting geometry (einfo codes).  If ArcGIS generated the JSON in the first place, the ring orientation should be reliable (first is exterior, interior rings are in reverse order [area calculation by trapezoid rule returns opposite sign]). It's a bit tedious but not difficult.  You may need to reverse the part arrays if Oracle is expecting a different order (left hand rule vs right hand rule; I don't think JSON cares, so long as it's consistent).

I used graph paper to assemble a bunch of boxes and triangles with labels like p1x, p1y, p2x,.... and encoded the JSON from the labels with search/replace.  Assembling a test harness was probably harder than the code (especially because I hosed one of the test cases).

- V

ab1
by
Occasional Contributor II

Thanks Vince

I finally found the JGeometry library (sdoapi.jar)!

I tried to insert the SDO Geometry using SQL but since the polygon has a large number of vertices, I got an SQL exception all the time. I'll try with JGeometry and see if it works ...

0 Kudos
ab1
by
Occasional Contributor II

It works perfectly! Thanks Vince

0 Kudos