search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
1.01k stars 343 forks source link

HINT: "SR" <-- parse error at position 2 within geometry #1466

Open jieguolove opened 2 years ago

jieguolove commented 2 years ago

The table structure of the Oracle database is as follows:

CREATE TABLE "SDE"."MARKET_GRID" (
  "OBJECTID" NUMBER NOT NULL,
  "ttt" NVARCHAR2(10),
  "tttt" NVARCHAR2(50),
  "TOTAL_VALUE" NUMBER(19,8),
  "VALUE_DENSITY" NUMBER(19,8),
  "VALUE_PROPORTION" NUMBER(19,8),
  "TOTAL_VALUE_SCORE" NUMBER(19,8),
  "VALUE_DENSITY_PROPORTION" NUMBER(19,8),
  "HIGH_VALUE_SCORE" NUMBER(19,8),
  "TOTAL_SCORE" NUMBER(8,2),
  "X" NUMBER(19,8),
  "Y" NUMBER(19,8),
  "SHAPE" "SDE"."ST_GEOMETRY",
  "TOTAL_SCORE_NO" NUMBER(10,0)
);

Oracle spatial data exported with the latest version of ora2pg is in the following format and cannot be imported into the postgresql database:

The table structure of the postgresql database is as follows:

CREATE TABLE "sde"."market_grid" (
  "objectid" numeric(38) NOT NULL,
  "ttt" varchar(10) COLLATE "pg_catalog"."default",
  "tttt" varchar(50) COLLATE "pg_catalog"."default",
  "total_value" numeric(19,8),
  "value_density" numeric(19,8),
  "value_proportion" numeric(19,8),
  "total_value_score" numeric(19,8),
  "value_density_proportion" numeric(19,8),
  "high_value_score" numeric(19,8),
  "total_score" numeric(8,2),
  "x" numeric(19,8),
  "y" numeric(19,8),
  "shape" "public"."geometry",
  "total_score_no" int8
);

[root@pgdb data]# cat t.sql

BEGIN;

SET client_encoding TO 'UTF8';
SET synchronous_commit TO off;

COPY market_grid (objectid,"ttt","tttt",total_value,value_density,value_proportion,total_value_score,value_density_proportion,high_value_score,total_score,x,y,shape,total_score_no) FROM STDIN;
43      HR-14           10053   .063    0       2.01    .4      0       3.66    116.70865       40.30065        SRID=;POLYGON ZM (( 116.67699900 40.31124400 0.00000000 nan, 116.67297100 40.30589900 0.00000000 nan, 116.67043600 40.29534500 0.00000000 nan, 116.70738100 40.29101900 0.00000000 nan, 116.73746600 40.28993800 0.00000000 nan, 116.73794200 40.29246300 0.00000000 nan, 116.73871900 40.29428000 0.00000000 nan, 116.73949400 40.29559100 0.00000000 nan, 116.74249100 40.29813800 0.00000000 nan, 116.74385400 40.29903900 0.00000000 nan, 116.74575200 40.30028700 0.00000000 nan, 116.74793800 40.30245200 0.00000000 nan, 116.75028900 40.30377400 0.00000000 nan, 116.75339500 40.30627600 0.00000000 nan, 116.67699900 40.31124400 0.00000000 nan))      212
\.
COMMIT;

[root@pgdb data]# psql --host=192.168.201.40 --port=5432 --username=sde --echo-errors jzshdb -f t.sql -v ON_ERROR_STOP=1
psql:t.sql:2: WARNING:  there is already a transaction in progress
BEGIN
SET
SET
psql:t.sql:10: ERROR:  parse error - invalid geometry
HINT:  "SR" <-- parse error at position 2 within geometry
CONTEXT:  COPY market_grid, line 1, column shape: "SRID=;POLYGON ZM (( 116.67699900 40.31124400 0.00000000 nan, 116.67297100 40.30589900 0.00000000 nan..."
psql:t.sql:10: STATEMENT:  COPY market_grid (objectid,"ttt","tttt",total_value,value_density,value_proportion,total_value_score,value_density_proportion,high_value_score,total_score,x,y,shape,total_score_no) FROM STDIN;
ROLLBACK
WARNING:  there is no transaction in progress

