Loading Oracle Spatial Data to Postgresql

Started by Ko, Christinaover 5 years ago10 messagesgeneral

Jump to latest

Ko, Christina

christina.ko@lmco.com

over 5 years ago

Hi All,

I am running into issue using DMS to load oracle spatial data (oracle 11.2.0.3) to postgresql.

Oracle table:

CREATE TABLE Spatial_Tbl
ID NUMBER(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

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.

Thanks in advanced for your help!

Christina

Adrian Klaver

adrian.klaver@aklaver.com

over 5 years ago

In reply to: Ko, Christina (#1)

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?

Postgres version?

Postgres table schema?

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?

Thanks in advanced for your help!

Christina

--
Adrian Klaver
adrian.klaver@aklaver.com

Gilles Darold

gilles@darold.net

over 5 years ago

In reply to: Ko, Christina (#1)

Re: Loading Oracle Spatial Data to Postgresql

Le 20/08/2020 ï¿½ 16:30, Ko, Christina a ï¿½critï¿½:

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

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.

Thanks in advanced for your help!

Christina

Hi,

You can either use oracle_fdw or Ora2Pg. The first is a FDW extension
that will allow you to use a foreign table to upload the data into your
destination table and the second tool export data from the Oracle
database to plain text file or to your PostgreSQL table directly.

Best regards,

--
Gilles Darold
http://www.darold.net/

Ko, Christina

christina.ko@lmco.com

over 5 years ago

In reply to: Adrian Klaver (#2)

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

-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, August 20, 2020 10:42 AM
To: Ko, Christina (US) <christina.ko@lmco.com>; pgsql-general@lists.postgresql.org
Cc: Ho, Chuong <chuongho@amazon.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.

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@aklaver.com

Ron

ronljohnsonjr@gmail.com

over 5 years ago

In reply to: Ko, Christina (#4)

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.klaver@aklaver.com>
Sent: Thursday, August 20, 2020 10:42 AM
To: Ko, Christina (US) <christina.ko@lmco.com>; pgsql-general@lists.postgresql.org
Cc: Ho, Chuong <chuongho@amazon.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.klaver@aklaver.com

--
Angular momentum makes the world go 'round.

Ko, Christina

christina.ko@lmco.com

over 5 years ago

In reply to: Gilles Darold (#3)

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

From: Gilles Darold <gilles@darold.net>
Sent: Thursday, August 20, 2020 10:54 AM
To: Ko, Christina (US) <christina.ko@lmco.com>; pgsql-general@lists.postgresql.org
Cc: Ho, Chuong <chuongho@amazon.com>
Subject: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

Le 20/08/2020 à 16:30, Ko, Christina a écrit :
Hi All,

I am running into issue using DMS to load oracle spatial data (oracle 11.2.0.3) to postgresql.

Oracle table:

CREATE TABLE Spatial_Tbl
ID NUMBER(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

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.

Thanks in advanced for your help!

Christina

Hi,

You can either use oracle_fdw or Ora2Pg. The first is a FDW extension that will allow you to use a foreign table to upload the data into your destination table and the second tool export data from the Oracle database to plain text file or to your PostgreSQL table directly.

Best regards,

Gilles Darold

http://www.darold.net/

I have just installed ora2pg and will see if it works. I believe I have to specify my setting in the config, do you have any suggestion of what I have to set in the config file to load spatial data from oracle to postgresql. Thank you.

Christina

Adrian Klaver

adrian.klaver@aklaver.com

over 5 years ago

In reply to: Ko, Christina (#4)

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.klaver@aklaver.com>
Sent: Thursday, August 20, 2020 10:42 AM
To: Ko, Christina (US) <christina.ko@lmco.com>; pgsql-general@lists.postgresql.org
Cc: Ho, Chuong <chuongho@amazon.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.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

Gilles Darold

gilles@darold.net

over 5 years ago

In reply to: Ko, Christina (#6)

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

Le 20/08/2020 ï¿½ 17:57, Ko, Christina a ï¿½critï¿½:

*From:* Gilles Darold <gilles@darold.net>
*Sent:* Thursday, August 20, 2020 10:54 AM
*To:* Ko, Christina (US) <christina.ko@lmco.com>;
pgsql-general@lists.postgresql.org
*Cc:* Ho, Chuong <chuongho@amazon.com>
*Subject:* EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

Le 20/08/2020 ï¿½ 16:30, Ko, Christina a ï¿½critï¿½:

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

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.

Thanks in advanced for your help!

Christina

Hi,

You can either use oracle_fdw or Ora2Pg. The first is a FDW extension
that will allow you to use a foreign table to upload the data into
your destination table and the second tool export data from the Oracle
database to plain text file or to your PostgreSQL table directly.

Best regards,

--
Gilles Darold
http://www.darold.net/
I have just installed ora2pg and will see if it works. ï¿½I believe I
have to specify my setting in the config,ï¿½ do you have any suggestion
of what I have to set in the config file to load spatial data from
oracle to postgresql.ï¿½ Thank you.
Christina

Well if you start with Ora2Pg your bible is
http://www.ora2pg.com/documentation.html and especially this chapter
that will make you save time
http://www.ora2pg.com/documentation.html#Generate-a-migration-template

After reading that as a shortcut once your ora2pg.conf is configured to
communicate with Oracle and if you just want to migrate this single table:

ora2pg -c config/ora2pg.conf -t COPY -b data/ -o data.sql -a
'SPATIAL_TB1'

I also recommend you to read
http://www.darold.net/confs/ora2pg_the_hard_way.pdf, it is a bit old but
plenty of useful information on Ora2PG use.

Best regards,

--
Gilles Darold
http://www.darold.net/

Ko, Christina

christina.ko@lmco.com

over 5 years ago

In reply to: Gilles Darold (#8)

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

From: Gilles Darold <gilles@darold.net>
Sent: Thursday, August 20, 2020 1:59 PM
To: Ko, Christina (US) <christina.ko@lmco.com>; pgsql-general@lists.postgresql.org
Cc: Ho, Chuong <chuongho@amazon.com>
Subject: Re: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

Le 20/08/2020 à 17:57, Ko, Christina a écrit :

From: Gilles Darold <gilles@darold.net><mailto:gilles@darold.net>
Sent: Thursday, August 20, 2020 10:54 AM
To: Ko, Christina (US) <christina.ko@lmco.com><mailto:christina.ko@lmco.com>; pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
Cc: Ho, Chuong <chuongho@amazon.com><mailto:chuongho@amazon.com>
Subject: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

Le 20/08/2020 à 16:30, Ko, Christina a écrit :
Hi All,

I am running into issue using DMS to load oracle spatial data (oracle 11.2.0.3) to postgresql.

Oracle table:

CREATE TABLE Spatial_Tbl
ID NUMBER(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

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.

Thanks in advanced for your help!

Christina

Hi,

You can either use oracle_fdw or Ora2Pg. The first is a FDW extension that will allow you to use a foreign table to upload the data into your destination table and the second tool export data from the Oracle database to plain text file or to your PostgreSQL table directly.

Best regards,

Gilles Darold

http://www.darold.net/

Christina

Well if you start with Ora2Pg your bible is http://www.ora2pg.com/documentation.html and especially this chapter that will make you save time http://www.ora2pg.com/documentation.html#Generate-a-migration-template

After reading that as a shortcut once your ora2pg.conf is configured to communicate with Oracle and if you just want to migrate this single table:

ora2pg -c config/ora2pg.conf -t COPY -b data/ -o data.sql -a 'SPATIAL_TB1'

I also recommend you to read http://www.darold.net/confs/ora2pg_the_hard_way.pdf, it is a bit old but plenty of useful information on Ora2PG use.

Best regards,

Gilles Darold

http://www.darold.net/

That really helps. Thank you.

Christina

#10

Laurenz Albe

laurenz.albe@cybertec.at

over 5 years ago

In reply to: Ko, Christina (#1)

Re: Loading Oracle Spatial Data to Postgresql

On Thu, 2020-08-20 at 14:30 +0000, Ko, Christina wrote:

I am running into issue using DMS to load oracle spatial data (oracle 11.2.0.3) to postgresql.

Oracle table:

CREATE TABLE Spatial_Tbl
ID NUMBER(38,9),
P_ID NUMBER(38,9),
GEOMETRY MDSYS.SDO_GEOMETRY

If the geometries are not exotic, oracle_fdw would do the trick.

You won't be able to install it on a hosted database though.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com