On 8/20/20 7:57 AM, Ko, Christina wrote:
-----Original Message-----
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Thursday, August 20, 2020 10:42 AM
To: Ko, Christina (US) <christina.ko@xxxxxxxx>; pgsql-general@xxxxxxxxxxxxxxxxxxxx
Cc: Ho, Chuong <chuongho@xxxxxxxxxx>
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.klaver@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