HOW TO FIX ora2pg? thanks!

jieguolove commented 2 years ago

This discovery may be helpful to fix the bug of ora2pg: in oracle: select st_astext (shape) from jzsh_lsd_maint_unionbase_84 where enbid='15181' use the ST_GeomFromText function in the pg to convert this string into a spatial string and store it in the pg. image

in pg: The point coordinate space table can be processed directly, and the area space table needs to replace the string transferred by the st_astext function during ST_GeomFromText to the pg. image

jieguolove commented 2 years ago

temp sloved: sed -i 's/SRID=;//g' .sql sed -i 's/ nan/ -1.797693134862316e+308/g' .sql

darold commented 2 years ago

Hi, thanks for the report. Can you provide me the value of GEOMETRY_EXPORT_TYPE in your ora2Pg.conf?

jieguolove commented 2 years ago

Hi, thanks for the report. Can you provide me the value of GEOMETRY_EXPORT_TYPE in your ora2Pg.conf?

There is no such parameter in my ora2Pg.conf

darold commented 2 years ago

Sorry, this is GEOMETRY_EXTRACT_TYPE

jieguolove commented 2 years ago

Sorry, this is GEOMETRY_EXTRACT_TYPE same, There is no such parameter in my ora2Pg.conf

darold commented 2 years ago

Can you post the configuration with the usual anonymization?

jieguolove commented 2 years ago

data.conf：

PG_VERSION 12 ORACLE_HOME /oracle/app/oracle/product/11.2.0/dbhome_1 ORACLE_DSN dbi:Oracle:host=192.168.1.100;sid=db;port=1521 ORACLE_USER test ORACLE_PWD password SCHEMA test PG_NUMERIC_TYPE 0 PG_INTEGER_TYPE 1 DEFAULT_NUMERIC float NLS_LANG AMERICAN_AMERICA.UTF8

export command:

ora2pg -p -c data.conf -t COPY -a 'TABLE[SDETABLE]' -o sdetable.sql

darold commented 2 years ago

The problem is probably because you are using an ST_GEOMETRY data type and the method to retrieve the SRID is not compatible and commit ec400ad is of no help in this case. For SDO_GEOMETRY Ora2Pg use the following query:

SELECT COALESCE(SRID, $self->{default_srid}) FROM ALL_SDO_GEOM_METADATA
WHERE TABLE_NAME='<tablename>' AND COLUMN_NAME='<colname>' AND OWNER='<owner>';

@jieguolove do you know the query that should be used for ST_GEOMETRY?

anthoninlize commented 1 year ago

Hi @darold

I'm actually encountering the same issue converting from SDE to postgis, with the following spatial configuration. GEOMETRY_EXTRACT_TYPE to INTERNAL, WKB... WKT works however. But I thought I'd share the feedback anyway.

Let me know if you need further info.

logs: FATAL: ERROR: parse error - invalid geometry HINT: "SR" <-- parse error at position 2 within geometry CONTEXT: COPY argo_coverage_year_hs, line 1, column shape: "SRID=;POLYGON (( -87.00000000 -57.00000000, -84.00000000 -57.00000000, -84.00000000 -54.00000000, -..."

conf: AUTODETECT_SPATIAL_TYPE 1

CONVERT_SRID 0

DEFAULT_SRID 4326

GEOMETRY_EXTRACT_TYPE INTERNAL

ST_GEOMETRYTYPE_FUNCTION sde.st_geometrytype ST_SRID_FUNCTION sde.st_srid ST_DIMENSION_FUNCTION sde.st_dimension ST_ASBINARY_FUNCTION sde.st_asbinary ST_ASTEXT_FUNCTION sde.st_astext

fguiet commented 1 year ago

Bonjour @darold ,

J'ai l'impression d'avoir le même problème.

J'ai une table Oracle avec des colonnes SDO_GEOMETRY

CREATE TABLE XXX.ICSP
(
  GEOM_L2E                    MDSYS.SDO_GEOMETRY,
  GEOM_WGS84                  MDSYS.SDO_GEOMETRY,
  GEOM_L93                    MDSYS.SDO_GEOMETRY
)

J'ai mis à jour la table sdo_coord_ref_sys pour que les conversions des srid => EPSG se fasse correctement

