Skip to main content
Question

Update multiple geometries in Oracle Spatial

J
Forum|alt.badge.img+1

I have a number of tables in Oracle Spatial that need to be synced from Smallworld. The tables contain multiple geometry columns.

We have a requirement for a nightly job that updates the ~80 tables, and are passing in only records that have been updated over the course of the day. In some cases there may be null geometries for some of the features (ex. in one of the two geometry columns), depending on the data changes.

When using Feature Operation: Update and Table Handling: Use Existing, the translation fails with the error that one of the geometries cannot be found in the current feature: "Spatial Column 'FIBER_OPTIC_LOCATION' of type 'geometry' cannot be found in current feature."

Is this the expected behaviour for performing updates to Oracle Spatial DBs with multiple geometries? Is there a way to get around this to update records that may contain multiple geometries?

When inserting records and truncating the existing table, the translation completes successfully, however there are too many records to truncate all tables on a nightly basis.

Thanks for your suggestions.

 

2 replies

nag
Contributor
Forum|alt.badge.img
  • nagContributor
  • Contributor
  • August 14, 2019

Hello,

Refer following document to handle multiple geometries,

https://cdn.safe.com/training/course-materials/Smallworld-Pathway-Training-2015.pdf

Thanks...

M
Forum|alt.badge.img+2
  • markatsafe
  • August 27, 2019

Working with multiple spatial columns is not straight forward and particularly when it comes to updating the spatial columns.

 

First, here's how updates are supposed to work when working with multiple spatial columns. There are three update scenarios.

1. update both spatial columns

2 update both spatial columns, but set one to a <null> geometry

3. only update one of the spatial columns leaving the other one unchanged.

Prerequesites for writing multiple spatial columns are discussed in this article: Writing to Database Tables that contain Multiple Geometry Columns

When writing updates to multiple spatial column tables (fme_db_operation = UPDATE) that have multiple geometries then you can expect the following behaviour:

 

Say we have named spatial columns G1 & G2

1. update both geometries: G1 & G1

 

Create an aggregate. Use MultipleGeometrySetter to flag the aggregate as a multiple geometry. G1 & G2 are on the feature as an aggregate. G1 & G2 are both updated

2. update both geometries and set G2 to <null> geometry: G2 needs to be on the feature bust as a <null> geometry. You can use VertexRemover to remove the coordinates but keep the geometry (i.e. G2 = null) Create the aggregate of G1 & G2 . G1 & G2 are both updated & G2 = <null>

3. Update G1, do NOT update G2. Create an aggregate of G1 and MultipleGeometrySetter. G2 is missing from the feature. G1 is updated and G2 should be left unchanged.

If you try 3. with Oracle Spatial then you get the error:

 

ERROR |Spatial Column 'G2' of type 'geometry' cannot be found in current feature

 

We're trying to fix this last error but in this case we should be using approach 2. and you should encounter the error. (it's OK on SQL Server and other databases that support multiple spatial columns)

 

 

Trying to address @jnfung 's error - I think the error occurs because the feature looks like 3 instead of 2. . If the <null> geometry (say G2) is missing when the data is read from Smallworld then you need to add G2 back, with a <null>. To do this, you'll need to do something like;

 

- make a copy of the original feature.

 

- On the copy, use VertexRemover to remove all the coordinates on the copy.

 

- use GeometryPropertySetter to set the name of the copy to G2

 

- use Aggregator to combine the original (G1) and the copy (where G2 = <null>)

 

Use Feature caching to help you piece together the correct structure. Your feature geometry will look something like this:

 

feature geometry

Reply


Helpful Members This Week

Community Stats

30,971
Posts
117,398
Replies
38,757
Members

Latest FME

Terms & ConditionsCookie settings

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.