Home » RDBMS Server » Server Utilities » Invalid geometries after import sqlldr
Invalid geometries after import sqlldr [message #456313] Mon, 17 May 2010 03:43 Go to next message
bibber
Messages: 38
Registered: August 2006
Member
Hi,

I'm using sqlldr to import geometries into a table. The import runs succesfully, but when I validate to imported geometries using "SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT", it gives an error on empty geometries (or supossedly empty).

When I select to columns using sqlplus, the geometry column isn't entirely empty. After I set the geometry = null, the geometry is really empty. (see screenshot)

Question: How do I import empty geometries values properly, so that it's really empty?
My guess is that I have to alter to ctrl-file, like NULLIF


data:
3806501|
3806504|2001|90112||121231|485668||;:


ctrl-file:
LOAD DATA
INFILE 'aap.dat'
APPEND INTO TABLE aap
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '$$'
TRAILING NULLCOLS
(
 ID DECIMAL EXTERNAL
,GEOMETRIE COLUMN OBJECT
  ( sdo_gtype INTEGER EXTERNAL,
    sdo_srid INTEGER EXTERNAL,
     isnull FILLER CHAR,
    SDO_POINT COLUMN OBJECT  NULLIF GEOMETRIE.isnull="pt"
    ( X INTEGER EXTERNAL,
      Y INTEGER EXTERNAL,
      Z INTEGER EXTERNAL),
    SDO_ELEM_INFO VARRAY terminated by ';'
      (SDO_ORDINATES char(38)),
    SDO_ORDINATES VARRAY terminated by ':'
      (SDO_ORDINATES char(38)) 
  )
)
  • Attachment: sqlplus.JPG
    (Size: 93.24KB, Downloaded 952 times)
Re: Invalid geometries after import sqlldr [message #456424 is a reply to message #456313] Mon, 17 May 2010 16:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The nullif needs to be used at the column object level, but it needs to reference something before that. Is there any flexibility in how you get your data? For example, you could use a column after the id with a value of E for empty, as shown below.


data:
3806501|E|
3806504||2001|90112||121231|485668||;:


ctrl-file:
LOAD DATA
INFILE 'aap.dat'
APPEND INTO TABLE aap
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '$$'
TRAILING NULLCOLS
(
ID DECIMAL EXTERNAL
,COL2 FILLER
,GEOMETRIE COLUMN OBJECT NULLIF COL2 = 'E'
( sdo_gtype INTEGER EXTERNAL,
sdo_srid INTEGER EXTERNAL,
isnull FILLER CHAR,
SDO_POINT COLUMN OBJECT NULLIF GEOMETRIE.isnull="pt"
( X INTEGER EXTERNAL,
Y INTEGER EXTERNAL,
Z INTEGER EXTERNAL),
SDO_ELEM_INFO VARRAY terminated by ';'
(SDO_ORDINATES char(38)),
SDO_ORDINATES VARRAY terminated by ':'
(SDO_ORDINATES char(38))
)
)
Re: Invalid geometries after import sqlldr [message #460753 is a reply to message #456313] Mon, 14 June 2010 06:27 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
Hi,

I have solved the problem by using "J" if geometry is present, else "N". But I encounter another problem with the sdo_elem_info and sdo_ordinates. If the geometry is a point, then the sdo_elem_info_array and sdo_ordinate_array should null. Else it is still invalid geometry. The isleeg2 filler is empty if the geometry is a point else "pt".
So I thought "NULLIF GEOMETRIE.isleeg2=blanks", but I still get invalid geometry, according to SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT.
How can alter my ctl-file, in order to get "null" for sdo_elem_info and sdo_ordinates?


data:
3806360|3630039852233|J|2001|90112||120890|485964||;:
3806361|3630039852234|J|2001|90112||120890|485964||;:
3806362|3630039852235|J|2001|90112||120890|485964||;:
3806363|3630039852236|J|2001|90112||122052|488398||;:
3806364|3630039852237|J|2001|90112||122060|488406||;:
3806365|3630039852238|J|2001|90112||122081|488443||;:
3806366|3630039852239|J|2001|90112||122085|488446||;:
3806367|3630039852240|J|2001|90112||122079|488495||;:
3806368|3630039852241|J|2001|90112||122347|488581||;:
3806369|3630039852242|J|2001|90112||122275|488538||;:

ctl:
LOAD DATA
INFILE 'test_exp2sqlldr.dat'
APPEND INTO TABLE test_exp2sqlldr
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '$$'
TRAILING NULLCOLS
(
 ID DECIMAL EXTERNAL
,VOT_ID DECIMAL EXTERNAL
, isleeg1 FILLER
,GEOMETRIE COLUMN OBJECT NULLIF isleeg1="N"
  ( sdo_gtype INTEGER EXTERNAL,
    sdo_srid INTEGER EXTERNAL,
     isleeg2 FILLER,
    SDO_POINT COLUMN OBJECT NULLIF GEOMETRIE.isleeg2="pt"
    ( X INTEGER EXTERNAL,
      Y INTEGER EXTERNAL,
      Z INTEGER EXTERNAL),
    SDO_ELEM_INFO VARRAY terminated by ';' NULLIF GEOMETRIE.isleeg2=blanks
      (SDO_ORDINATES char(38)),
    SDO_ORDINATES VARRAY terminated by ':' NULLIF GEOMETRIE.isleeg2=blanks
      (SDO_ORDINATES char(38)) 
  )
)
Re: Invalid geometries after import sqlldr [message #461165 is a reply to message #460753] Wed, 16 June 2010 19:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It does not seem to like checking for blanks. If you can find some way to give isleeg2 a value, like e for empty, instead of blanks, then you can use isleeg2='e'. Otherwise, you can do an update after loading.
Re: Invalid geometries after import sqlldr [message #461206 is a reply to message #456313] Thu, 17 June 2010 01:33 Go to previous message
bibber
Messages: 38
Registered: August 2006
Member
hurray!

Weird that it doesn't check for blanks or "".

thanks, Rob Smile
Previous Topic: ORA-01855: AM/A.M. or PM/P.M.
Next Topic: Tuning sql loader process
Goto Forum:
  


Current Time: Fri Mar 29 07:24:03 CDT 2024