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

On 8/20/20 9: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?

You've got to be running some command or SQL statement to load the data... 
What is it?

> The error messages?
> CK - Error insert null to column, looks like DMS is having problem converting Spatial data.
>
> Postgres version?

Your Postgres installation has a version number.  What is it?

> 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
>
>

--
Angular momentum makes the world go 'round.