Oracle - Converting SDO_GEOMETRY to WKT?

Question

I am very new to oracle spatial.

I have a spatial table with one SDO_GEOMETRY column. After inserting POINT data in this table, I want to retrieve data in WKT format.

Here is what I did:

Inserting data -

INSERT INTO new_test (name, geom) VALUES (
'Test', 
SDO_GEOMETRY(
             2001,
             4326, 
             SDO_POINT_TYPE(12,14,NULL), 
             NULL, 
             NULL));

Fetching data -

SELECT NAME, SDO_UTIL.TO_WKTGEOMETRY(GEOM) AS point FROM NEW_TEST;

Output -

NAME | POINT
-------------
Test | (null)

Why do I get null here? Shouldn't it display the co-ordinate points?

Franconia

Answer 1

Too long for a comment - not sure why it doesn't work for you but I cannot replicate your results:

Oracle Setup:

CREATE TABLE new_test ( name varchar2(20), geom SDO_GEOMETRY );

INSERT INTO new_test (name, geom)
VALUES (
  'Test', 
  SDO_GEOMETRY( 2001, 4326, SDO_POINT_TYPE(12,14,NULL), NULL, NULL)
);

Query:

SELECT NAME, SDO_UTIL.TO_WKTGEOMETRY(GEOM) AS point FROM NEW_TEST;

Output:

NAME POINT                                                                          
---- -----------------
Test POINT (12.0 14.0)

Deforest

It's weird. I'm using oracle locator (The free version of oracle spatial). Also, database is setup in a linux box (fedora). Could any of this be the reason for such output (I highly doubt, but still?) As per docs - SDO_UTIL is supported by oracle locator. – Franconia

That function is definitely part of Locator. What tool are you using to show the results. Clearly not SQLPLUS ... Maybe that tool is just unable to show the result - the string returned is a CLOB. – Benghazi

Another possible is to use the get_wkt() method of the object type: SELECT NAME, t.GEOM.get_wkt() AS point FROM NEW_TEST t; Just don't forget to use an alias for the table (here "t"). – Benghazi

@AlbertGodfrind Yes yes! I am using sqldeveloper. Also, the get_wkt() query gave the same result (null). Am I missing any kind of configuration? I also tried executing query in windows 7 machine (VM). Got the same error. – Franconia

Anyone who could assist me with this? – Franconia

You say you get an error, but I don't see any error, just you get a NULL result. Did you try using sqlplus ? If you really get NULL results that means the geometry column itself contains NULLs. Are you sure your insert completed successfully ? Can you do a SELECT * FROM NEW_TEST ? – Benghazi

Not an ERROR error per se. I meant in-appropriate output (my bad). Yes I tried sqlplus. It gave blank output in POINT column. And yes insert statement did execute successfully. Output for SELECT * FROM NEW_TEST; gives NAME=Test and GEOM=MDSYS.SDO_GEOMETRY. Another query I tried - SELECT SDO_UTIL.TO_WKTGEOMETRY(SDO_UTIL.FROM_WKTGEOMETRY('POINT (12 14)')) AS DATA FROM DUAL;. Output : null – Franconia

Answer 2

The function SDO_UTIL.TO_WKTGEOMETRY seems not to be available in the Express version (Source - Siva Ravada-Oracle):

Oracle Express does not have a Javavm in the database and the WKT conversion routines need this as the feature is implemented as Java stored procedures. So these WKT routines are not supported on the Express edition.

You can still build your own WKT like this (for points):

SELECT name, 'POINT ('||t.X||' '||t.Y||')' AS point FROM NEW_TEST p, TABLE(SDO_UTIL.GETVERTICES(p.GEOM)) t;

Jorin