ili2ora error on import option

flvgdeveloper commented 2 years ago

Hi,

when i try to import xtf to oracle schema generate this error

ORA-06512: en "MDSYS.SDO_JAVA_STP", línea 71 ORA-06512: en "MDSYS.SDO_UTIL", línea 6410 ORA-06512: en "MDSYS.SDO_GEOMETRY", línea 195 for every row

the comand submited is

java -jar ili2ora-4.7.0.jar --import --disableRounding --log C:\Users\fredy.ilisuite\log\20221026_064453.log --dbhost 10.1. --dbport 1521 --dbschema OW_CAPTURA --dbusr OW_CAPTURA --dbservice xfilesdb --dbpwd ** --modeldir /home/ubuntu/ModeloCaptura/ModeloCaptura --disableValidation C:\proyectos\TenjoActualizacion2022\tenjo2.xtf

any help will be appreciated thanks

beistehen commented 2 years ago

Can you run the tool with the --trace option and post the log file here?

flvgdeveloper commented 2 years ago

Hi thanks for your help attach the file 20221026_222624.zip

beistehen commented 2 years ago

Perfect. In line 29'252 the error is raised for the first time:

Error:   ORA-29532: llamada Java terminada por una excepción Java no resuelta: 
    java.lang.RuntimeException: oracle.spatial.util.GeometryExceptionWithContext: Unknown WKB label: -2147483642.
ORA-06512: en "MDSYS.SDO_JAVA_STP", línea 71
ORA-06512: en "MDSYS.SDO_UTIL", línea 6410
ORA-06512: en "MDSYS.SDO_GEOMETRY", línea 195
ORA-06512: en "OW_CAPTURA.ILI2ORA_SDO_GEOMETRY", línea 6

This shows a problem related to spatial data.

