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

[jieguolove]

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]

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.

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.

[jieguolove]

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

[darold]

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

[jieguolove]

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]

Sorry, this is GEOMETRY_EXTRACT_TYPE

[jieguolove]

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

[darold]

Can you post the configuration with the usual anonymization?

[jieguolove]

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]

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]

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]

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