This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,699 Users
  • 2,269,776 Discussions
  • 7,916,823 Comments

Discussions

How to convert CLOB WTK Polygons to sdo_geometry?

Cincocielos_NZ
Cincocielos_NZ Member Posts: 1 Green Ribbon

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

Tagged:

Answers

  • David Lapp-Oracle
    David Lapp-Oracle Member Posts: 68 Employee

    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
    B Hall Oklahoma City, OKMember Posts: 327 Silver Badge

    Just an observation here...

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

  • Rick Anderson-Oracle
    Rick Anderson-Oracle Member Posts: 162 Employee

    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
    user3898091 Member Posts: 1 Red Ribbon
    edited Dec 8, 2022 12:08PM

    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