Re: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

On 8/20/20 7:57 AM, Ko, Christina wrote:
>
>
> -----Original Message-----
> From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> Sent: Thursday, August 20, 2020 10:42 AM
> To: Ko, Christina (US) <christina(dot)ko(at)lmco(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org
> Cc: Ho, Chuong <chuongho(at)amazon(dot)com>
> Subject: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql
>
> On 8/20/20 7:30 AM, Ko, Christina wrote:
>> Hi All,
>>
>> I am running into issue using DMS to load oracle spatial data (oracle
>> 11.2.0.3) to postgresql.
>>
>> Oracle table:
>>
>> CREATETABLESpatial_Tbl
>>
>> IDNUMBER(38,9),
>>
>>   P_ID NUMBER(38,9),
>>
>> GEOMETRY  MDSYS.SDO_GEOMETRY
>>
>> AWS DMS calls the SDO2GEOJSON custom function trying to load the data,
>> but it failed and the Geometry columns in Postgresql was empty
>
> The query?
>
> The error messages?
> CK - Error insert null to column, looks like DMS is having problem converting Spatial data.

Where does the above error appear?

Does SDO2GEOJSON work when run on the Oracle database?

As I understand it SDO2GEOJSON converts SDO_GEOMETRY object to
GeoJSON(https://spatialdbadvisor.com/oracle_spatial_tips_tricks/206/sdo2geojson)

Not sure how well that is going to work even if the function runs as you
are trying to put JSON into a Postgres(PostGIS) geometry field.

>
> Postgres version?
>
> Postgres table schema?
> CK -
> CREATE TABLE modstar.p_mstdbgeospatial
> (
> id numeric(38,9) NOT NULL,
> p_id numeric(38,9),
> geometry geometry,
> CONSTRAINT p_mstdbgeospatial_pkey PRIMARY KEY (id)
> )
> WITH (
> OIDS = FALSE
> )
>
>>
>> I also tried using SDO_UTIL.TO_GEOJSON utility and didn't work either.
>>
>> I am looking for suggestions of how to load the geometry data from
>> oracle 11.2.0.3 to oracle.
>
> I'm assuming you mean oracle 11.2.0.3 to Postgres?
> CK - Correct. oracle 11.2.0.3 to Postgres
> Thank you for your response Adrian.
>
>>
>> Thanks in advanced for your help!
>>
>> Christina
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com