Skip to main content
Solved

Write OBJECTID attribute of Oracle Spatial registered feature class

  • February 24, 2017
  • 5 replies
  • 247 views
G

I’m trying to create an FME process that reads a FGDB and
writes the data to an Esri registered feature class stored in an Oracle
database. I have issues in finding the proper way of doing it using the Oracle
Spatial Object writer. The trouble is caused by the OBJECTID: how to populate
it?

I’ve
tried using an SQL statement (Overrides Feature Operation) like 

INSERT INTO FEATURE_CLASS (OBJECTID, COL1, COL2) VALUES(sde.gdb_util.next_rowid('OWNER', 'FEATURE_CLASS'),
:COL1, :COL2)

With this approach the OBJECTID column gets populated but I’m
failing in writing the geometry (the geometry in Oracle is stored as
SDO_GEOMETRY). As a workaround I’ve defined another process using the same writer
to update the geometry column only (Feature Operation = Updated instead of
using the SQL Statement Overrides Feature Operation option).

Is there a way of doing this in one step only?

I know it can be done by using Esri Geodatabase (ArcSDE
Geodb) writer but for some reason the process takes about 20 minutes in
comparison with the 3 minutes of the first scenario and, on top of that, the feature class
created loads slower in ArcMap.

Best answer by erik_jan

Use the SQLExecutor to get the OBJECTID value for each feature as in the SQL statement above.

Then use a regular Oracle Spatial Writer to write the features to the database.

I have used this route before and as you stated it is faster than using the ESRI writers.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

5 replies

erik_jan
Contributor
Forum|alt.badge.img+23
  • erik_janContributor
  • Contributor
  • Best Answer
  • February 24, 2017

Use the SQLExecutor to get the OBJECTID value for each feature as in the SQL statement above.

Then use a regular Oracle Spatial Writer to write the features to the database.

I have used this route before and as you stated it is faster than using the ESRI writers.

david_r
Celebrity
  • david_rCelebrity
  • February 24, 2017

What you're describing is a bit risky because there's always a chance that you'll end up writing a geometry that isn't 100% compatible with SDE later on, so I'd not recommend it.

That said, it is possible to convert the geometries to e.g. OGC WKT using the GeometryExtractor and then doing something like

insert into my_table (id, geom) values (@Value(ID), '@Value(WKT)')

Just be aware that e.g. Oracle has a limit of 4000 characters for string literals, which you're going to hit pretty quickly if you have large geometries. There are some workarounds, but you've gotta to be pretty motivated to jump those hoops.

david_r
Celebrity
  • david_rCelebrity
  • February 24, 2017

Use the SQLExecutor to get the OBJECTID value for each feature as in the SQL statement above.

Then use a regular Oracle Spatial Writer to write the features to the database.

I have used this route before and as you stated it is faster than using the ESRI writers.

That's the best solution, for sure.
G
  • goro13
  • Author
  • February 24, 2017

What you're describing is a bit risky because there's always a chance that you'll end up writing a geometry that isn't 100% compatible with SDE later on, so I'd not recommend it.

That said, it is possible to convert the geometries to e.g. OGC WKT using the GeometryExtractor and then doing something like

insert into my_table (id, geom) values (@Value(ID), '@Value(WKT)')

Just be aware that e.g. Oracle has a limit of 4000 characters for string literals, which you're going to hit pretty quickly if you have large geometries. There are some workarounds, but you've gotta to be pretty motivated to jump those hoops.

I’ve had an attempt with a similar scenario as the one
described by you but I included a transformation to SDO_GEOMERTY like
INSERT INTO FEATURE_CLASS (OBJECTID, COL1, COL2, GEOM) VALUES(sde.gdb_util.next_rowid('OWNER', 'FEATURE_CLASS'),
:COL1, :COL2, mdsys.sdo_util.from_wktgeometry(:WKT))


I didn’t manage to
make it work as I was getting an unhandled error raised by the database.

 



I don’t understand how your SQL statement can work if GEOM
field is defined as SDO_GEOEMTRY object at the database level and the insert value is  of OGC WKT type.

 

 

 

david_r
Celebrity
  • david_rCelebrity
  • February 24, 2017
I’ve had an attempt with a similar scenario as the one
described by you but I included a transformation to SDO_GEOMERTY like
INSERT INTO FEATURE_CLASS (OBJECTID, COL1, COL2, GEOM) VALUES(sde.gdb_util.next_rowid('OWNER', 'FEATURE_CLASS'),
:COL1, :COL2, mdsys.sdo_util.from_wktgeometry(:WKT))


I didn’t manage to
make it work as I was getting an unhandled error raised by the database.

 



I don’t understand how your SQL statement can work if GEOM
field is defined as SDO_GEOEMTRY object at the database level and the insert value is  of OGC WKT type.

 

 

 

You're right, of course, I forgot to include the from_wktgeometry() function above.  Have you tried inserting the values directly using the @Value(ATTRIBUTE_NAME) syntax rather than bind variables? Example:

 

INSERT INTO FEATURE_CLASS (OBJECTID, COL1, COL2, GEOM) 
VALUES(sde.gdb_util.next_rowid('OWNER', 'FEATURE_CLASS'),
@Value(COL1), @Value(COL1), mdsys.sdo_util.from_wktgeometry('@Value(WKT)'))

Community Stats

32,154
Posts
122,047
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.