Skip to main content
  • EN

 

Hi FME experts,

I have stored polygon objects in an Oracle database. The geometry is in the GEOM field. For an interface, I have to convert the GEOM field into a text field. The GEOM field looks like this:

select geom from VIEW_RIWE_2016_BORIS where fid = 70118

sdo_geometry(2003,25832,null,sdo_elem_info_array(1,1003,1),sdo_ordinate_array(510843.904449149,5369878.15541307,510782.847025386,5369803.95356616,510757.074323074,5369773.29550141,510804.270134531,5369734.73477362,510819.559582734,5369752.69084497,510863.309430839,5369806.93171413,510890.267651986,5369838.63447086,510843.904449149,5369878.15541307))

 

The TEXT field must later look like this:

POLYGON ((32563549.456 5934230.837,32563553.002 5934219.989,32563552.58 5934219.778,32563542.348 5934220.82,32563537.637 5934221.725,32563533.431 5934221.663,32563522.282 5934224.668,32563543.799 5934230.72,32563549.027 5934230.188,32563549.456 5934230.837))

With a bit of text editing, I could easily edit the field. My problem now is that the SQL Creator does not display the text from the SQL as an attribute, but only as a graphic object. Although I could read the geometry with CoordinateExtractor, I would have to put it together again correctly.

Is there a way to read the complete GEOM as a simple attribute?

Many thanks in advance.

You need to cast the GEOM column to so-called Well-Known Text, for this you can use the Oracle Spatial function TO_WKTGEOMETRY.

Try something like the following in the SQLCreator:

select SDO_UTIL.TO_WKTGEOMETRY(geom) as GEOM_WKT from my_table

You should then expose GEOM_WKT in the SQLCreator.

If needed, use a GeometryReplacer on GEOM_WKT configured for OGC Well-Known Text to convert the string back to an FME geometry.

Hello, thank you for the answer. I was on vacation and apologize for the late feedback.

 

The SQL command works, unfortunately only partially. I still have problems with the following cases:

- If the polygon has a hole, I get this message:

 

... did not succeed; error was `ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet: java.lang.RuntimeException

ORA-06512: in "MDSYS.SDO_JAVA_STP", Zeile 370

ORA-06512: in "MDSYS.SDO_UTIL", Zeile 3188

 

- If the polygon has a lot of vertices, I get this message:

 

.. error was `ORA-22835: Puffer zu klein für Konvertierung von CLOB zu CHAR oder BLOB zu RAW (tatsächlich: 4289, maximal: 4000)'.

 

Is there a way to work around these Oracle / Java messages

Or

an alternative method to convert directly into FME all coordinates (including holes) to a text?

Hello, thank you for the answer. I was on vacation and apologize for the late feedback.

 

The SQL command works, unfortunately only partially. I still have problems with the following cases:

- If the polygon has a hole, I get this message:

 

... did not succeed; error was `ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet: java.lang.RuntimeException

ORA-06512: in "MDSYS.SDO_JAVA_STP", Zeile 370

ORA-06512: in "MDSYS.SDO_UTIL", Zeile 3188

 

- If the polygon has a lot of vertices, I get this message:

 

.. error was `ORA-22835: Puffer zu klein für Konvertierung von CLOB zu CHAR oder BLOB zu RAW (tatsächlich: 4289, maximal: 4000)'.

 

Is there a way to work around these Oracle / Java messages

Or

an alternative method to convert directly into FME all coordinates (including holes) to a text?

After reading the geometries into FME, you could use a geometryextractor to get the geometry as well known text and see if that bypasses the issues you are seeing when using the oracle conversion.

Hello, thank you for the answer. I was on vacation and apologize for the late feedback.

 

The SQL command works, unfortunately only partially. I still have problems with the following cases:

- If the polygon has a hole, I get this message:

 

... did not succeed; error was `ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet: java.lang.RuntimeException

ORA-06512: in "MDSYS.SDO_JAVA_STP", Zeile 370

ORA-06512: in "MDSYS.SDO_UTIL", Zeile 3188

 

- If the polygon has a lot of vertices, I get this message:

 

.. error was `ORA-22835: Puffer zu klein für Konvertierung von CLOB zu CHAR oder BLOB zu RAW (tatsächlich: 4289, maximal: 4000)'.

 

Is there a way to work around these Oracle / Java messages

Or

an alternative method to convert directly into FME all coordinates (including holes) to a text?

Thank you egomm, that is very close to the desired result.

Here again I have a problem with polygons with holes.

 

The result of the GeometryExtractor as Well Known Text differs in this case from the format description on this page: https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry

 

Here it is written that the holes within a polygon should be separated with parentheses.

 

Unfortunately, the GeometryExtractor in FME does not use these parentheses, so I do not realize afterwards that there was a hole here.

 

Is this an FME error and is there a solution for this?

Thank you egomm, that is very close to the desired result.

Here again I have a problem with polygons with holes.

 

The result of the GeometryExtractor as Well Known Text differs in this case from the format description on this page: https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry

 

Here it is written that the holes within a polygon should be separated with parentheses.

 

Unfortunately, the GeometryExtractor in FME does not use these parentheses, so I do not realize afterwards that there was a hole here.

 

Is this an FME error and is there a solution for this?

Have you tried both version 1.1 and version 1.2 of the OGC standard? It's a setting in the GeometryExtractor of the more recent FME versions.

Have you tried both version 1.1 and version 1.2 of the OGC standard? It's a setting in the GeometryExtractor of the more recent FME versions.

 

Yes, I tested both versions. The output at the examined polygon is identical. The parentheses are missing in both.

Thank you egomm, that is very close to the desired result.

Here again I have a problem with polygons with holes.

 

The result of the GeometryExtractor as Well Known Text differs in this case from the format description on this page: https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry

 

Here it is written that the holes within a polygon should be separated with parentheses.

 

Unfortunately, the GeometryExtractor in FME does not use these parentheses, so I do not realize afterwards that there was a hole here.

 

Is this an FME error and is there a solution for this?

Are you sure that these are valid geometries/donuts? The geometryextractor gives me the correct formatting for a donut

Are you sure that these are valid geometries/donuts? The geometryextractor gives me the correct formatting for a donut

Sorry, the Geometry Extractor works properly! The order of the coordinates of the output is a different one than in my original SQL string, so I searched the parentheses in the wrong place. Sorry!

 

Egomm's proposal is the right solution for me.

Reply


Community Stats

30,108
Posts
114,295
Replies
37,744
Members
Terms & ConditionsCookie 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.