[postgis-users] Any way to migrate Oracle Spatial tables to Postgis?

[Michael Smedberg]

One quick-and-dirty way to transform data in one DB into a format suitable for another DB is to create a SELECT statement that generates the correct INSERT statement.  You'd have to do this for each table of interest.

 

For example, suppose you had:

 

DROP TABLE IF EXISTS test_gis;

CREATE TABLE test_gis (test_col integer);

SELECT AddGeometryColumn('test_gis', 'centroid', 4269, 'POINT', 2);

INSERT INTO test_gis (test_col, centroid) VALUES (1, GeomFromText('POINT(1 1)', 4269));

 

You could run a SELECT like this:

 

SELECT 'INSERT INTO test_gis (test_col, centroid) VALUES (' || test_col || ', GeomFromText(''' || AsText(centroid) || ''', 4269));' FROM test_gis;

 

Which would return:

 

INSERT INTO test_gis (test_col, centroid) VALUES (1, GeomFromText('POINT(1 1)', 4269));

 

Then you'd run the SELECT, capture the output to a script, and run the script in Oracle.

 

This is tailored to creating PostGIS data- I don't know the Oracle syntax.  You'd need to massage this to match the Oracle syntax.

 

I'm not saying this is the BEST way to do the transformation, but it might be easiest in some cases.

________________________________

From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of JOSE MORAN
Sent: Tuesday, August 28, 2007 3:03 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Any way to migrate Oracle Spatial tables to Postgis?

 

Hi guys,
I'm searching any way to moving oracle spatial tables to Postgis..
I thought Postgis have some tool to do this.. but nothing I have found..
and not luck with documentation either

Please some help in this approach 

Saludos
José

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070828/17f4144c/attachment.html>