topic Re: How to store ArcGIS JSON feature into Oracle SDO Geometry database? in ArcGIS Enterprise Questions

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

ab1 — Thu, 22 Jan 2015 09:16:35 GMT

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?

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

TanuHoque — Thu, 22 Jan 2015 17:12:29 GMT

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.

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

ErinBrimhall — Thu, 22 Jan 2015 17:29:26 GMT

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.

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

VinceAngelo — Fri, 23 Jan 2015 06:05:36 GMT

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

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

ab1 — Fri, 23 Jan 2015 08:49:36 GMT

@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.

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

ab1 — Fri, 23 Jan 2015 14:10:00 GMT

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

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

VinceAngelo — Sat, 11 Dec 2021 20:50:32 GMT

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

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

ab1 — Mon, 26 Jan 2015 08:45:33 GMT

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 ...

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

ab1 — Mon, 26 Jan 2015 13:30:53 GMT

It works perfectly! Thanks Vince