将SPATIAL数据从Oracle迁移到Postgresql [英] Migrate SPATIAL data from Oracle to Postgresql

问题描述

我正在尽力将空间数据库从Oracle迁移到Postgresql，但失败了.

I am trying my best to migrate a spatial database from Oracle to Postgresql and failing miserably.

从这里的上一个问题可以看出，我尝试了许多不同的途径，但没有一个起作用.有人可以告诉我这样做的一种相对轻松的方式，因为我现在对此一无所知.

I have tried many different avenues as you can see from my previous question on here and none are working. Can someone please tell me of a relatively painless way of doing this as I am now clueless with it.

我曾尝试使用第三方软件(例如SwisSQL)，但是失败了，并出现了许多错误.我试过创建充满insert语句的文件，然后创建一个C#程序来解析这些文件，并用相关的postgis替换oracle空间类型，但是由于这些文件的绝对大小，在最基本的替换上由于内存不足异常而失败.其中一些表中有超过200万条记录，因此您可以想象其中每个表包含插入的文件的大小.

I have tried using 3rd party software such as SwisSQL but this failed with a multitude of errors. I have tried creating files full of insert statements and then creating a C# program to parse these and replace the oracle spatial types with relevant postgis ones and that failed with an out of memory exception on the most basic replaces due to the sheer size of these files. Some of the tables have over 2 million records in them so you can imagine the size of the file containing inserts for each of those.

对于这个问题，我感到非常绝望，因为它严重阻碍了该项目的进展.我需要Postgresql中的数据，因为我正在编写需要与数据库无关的软件，这意味着必须对每个数据库上的真实数据进行测试.

I am getting pretty desperate for a solution to this as it is seriously hampering progress on this project. I need the data in Postgresql because I am writing software that needs to be database agnostic which means it has to be tested on real data on each database.

张开双臂欢迎任何想法.如果不是从空间角度考虑的话，它会更加简单，并且很可能在现在完成.

Any ideas whatsoever would be welcomed with open arms. If it wasnt for the spatial aspect of this it would have been far more simple and most likely done by now.

这是Toad为Oracle生产的插入示例，用于介绍数据复杂性.

Here is an example of an insert produced by Toad for Oracle to give an idea of data complexity.

Insert into CLUSTER_1000M
(CLUSTER_ID, CELL_GEOM, CELL_CENTROID)
Values
(4410925, 
"MDSYS"."SDO_GEOMETRY"(2003,81989,NULL,
"MDSYS"."SDO_ELEM_INFO_ARRAY"(1,1003,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
"MDSYS"."SDO_ORDINATE_ARRAY"(80000,103280,81000,104280,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)), 
"MDSYS"."SDO_GEOMETRY"(2001,81989,
"MDSYS"."SDO_POINT_TYPE"(80500,103780,NULL),NULL,NULL));

推荐答案

有两种流行的选择:开源ogr2ogr或安全软件.

There are two popular options: open source ogr2ogr or a commercial offering from Safe Software.

这是我采用ogr2ogr解决方案的方式.

Here is how I'd approach the ogr2ogr solution.

首先，您需要使用正确的工具: GDAL/OGR .如果您使用的是Unix，请使用正确的库进行编译以获得Oracle支持(默认情况下未启用).但是我假设您使用的是Windows.获取开源地理空间工具的最简单方法是使用 OSGeo4W .要启用Oracle对GDAL/OGR的支持，还需要使用高级安装"模式在setup.exe中选择gdal-oracle10g软件包.有关Oracle软件包的更多信息，请参见此页面，并注意您还需要提供非免费的OCI.DLL.工作时，您应该看到驱动程序名称以ogr2ogr --formats出现.

First, you need to have the right tools: GDAL/OGR. If you are on Unix, compile using the right libraries to get Oracle support (not enabled by default). But I'm going to assume you are on Windows. The simplest way to get open source geospatial tools is with OSGeo4W. To enable Oracle support for GDAL/OGR, you need to also select the gdal-oracle10g package in setup.exe with the "Advanced Install" mode. More info about the Oracle package is at this page, and note that you also need to supply the non-free OCI.DLL. When working, you should see the driver name appear with ogr2ogr --formats.

您从OSGeo4W Shell获得的基本命令应类似于:

Your basic command from the OSGeo4W Shell should look something like:

ogr2ogr -f "PostgreSQL" PG:"host=localhost user=someuser dbname=somedb password=password port=5432" OCI:someuser/password layername

以下是有关GDAL/OGR的更多信息:

Here is more info on GDAL/OGR:

• ogr2ogr命令用法: http://www.gdal.org/ogr2ogr.html
• Oracle驱动程序: http://www.gdal.org/drv_oci.html
• PostgreSQL/PostGIS驱动程序: http://www.gdal.org/drv_pg.html

• ogr2ogr command usage: http://www.gdal.org/ogr2ogr.html
• Oracle Driver: http://www.gdal.org/drv_oci.html
• PostgreSQL/PostGIS driver: http://www.gdal.org/drv_pg.html

这篇关于将SPATIAL数据从Oracle迁移到Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！