Skip to main content
Solved

Oracle complete GEOM to Text field

R
Forum|alt.badge.img

 

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.

Best answer by rt_gis

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?

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

8 replies

david_r
Celebrity
  • david_rCelebrity
  • April 15, 2019

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.

R
Forum|alt.badge.img
  • rt_gis
  • Author
  • Best Answer
  • May 17, 2019

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?

ebygomm
Influencer
Forum|alt.badge.img+38
  • ebygommInfluencer
  • Influencer
  • May 17, 2019
rt_gis wrote:

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.

R
Forum|alt.badge.img
  • rt_gis
  • Author
  • May 17, 2019
rt_gis wrote:

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?

david_r
Celebrity
  • david_rCelebrity
  • May 17, 2019
rt_gis wrote:

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.

R
Forum|alt.badge.img
  • rt_gis
  • Author
  • May 17, 2019
david_r wrote:

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.
ebygomm
Influencer
Forum|alt.badge.img+38
  • ebygommInfluencer
  • Influencer
  • May 17, 2019
rt_gis wrote:

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

R
Forum|alt.badge.img
  • rt_gis
  • Author
  • May 17, 2019
ebygomm wrote:

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


Helpful Members This Week

Community Stats

31,341
Posts
118,857
Replies
38,930
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.