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
