ora2pg
ora2pg copied to clipboard
darold
HINT: "SR" <-- parse error at position 2 within geometry
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!
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.
temp sloved: sed -i 's/SRID=;//g' *.sql sed -i 's/ nan/ -1.797693134862316e+308/g' *.sql
Hi, thanks for the report. Can you provide me the value of GEOMETRY_EXPORT_TYPE in your ora2Pg.conf?
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
Sorry, this is GEOMETRY_EXTRACT_TYPE same, There is no such parameter in my ora2Pg.conf
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
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?
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
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
