How to convert CLOB WTK Polygons to sdo_geometry?

Cincocielos_NZ

Hello everyone, I need to migrate information from Postgres(postgis) to Oracle.

I extract information from postgres using st_astext(geom) and upload that information to oracle.

In oracle I have this WKT information

MULTIPOLYGON(((1685401.75223327 6101109.67645498,1685421.07276106 6101104.79940589,1685401.75223327 6101109.67645498)))

And now I need to convert to sdo_geometry, I tried:

select sdo_geometry(geom ,2193) from upload_geom; -- (I got null in columns)

and tried this

SDO_UTIL.FROM_WKTGEOMETRY(geom) -- Geting null

After the conversion I tried also to display the information in Oracle Spatial Studio but I got a message "WITHOUT GEOMETRIES"

What should be the correct way to migrate geospatial information from postgres to oracle, or what is missing?

Cheers.

MG

David Lapp-Oracle

Hi MG, Are you doing this on ADW or ATP-S? If so then you need enable JVM as mentioned in Usage Notes here; https://docs.oracle.com/en/database/oracle/oracle-database/19/spatl/SDO_UTIL-reference.html#GUID-EFDD565A-E565-4463-9CFC-464DC856ECB7

B Hall

Just an observation here...

That multipolygon is just a line, not even a polygon as that needs at least four coordinates.

Rick Anderson-Oracle

I tried this and it worked just fine:

SQL> create table foo(geom mdsys.sdo_geometry);

Table created.

SQL>

SQL> declare

 2   geom mdsys.sdo_geometry;

 3   wkt varchar2(4000);

 4 begin

 5   wkt := 'MULTIPOLYGON(((1685401.75223327 6101109.67645498, 1685421.07276106 6101104.79940589, 1685401.75223327 6101109.67645498)))';

 6   insert into foo values(SDO_UTIL.FROM_WKTGEOMETRY(wkt));

 7   commit;

 8 end;

 9 /

PL/SQL procedure successfully completed.

SQL>

SQL> select * from foo;

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

--------------------------------------------------------------------------------

SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR

AY(1685401.75, 6101109.68, 1685421.07, 6101104.8, 1685401.75, 6101109.68))

The primary reason FROM_WKTGEOMETRY() returns NULL is that JVM is not installed (as David indicated above).

Rick

user3898091

Can't confirm the issue in ORACLE 19.17.0.0.0:

SELECT sdo_util.from_wktgeometry(
         to_clob('MULTIPOLYGON(((1685401.75223327 6101109.67645498, 1685421.07276106 6101104.79940589, 1685401.75223327 6101109.67645498)))')) cgeom 
 FROM dual;

CGEOM
--------------------
MDSYS.SDO_GEOMETRY(2007, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(1685401.75223327, 6101109.67645498, 1685421.07276106, 6101104.79940589, 1685401.75223327, 6101109.67645498))

Of course validation shows ORA-13343 "a polygon geometry has fewer than four coordinates", it has only three coordinates and isn't closed:

If you need the SRID 2193, you could try something like:

SELECT sdo_geometry((cgeom).sdo_gtype, 2193, NULL, (cgeom).sdo_elem_info, (cgeom).sdo_ordinates)
 FROM
 (SELECT sdo_util.from_wktgeometry(
         to_clob('MULTIPOLYGON(((1685401.75223327 6101109.67645498, 1685421.07276106 6101104.79940589, 1685401.75223327 6101109.67645498)))')) cgeom 
 FROM dual);

CGEOM
------
MDSYS.SDO_GEOMETRY(2007, 2193, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(1685401.75223327, 6101109.67645498, 1685421.07276106, 6101104.79940589, 1685401.75223327, 6101109.67645498))

HTH