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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 1109 times)
|
|
|
Re: Invalid geometries after import sqlldr [message #456424 is a reply to message #456313] |
Mon, 17 May 2010 16:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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))
)
)
|
|
|
|
|
Goto Forum:
Current Time: Mon Sep 09 22:19:01 CDT 2024
|