La génération des scripts SQL de création des tables est ok

CREATE TABLE icsp (
...
        geom_l2e geometry(POINT,27572),
        geom_wgs84 geometry(POINT,4326),
        geom_l93 geometry(POINT,2154)
) ;

Cependant le SQL produit par la commande:

ora2pg -c config/ora2pg.conf -t COPY -a ICSP -b ./data -o icsp_table.sql -d

Me fournit ce genre de résultat (en gros, j'ai un SRID=; en trop partout...)

COPY icsp (code_icsp,origine_code,nom_icsp,x_icsp,y_icsp,projection_icsp,precision_icsp,date_mise_en_service,date_mise_hors_service,commentaires_icsp,commune_icsp,secteur_activite_principal,code_ape_etablissement,nomutil,x_lamb_93,y_lamb_93,gid,xouvl2e,youvl2e,longitude,latitude,num_departement,agence,region,district,geom_l2e,geom_wgs84,geom_l93,code_naf_2008) FROM STDIN;
SRID=;RHA69_00086       SRID=;GENMOLOSSE        SRID=;SOCIETE DU DEPOT DE ST PRIEST     SRID=;800930    SRID=;2079910   SRID=;5 SRID=;3 \N      \N      SRID=;Siret : 399087220 SRID=;69290     SRID=;4 SRID=;51.5A     SRID=;DRIRERHA  SRID=;849132    SRID=;6511842   SRID=;1641      SRID=;800930    SRID=;2079910   SRID=;4.91632963296735  SRID=;45.6898639249745  SRID=;69        SRID=;AGO5      SRID=;REG82     SRID=;D SRID=27572;POINT (800929.987299754 2079909.99961538)    SRID=4326;POINT (4.91632963296735 45.6898639249745)     \N      \N
SRID=;RHA69_00087       SRID=;GENMOLOSSE        SRID=;KODAK     SRID=;790050    SRID=;2070290   SRID=;5 SRID=;3 \N      \N      SRID=;Siret : inconnu   SRID=;69096     SRID=;5 SRID=;74.8A     SRID=;DRIRERHA  SRID=;838180    SRID=;6502324   SRID=;1642      SRID=;790050    SRID=;2070290   SRID=;4.77290480645298  SRID=;45.6064832261955  SRID=;69        SRID=;AGO5      SRID=;REG82     SRID=;D SRID=27572;POINT (790049.987283841 2070289.99963591)    SRID=4326;POINT (4.77290480645298 45.6064832261955)     \N      \N
SRID=;RHA69_00089       SRID=;GENMOLOSSE        SRID=;SOCIETE TEXTILE INDUSTRIELLE DU RHONE - STIR      SRID=;798210    SRID=;2086890   SRID=;5 SRID=;3 \N      \N      SRID=;Siret : inconnu   SRID=;69383     SRID=;6 SRID=;17.3Z     SRID=;DRIRERHA  SRID=;846474    SRID=;6518839   SRID=;1643      SRID=;798210    SRID=;2086890   SRID=;4.88433476257371  SRID=;45.7534250160617  SRID=;69        SRID=;AGO5      SRID=;REG82     \N      SRID=27572;POINT (798209.987314235 2086889.99962165)    SRID=4326;POINT (4.88433476257371 45.7534250160617)     \N      \N
SRID=;RHA69_00092       SRID=;GENMOLOSSE        SRID=;STATION SERVICE ELF - Tassin      SRID=;789440    SRID=;2086820   SRID=;5 SRID=;3 \N      \N      SRID=;Siret : inconnu   SRID=;69244     SRID=;14        SRID=;50.5Z     SRID=;DRIRERHA  SRID=;837712    SRID=;6518844   SRID=;1644      SRID=;789440    SRID=;2086820   SRID=;4.77165119444173  SRID=;45.7552955085604  SRID=;69        SRID=;AGO5      SRID=;REG82     SRID=;D SRID=27572;POINT (789439.987316611 2086819.99963918)    SRID=4326;POINT (4.77165119444173 45.7552955085604)     \N      \N

Je vais probablement utiliser la solution suivante temporairement

sed -i 's/SRID=;//g' *.sql

Une idée?

Merci Frederic