Replacing faulty Z coordinates in an easy manner ?

There are some interesting ideas here. The first one includes python code, the second one uses only FME transformers. As you only want to replace 0 values, you can simplify both solutions.

Note the commas in Text To Replace and Replacement Text (you don't want to replace 10).

Also make sure the incoming features are 3D, otherwise you may inadvertently replace a vertex's y-value.

I have been looking into something similar, although I looked at WKT. In WKT I can use " 0)" and " 0," (first character is space) to find zero-Z's. GeoJSON may simplify that a bit.

Yes, forced 3D is essential to not accidentally remove a Y value of 0.

Alas, GeometryReplacer reintroduces a faulty Z in geometries, that feature mixed-Z nodes.

This line string (in WKT): LINESTRING (571385.6196 6138491.615,571392.3857 6138491.1588 109.433,571398.7318 6138488.917 109.209)

has one node without a Z, and two nodes with a Z.

After using GeometryReplacer on it the geometry looks like this:

and exports to GeoJSON like this:

{"type":"LineString","coordinates":[[571385.6196,6138491.615,0],[571392.3857,6138491.1588,109.433],[571398.7318,6138488.917,109.209]]}

Ah well, back to the drawing board :-(

Ps!

MS/SQL do respect mixed-Z geometries. Here's the result of [geom].AsTextZM():

LINESTRING (571385.6196 6138491.615, 571392.3857 6138491.1588 109.433, 571398.7318 6138488.917 109.209)

So I've built a small test workspace, extracting all geometries and IDs, exporting ther geometries to WKT, removing all Z=0 references, and updating the geometries in my MS/SQL using SQLExecutor and a command along this line:

UPDATE @Value(fme_feature_type) SET [geometri] = geometry::STGeomFromText('@Value(_geometry)',25832) WHERE [ID] = @Value(ID)

I know for a fact, because I've tested it, that this should respect missing Z's in line/polygon geometries, that do have other Z values. The downside is that it's an extremely slow method. Something like 10 geometries per second, and I have more than 600K+ to process !

GeometryReplacer cannot be trusted, as it inserts dummy Z values if other Z values are present, so I cannot use writers to update my geometries. 

Using PythonCaller to utilize feature functions getAllCoordinates, resetCoords, and addCoordinates unfortuately results in the same inserts of dummy Z values.

Can you not do your update via a single SQLExecutor statement for the entire table rather than on a line by line basis?

Out of interest, do these geometries evaluate as true with STIsValid() ?

Can you use SQL To Run After Write (Parameters, Advanced)?

No, I cannot use a single UPDATE statement, as @(fme_feature_type) is highly variable.

But I did manage to use a writer instead in update-mode, and that gave me much better performance.

The geometries are fairly simple, so it's not a case of invalidity.

I'm not sure what you mean by this.

You can write GML (or GeoJSON, or WKT, or something else) to a column in your database, and afterwards let the database translate this value to geometry. You can do this by passing SQL commands to the database in 'SQL To Run After Write' in your Writer or FeatureWriter.

A few years ago I described a possible process for Oracle Non-Spatial in this post.

That kinda defeats the whole purpose of using FME, doesn't it ? I was looking for a way to use FME to have it run as a post-processing task, after FME is used to import new data. Using FME to just run T-SQL isn't very productive, imho, unless it's absolutely necessary. I'm using writer "before" and "after" SQL to truncate tables before import, and run MakeValid on data after import. But in this case it makes little sense to go that way. Sorry.

Besides, the before/after scripts are completely static, and doesn't allow anything but simple text substitutions.

UPDATE @Value(fme_feature_type) SET [geometri] = geometry::STGeomFromText('@Value(_geometry)',25832) WHERE [ID] = @Value(ID)

I did manage to switch to a writer in update-mode, which increased the performance significantly :-)

Hi ebygomm. I did end up going with WKT-replacement in the database, and opted out of having mixed Z-presence in multi-node geometries (using the system defined "null" value of -99 in this case). But using "-nan" is an interesting option, so I'm giving you the "best answer" in case others need this in their projects.