Skip to main content
Solved

Write OBJECTID attribute of Oracle Spatial registered feature class

  • February 24, 2017
  • 5 replies
  • 196 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.

View original
Did this help you find an answer to your question?

5 replies

erik_jan
Contributor
Forum|alt.badge.img+18
  • 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
erik_jan wrote:

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
david_r wrote:

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
goro13 wrote:
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)'))

Reply


Helpful Members This Week

Community Stats

31,479
Posts
119,340
Replies
39,097
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

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