Just before the error you see the object that is processed at this time (line 29'251):

Error: Object 9a234e8c-a04b-40c2-acdb-2723b9bc6825 at (line 14587,col 82)

Can you look up this object in the source data (.xtf file) and check whether the spatial part (e. g. coordinate range, number of coordinates) looks correct?

If you are not familiar with analyzing .xtf files and the data is not sensitive you can also post the .xtf file here.

flvgdeveloper commented 2 years ago

Hi, I review the java error and its generated by one funtion created on oracle schema ILI2ORA_SDO_GEOMETRY this funtion receives A blob and return a SDO_GEOMETRY, i modifty the code of this function ti validate if the blob is a valid WKB format to the function but all rrecords are identified that invalid, I tested the xtf importing in postgres database and this data works fine and is procesed. this is the oracle function rewrited:

create or replace FUNCTION ILI2ORA_SDO_GEOMETRY(geom_input BLOB, srid NUMBER) RETURN MDSYS.SDO_GEOMETRY IS geom MDSYS.SDO_GEOMETRY; -- DECLARACION DE VARIABLES sgResultado sdo_geometry; scValidador varchar2(20);

BEGIN

sgResultado := NULL; scValidador := null; IF geom_input IS NOT NULL THEN --- THIS LINES ARE MOUNTED TI VALIDATE THE CONTENT OF BLOB IF IS A VALID WKB FORMAT IN THEE BLOB TO BE PROCESSED scValidador := SDO_UTIL.VALIDATE_WKBGEOMETRY(geom_input);

    if scValidador = 'TRUE' then    
       sgResultado := SDO_UTIL.FROM_WKBGEOMETRY(geom_input);
    end if;
--geom := SDO_GEOMETRY(geom_input, srid);

END IF; RETURN(sgResultado); END;

with this function the file is processed but none column sdo_geometry is loaded..

Thanks for your help is there any way to know what format is sended to the funtion to permit the import. attach the file that iam using to process EPSG 3116

tenjo2.zip

beistehen commented 2 years ago

It looks like the corresponding INTERLIS model Captura_Geo_V0_5 is not publicly available. Can you provide it as well?

And to understand the process: You first did an export with ili2ora and now try to import this data again with ili2ora?

flvgdeveloper commented 2 years ago

Hi Stefan,

Its correct, i try to test that export and import on the same structure, the data generated. I send you the .ili files used in the process

thanks for your help

captura.zip

beistehen commented 1 year ago

According to the data model, you have to deal with 3d data (data type is ISO19107_PLANAS_V3_0.GM_Point3D). The data in the .xtf is only 2d. When checking the data with ilivalidator I get 3 errors for each coordinate tuple in object 9a234e8c-a04b-40c2-acdb-2723b9bc6825:

Error: line 14587: Captura_Geo_V0_5.Captura_Geo.Terreno: tid 9a234e8c-a04b-40c2-acdb-2723b9bc6825: value 993259.819 is out of range in attribute value
Error: line 14587: Captura_Geo_V0_5.Captura_Geo.Terreno: tid 9a234e8c-a04b-40c2-acdb-2723b9bc6825: value 1025129.164 is out of range in attribute value
Error: line 14587: Captura_Geo_V0_5.Captura_Geo.Terreno: tid 9a234e8c-a04b-40c2-acdb-2723b9bc6825: Wrong COORD structure, C3 expected

Error: line 14587: Captura_Geo_V0_5.Captura_Geo.Terreno: tid 9a234e8c-a04b-40c2-acdb-2723b9bc6825: value 993245.482 is out of range in attribute value
Error: line 14587: Captura_Geo_V0_5.Captura_Geo.Terreno: tid 9a234e8c-a04b-40c2-acdb-2723b9bc6825: value 1025137.628 is out of range in attribute value
Error: line 14587: Captura_Geo_V0_5.Captura_Geo.Terreno: tid 9a234e8c-a04b-40c2-acdb-2723b9bc6825: Wrong COORD structure, C3 expected

Error: line 14587: Captura_Geo_V0_5.Captura_Geo.Terreno: tid 9a234e8c-a04b-40c2-acdb-2723b9bc6825: value 993245.425 is out of range in attribute value
Error: line 14587: Captura_Geo_V0_5.Captura_Geo.Terreno: tid 9a234e8c-a04b-40c2-acdb-2723b9bc6825: value 1025137.861 is out of range in attribute value
Error: line 14587: Captura_Geo_V0_5.Captura_Geo.Terreno: tid 9a234e8c-a04b-40c2-acdb-2723b9bc6825: Wrong COORD structure, C3 expected
...

The 'Wrong COORD structure, C3 expected' error indicates the missing coordinate. ~~I therefore expect the 'out of range' errors to be subsequent errors that can be ignored as the values are effectively in range of the CRS:~~

GM_Point3D = 
COORD 3980000.000 .. 5700000.000 [INTERLIS.m],
1080000.000 .. 3100000.000 [INTERLIS.m],
-5000.000 .. 6000.000 [INTERLIS.m] ,ROTATION 2 -> 1;

Manually enhancing the coordinates with a third value <C3>0.0</C3> to make it real 3d, the 'out of range' errors did not disappear, which seem strange to me. There might be a problem processing the nested structures in the ISO19107_PLANAS_V3_0 data model.

Reaching out to @edigonzales @claeis : setting aside the initial ili2ora problem, why is the following data not valid (check with ilivalidator and igCheck fails)? I reduced the data to 2 objects to test with, see attached .zip file. #495_condensed.zip

claeis commented 1 year ago

TID 9a234e8c-a04b-40c2-acdb-2723b9bc6825: C1 993259.819 is out of range (3980000.000 .. 5700000.000)!?

flvgdeveloper commented 1 year ago

Hi Stefan ,Claude

3 situations identified :

I correct the data on oracle database with sdo and add C3 coordinate for all records i put 0 value for all coordinates of objects ... export the data with ili2ora.jar , but when validate the xtf dont generate the C3 value.
then import the data in gpkp ... export the data with C3 filled, verify that export correctly and then try to import to oracle with ili2ora but get the same error initially reported.
About the comment publish by claude validate the data and not find the coordinate out of range

Attach the xtf with Z coordinate and log of error the import to oracle.

Thanks, 20221101_110517.zip tenjoPostgres.zip

beistehen commented 1 year ago

TID 9a234e8c-a04b-40c2-acdb-2723b9bc6825: C1 993259.819 is out of range (3980000.000 .. 5700000.000)!?

indeed it is out of range! What a shame 🙈 I should not work that early in the morning 🧐

beistehen commented 1 year ago

Not sure how to proceed from here. I don't have an Oracle Database installation available to test. I suggest the following test for you to do:

use the newest version of ili2ora from here
try to import the attached .xtf file (which is a modified version from my first attachment #495_condensed.zip). This .xtf file is error free and contains only 1 object with geometry. Importing into gpkg and displaying the object in QGIS works as expected. I adjusted the coordinates manually so that they are in the required range (see previous comment).
write down and post here ...
- ... the complete command line with ili2ora to create the database schema using --schemaImport
- ... the complete command line with ili2ora to import the data using --import
- ... each corresponding log file

If the test data fails to import and your initial Oracle error is raised in the log file, there has to be a bug in ili2ora which has to be further investigated. Having an error free dataset to start with is helpful for the developers.

If the test data is imported successfuly then you have to answer a few questions for yourself:

why are you using a data model (ISO19107_PLANAS_V3_0) that is based on EPSG 3116 but your data is located outside the range of this spatial reference system (SRS)?
is there a more suitable INTERLIS model available where the correct SRS is already specified?
is there a more suitable data type for your data? You don't process any 3d data, why not choose a 2d data type?

Regards, Stefan tenjo2_mod_3d_inrange.zip

claeis / ili2db

ili2ora error on import option #495