Home » RDBMS Server » Server Utilities » Load WKT using sql*loader (oracle 10.2.0.1)
Load WKT using sql*loader [message #446795] Wed, 10 March 2010 08:31 Go to next message
bibber
Messages: 38
Registered: August 2006
Member
Hi,

I want to load geometry into a table using sql*loader. My datafile contains geometry defined as WKT http://en.wikipedia.org/wiki/Well-known_text, a standard for geometry and also Oracle has a function called sdo_util.from_wktgeometry.
If I'm using a separate 'insert into' statement using this function in sql*plus, there's no problem. But if I'm using the same function in my control-file for sql*loader import, I get a sql*loader-418 error: "bad datafile for column geometrie".

Does anyone know why and how I can import WKT using sql*loader?

-- data file
id;geometrie
1;POINT(120123.123 485345.789)
2;LINESTRING(123456.01 482543.21, 125764.76 483444.11)
3;POLYGON((121121.22 484394.22, 122887.444 484721.48, 122911.098 486382.45, 121005.21 486592.01, 121121.22 484394.22),(121922.56 485333.23, 122010.22 485854.83, 121922.56 485333.23))
4;MULTIPOINT((120586 483958.33),(120635 483726.11))
5;MULTILINESTRING((117948 480284, 118215 481236),(118475 481604, 120462 482822))
6;MULTIPOLYGON(((123678 481948, 124654 485215, 123678 481948),(127321 488321, 124907 483921, 127321 488321)))

-- table definition
CREATE TABLE test_wkt1
( id number(1,0)
, geometrie mdsys.sdo_geometry );


-- control file
options(skip=1, errors=100)

load data
truncate
into table test_wkt1

fields terminated by ";"
trailing nullcols

( id		integer external
, geometrie	"sdo_util.from_wktgeometry(:geometrie)" )
Re: Load WKT using sql*loader [message #446876 is a reply to message #446795] Wed, 10 March 2010 20:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I don't think SQL*Loader supports the mdsys.sdo_geometry datatype. I believe you will need to load the data into a staging table as varchar2 or clob, then insert from the staging table to the target table using sdo_util.from_wktgeometry.
Re: Load WKT using sql*loader [message #446958 is a reply to message #446795] Thu, 11 March 2010 01:30 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
There are examples, where the split up the components of a geometry.

LOAD DATA 
 INFILE *
 TRUNCATE
 CONTINUEIF NEXT(1:1) = '#'
 INTO TABLE POLY_4PT
 FIELDS TERMINATED BY '|'
 TRAILING NULLCOLS (
  GID  INTEGER EXTERNAL,
  GEOM COLUMN OBJECT 
   (
     SDO_GTYPE       INTEGER EXTERNAL, 
     SDO_ELEM_INFO   VARRAY TERMINATED BY '|/' 
       (elements     FLOAT EXTERNAL), 
     SDO_ORDINATES   VARRAY TERMINATED BY '|/' 
       (ordinates    FLOAT EXTERNAL) 
   )
)
begindata
 1|2003|1|1003|1|/
#-122.4215|37.7862|-122.422|37.7869|-122.421|37.789|-122.42|37.7866|
#-122.4215|37.7862|/
 2|2003|1|1003|1|/
#-122.4019|37.8052|-122.4027|37.8055|-122.4031|37.806|-122.4012|37.8052|
#-122.4019|37.8052|/
 3|2003|1|1003|1|/
#-122.426|37.803|-122.4242|37.8053|-122.42355|37.8044|-122.4235|37.8025|
#-122.426|37.803|/
Re: Load WKT using sql*loader [message #446973 is a reply to message #446958] Thu, 11 March 2010 03:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You're right. MDSYS.SDO_GEOMETRY is an object, so it is supported. I fiddled with it a bit and found that the combination of BOUNDFILLER and the EXPRESSION keyword solves the problem, as demonstrated below.

-- test.ctl:
options(skip=1, errors=100)
load data
truncate
into table test_wkt1
fields terminated by ";"
trailing nullcols
( id		integer external
, geomdata       BOUNDFILLER
, geometrie	EXPRESSION "sdo_util.from_wktgeometry(:geomdata)" )
BEGINDATA
id;geometrie
1;POINT(120123.123 485345.789)
2;LINESTRING(123456.01 482543.21, 125764.76 483444.11)
3;POLYGON((121121.22 484394.22, 122887.444 484721.48, 122911.098 486382.45, 121005.21 486592.01, 121121.22 484394.22),(121922.56 485333.23, 122010.22 485854.83, 121922.56 485333.23))
4;MULTIPOINT((120586 483958.33),(120635 483726.11))
5;MULTILINESTRING((117948 480284, 118215 481236),(118475 481604, 120462 482822))
6;MULTIPOLYGON(((123678 481948, 124654 485215, 123678 481948),(127321 488321, 124907 483921, 127321 488321)))


SCOTT@orcl_11g> CREATE TABLE test_wkt1
  2  ( id number(1,0)
  3  , geometrie mdsys.sdo_geometry )
  4  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> SELECT * FROM test_wkt1
  2  /

        ID
----------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
         1
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(120123.123, 485345.789, NULL), NULL, NUL
L)

         2
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
123456.01, 482543.21, 125764.76, 483444.11))

         3
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 11, 2003, 1), SDO
_ORDINATE_ARRAY(121121.22, 484394.22, 122887.444, 484721.48, 122911.098, 486382.
45, 121005.21, 486592.01, 121121.22, 484394.22, 121922.56, 485333.23, 122010.22,
 485854.83, 121922.56, 485333.23))

         4
SDO_GEOMETRY(2005, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 2), SDO_ORDINATE_ARRAY(
120586, 483958.33, 120635, 483726.11))

         5
SDO_GEOMETRY(2006, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 5, 2, 1), SDO_ORDINA
TE_ARRAY(117948, 480284, 118215, 481236, 118475, 481604, 120462, 482822))

         6
SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 7, 2003, 1), SDO_
ORDINATE_ARRAY(123678, 481948, 124654, 485215, 123678, 481948, 127321, 488321, 1
24907, 483921, 127321, 488321))


6 rows selected.

SCOTT@orcl_11g> 


Re: Load WKT using sql*loader [message #446975 is a reply to message #446795] Thu, 11 March 2010 03:39 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
indeed, many thanks!
Re: Load WKT using sql*loader [message #447165 is a reply to message #446795] Fri, 12 March 2010 02:10 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
Another difficulty came to my attention:
sdo_util.from_wktgeometry is available since oracle 10g. Since we are dealing with both 10g and 9i, I was wndering if there is a solution to import WKT into a 9i database?
Re: Load WKT using sql*loader [message #447277 is a reply to message #447165] Fri, 12 March 2010 17:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
One method that I can think of is to write your own substitute function. I have provided some partial code below. It does not address all posible data and may contain errors. It is just intended to give you the general idea and provide a starting point that you can modify to suit your needs.

-- test.ctl:
options(skip=1, errors=100)
load data
infile *
truncate
into table test_wkt1
fields terminated by ";"
trailing nullcols
( id		integer external
, geomdata       BOUNDFILLER
, geometrie	EXPRESSION "wkt_to_geom (:geomdata)" )
BEGINDATA
id;geometrie
1;POINT(120123.123 485345.789)
2;LINESTRING(123456.01 482543.21, 125764.76 483444.11)
3;POLYGON((121121.22 484394.22, 122887.444 484721.48, 122911.098 486382.45, 121005.21 486592.01, 121121.22 484394.22),(121922.56 485333.23, 122010.22 485854.83, 121922.56 485333.23))
4;MULTIPOINT((120586 483958.33),(120635 483726.11))
5;MULTILINESTRING((117948 480284, 118215 481236),(118475 481604, 120462 482822))
6;MULTIPOLYGON(((123678 481948, 124654 485215, 123678 481948),(127321 488321, 124907 483921, 127321 488321)))


SCOTT@orcl_11g> CREATE TABLE test_wkt1
  2  ( id number(1,0)
  3  , geometrie mdsys.sdo_geometry )
  4  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION wkt_to_geom
  2    (p_geomdata   IN VARCHAR2)
  3    RETURN		MDSYS.SDO_GEOMETRY
  4  AS
  5    v_type		VARCHAR2 (100);
  6    v_nums		VARCHAR2 (2000);
  7    v_SDO_GEOMETRY	MDSYS.SDO_GEOMETRY;
  8  BEGIN
  9    -- separate type and numbers:
 10    v_type := SUBSTR (p_geomdata, 1, INSTR (p_geomdata, '(') - 1);
 11    v_nums := SUBSTR (p_geomdata, INSTR (p_geomdata, '('));
 12    -- remove right parentheses:
 13    v_nums := REPLACE (v_nums, ')', '');
 14    -- replace spaces with commas:
 15    v_nums := REPLACE (v_nums, ' ', ',');
 16    -- replace double commas with single comma:
 17    v_nums := REPLACE (v_nums, ',,', ',');
 18    -- add ending comma:
 19    v_nums := v_nums || ',';
 20    -- initialize:
 21    v_SDO_GEOMETRY := MDSYS.SDO_GEOMETRY (NULL, NULL, NULL, NULL, NULL);
 22    -- assign values
 23    -- (this is only partial code as an example
 24    --  and does not address all potential data):
 25    IF v_type = 'POINT' THEN
 26  	 v_SDO_GEOMETRY.SDO_GTYPE := 2001;
 27    ELSIF v_type = 'LINESTRING' THEN
 28  	 v_SDO_GEOMETRY.SDO_GTYPE := 2002;
 29    ELSIF v_type = 'POLYGON' THEN
 30  	 v_SDO_GEOMETRY.SDO_GTYPE := 2003;
 31    ELSIF v_type = 'MULTIPOINT' THEN
 32  	 v_SDO_GEOMETRY.SDO_GTYPE := 2005;
 33    ELSIF v_type = 'MULTILINESTRING' THEN
 34  	 v_SDO_GEOMETRY.SDO_GTYPE := 2006;
 35    ELSIF v_type = 'MULTIPOLYGON' THEN
 36  	 v_SDO_GEOMETRY.SDO_GTYPE := 2007;
 37    END IF;
 38    IF v_type = 'POINT' THEN
 39  	 v_SDO_GEOMETRY.SDO_POINT := SDO_POINT_TYPE (NULL, NULL, NULL);
 40  	 v_nums := LTRIM (v_nums, '(');
 41  	 v_SDO_GEOMETRY.SDO_POINT.X := SUBSTR (v_nums, 1, INSTR (v_nums, ',') - 1);
 42  	 v_SDO_GEOMETRY.SDO_POINT.Y := RTRIM (SUBSTR (v_nums, INSTR (v_nums, ',') + 1), ',');
 43    ELSE
 44  	 v_SDO_GEOMETRY.SDO_ELEM_INFO := SDO_ELEM_INFO_ARRAY ();
 45  	 v_SDO_GEOMETRY.SDO_ORDINATES := SDO_ORDINATE_ARRAY ();
 46  	 WHILE LENGTH (v_nums) > 1 LOOP
 47  	   v_SDO_GEOMETRY.SDO_ORDINATES.EXTEND;
 48  	   IF SUBSTR (v_nums, 1, 1) = '(' THEN
 49  	     v_SDO_GEOMETRY.SDO_ELEM_INFO.EXTEND;
 50  	     v_SDO_GEOMETRY.SDO_ELEM_INFO(v_SDO_GEOMETRY.SDO_ELEM_INFO.LAST) :=
 51  	       v_SDO_GEOMETRY.SDO_ORDINATES.LAST;
 52  	     v_SDO_GEOMETRY.SDO_ELEM_INFO.EXTEND;
 53  	     IF INSTR (v_type, 'STRING') > 0 THEN
 54  	       v_SDO_GEOMETRY.SDO_ELEM_INFO(v_SDO_GEOMETRY.SDO_ELEM_INFO.LAST) := 2;
 55  	     ELSIF INSTR (v_type, 'POLYGON') > 0 THEN
 56  	       IF v_SDO_GEOMETRY.SDO_ELEM_INFO.LAST > 2 THEN
 57  		 v_SDO_GEOMETRY.SDO_ELEM_INFO(v_SDO_GEOMETRY.SDO_ELEM_INFO.LAST) := 2003;
 58  	       ELSE
 59  		 v_SDO_GEOMETRY.SDO_ELEM_INFO(v_SDO_GEOMETRY.SDO_ELEM_INFO.LAST) := 1003;
 60  	       END IF;
 61  	     ELSIF INSTR (v_type, 'POINT') > 0 THEN
 62  	       v_SDO_GEOMETRY.SDO_ELEM_INFO(v_SDO_GEOMETRY.SDO_ELEM_INFO.LAST) := 1;
 63  	     END IF;
 64  	     v_SDO_GEOMETRY.SDO_ELEM_INFO.EXTEND;
 65  	     v_SDO_GEOMETRY.SDO_ELEM_INFO(v_SDO_GEOMETRY.SDO_ELEM_INFO.LAST) := 1;
 66  	   END IF;
 67  	   v_nums := LTRIM (v_nums, '(');
 68  	   v_SDO_GEOMETRY.SDO_ORDINATES(v_SDO_GEOMETRY.SDO_ORDINATES.LAST) := SUBSTR (v_nums, 1, INSTR (v_nums, ',') - 1);
 69  	   v_nums := SUBSTR (v_nums, INSTR (v_nums, ',') + 1);
 70  	 END LOOP;
 71    END IF;
 72    -- return:
 73    RETURN v_SDO_GEOMETRY;
 74  END wkt_to_geom;
 75  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> SELECT * FROM test_wkt1
  2  /

        ID
----------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
         1
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(120123.123, 485345.789, NULL), NULL, NUL
L)

         2
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
123456.01, 482543.21, 125764.76, 483444.11))

         3
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 11, 2003, 1), SDO
_ORDINATE_ARRAY(121121.22, 484394.22, 122887.444, 484721.48, 122911.098, 486382.
45, 121005.21, 486592.01, 121121.22, 484394.22, 121922.56, 485333.23, 122010.22,
 485854.83, 121922.56, 485333.23))

         4
SDO_GEOMETRY(2005, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1, 3, 1, 1), SDO_ORDINA
TE_ARRAY(120586, 483958.33, 120635, 483726.11))

         5
SDO_GEOMETRY(2006, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 5, 2, 1), SDO_ORDINA
TE_ARRAY(117948, 480284, 118215, 481236, 118475, 481604, 120462, 482822))

         6
SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 7, 2003, 1), SDO_
ORDINATE_ARRAY(123678, 481948, 124654, 485215, 123678, 481948, 127321, 488321, 1
24907, 483921, 127321, 488321))


6 rows selected.

SCOTT@orcl_11g> 



Re: Load WKT using sql*loader [message #447302 is a reply to message #446795] Sat, 13 March 2010 06:59 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
awesome!
I will try your code asap.

many thanks in advance
Re: Load WKT using sql*loader [message #456071 is a reply to message #446795] Fri, 14 May 2010 07:33 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
Hi,

I'm having difficulties importing large polygons. De controlfile is functioning properly, but the polygondata in the datafile seems to contain too many coordinates. My errorlog:

Record 1: refused error in table GBD_STADSDEEL_GEOMETRIE, column GEOMETRIE_DATA.
field in datafile exceeds maximum length.

Is there a limitation of the maximum of my wkt datastring (or boundfiller)? And how to solve this problem? My contains polygondata of at least 9332 characters.

LOAD DATA
INFILE 'gbd_stadsdeel_geometrie.dat'
APPEND INTO TABLE gbd_stadsdeel_geometrie
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '$$'
TRAILING NULLCOLS
(
 ID DECIMAL EXTERNAL
,SDC_ID DECIMAL EXTERNAL
,SCHAAL DECIMAL EXTERNAL
,GEOMETRIE_DATA BOUNDFILLER
,GEOMETRIE EXPRESSION "sdo_util.from_wktgeometry(:GEOMETRIE_DATA)"
)

begindata

44|1004|1000|POLYGON ((118390.812671607 486299.739836496, 118371.617805143 486299.859289803, 118373.341566781 486030.931038016, 118330.597778023 486010.008636629, 118330.289898483 486009.857934517, 118328.030356684 485707.979759539, 118328.347479571 485693.445979423, 118406.703 485693.772, 118423.416913778 485693.11083983, 118443.031 485692.409, 118552.084507609 485692.852121343, 118559.604608381 485692.148154285, 118565.977 485691.302, 118567.412674555 485691.135764628, 118569.127923318 485690.996096001, 118571.153459908 485690.917629857, 118572.93585317 485690.923399288, 118574.917 485691.011, 118579.571265746 485691.325632969, 118584.360057742 485691.702265847, 118588.964822735 485692.33988069, 118593.359038944 485693.222330519, 118597.572 485694.311, 118608.781 485699.701, 118635.924655791 485708.942008742, 118645.860937794 485711.503884686, 118648.118247909 485711.453859222, 118650.805650661 485710.954941707, 118669.404366919 485703.151822746, 118729.449484375 485672.871372222, 118735.227944596 485684.606282805, 118830.728233922 485874.684038525, 118834.885478368 485882.958355805, 118840.403559811 485893.941197259, 118844.275080723 485901.646828428, 118848.135944838 485908.501329364, 118853.95173242 485918.859162475, 118862.356517392 485934.9665563, 118871.195399086 485953.837891417, 118875.992519967 485965.119071388, 118880.755336142 485977.271230354, 118885.079058836 485989.202768579, 118888.805726059 486000.485025854, 118892.692926613 486013.363817735, 118895.811479189 486024.943346021, 118899.119950729 486038.588607219, 118903.065966857 486054.983273938, 118905.911163311 486065.616499192, 118908.973416405 486076.033754085, 118911.951323666 486085.532370407, 118914.727527084 486093.713199666, 118916.012721028 486097.342692212, 118917.32732314 486100.961636934, 118918.671246863 486104.569795555, 118920.044403713 486108.166930509, 118921.446703277 486111.752804956, 118922.878053228 486115.327182794, 118924.338359321 486118.889828683, 118925.827525409 486122.440508051, 118927.345453443 486125.978987116, 118928.892043479 486129.505032901, 118930.467193689 486133.018413243, 118932.07080036 486136.518896817, 118933.702757911 486140.006253146, 118935.36295889 486143.480252617, 118937.051293986 486146.940666496, 118938.767652038 486150.387266945, 118940.511920038 486153.819827033, 118942.283983139 486157.238120756, 118944.083724668 486160.641923048, 118945.911026126 486164.031009797, 119037.888392013 486328.782514288, 119095.168485814 486431.383643818, 119095.307414661 486431.651866275, 119180.347907746 486595.834975434, 119246.355397018 486711.346446931, 119317.075375685 486835.104658161, 119437.334837498 487045.555738011, 119529.09 487206.125, 119589.702301635 487311.780098422, 119445.363019954 487512.444754004, 119442.33358981 487516.30839467, 119438.747273009 487520.882271198, 119435.064992873 487525.945036109, 119432.095143804 487530.351379821, 119429.285513547 487534.878662243, 119425.984006204 487540.832875319, 119423.067837604 487546.848736508, 119420.400163731 487553.09390237, 119418.032624409 487559.585516058, 119416.287815251 487565.742613764, 119414.523126174 487571.98653705, 119413.714102567 487575.761422144, 119412.257845945 487582.556281245, 119410.772779837 487589.48556473, 119408.454814774 487600.301135122, 119401.509852254 487632.706166676, 119294.284531871 487609.616551494, 119274.621842544 487605.447718556, 119240.005349968 487598.141559037, 119201.793830374 487591.662425916, 119129.954190593 487579.13876522, 119091.835 487568.846, 119081.716 487565.7915, 119063.027451086 487561.803242742, 119010.337171202 487552.296649168, 119001.159772433 487551.340678769, 118993.209991518 487550.512584074, 118941.381686102 487545.509360634, 118797.854652784 487519.915900316, 118796.575099736 487519.687732915, 118769.38350348 487513.972363418, 118767.044665078 487513.46995646, 118764.77986991 487512.952572525, 118762.948081628 487512.458839174, 118761.26069023 487512.004026019, 118759.506867299 487511.460810477, 118757.75873724 487510.919358201, 118756.302986111 487510.42371813, 118754.629544358 487509.853960861, 118753.125903814 487509.274505987, 118751.251641349 487508.552225303, 118749.349002859 487507.757681314, 118749.209904855 487507.69959383, 118749.081326435 487507.645899336, 118747.722216577 487507.068194812, 118746.438894171 487506.472681633, 118744.924445163 487505.769916421, 118743.630812973 487505.119683269, 118743.3218381 487504.964379693, 118741.974094732 487504.28694802, 118740.485258408 487503.487011428, 118739.158889184 487502.76487493, 118737.958468101 487502.061978542, 118736.303957392 487501.080398406, 118734.296733125 487499.838929966, 118730.842971794 487497.545026516, 118727.688999783 487495.337525905, 118724.413786025 487493.053928513, 118645.764829359 487441.009680646, 118643.664080204 487439.619555279, 118642.482517321 487438.82998733, 118640.849036798 487437.724489964, 118639.169191457 487436.612951986, 118638.655284174 487436.274524663, 118637.564588659 487435.582241536, 118636.006553627 487434.609139083, 118634.444321476 487433.671479721, 118632.884961674 487432.762314041, 118631.051504128 487431.733572477, 118628.852217031 487430.550551547, 118626.972403965 487429.584665364, 118625.291159005 487428.749397348, 118623.345248873 487427.833023338, 118622.00661882 487427.210925759, 118621.060884886 487426.795375639, 118619.482566396 487426.107364012, 118617.708809111 487425.366640733, 118615.875246219 487424.637163141, 118614.01432349 487423.951884191, 118612.438100672 487423.455959441, 118610.032566643 487422.699109682, 118607.027377041 487421.884280251, 118581.194496809 487414.879933143, 118575.8525 487413.4315, 118492.906000655 487390.088487011, 118457.809457644 487378.95282238, 118407.89096102 487362.671712711, 118369.415319776 487351.228857331, 118339.812665489 487343.43172964, 118317.807274035 487337.635666712, 118264.970465301 487323.504892283, 118241.393575357 487316.823493504, 118219.968000036 487310.841999972, 118204.562903497 487308.720994479, 118196.752920015 487307.645699831, 118192.553660706 487307.128777508, 118185.169621908 487305.87680592, 118174.1161 487303.5618, 118168.3735 487302.0846, 118166.7571 487301.6358, 118165.1647 487301.1378, 118164.03966785 487300.746475359, 118162.8097 487300.295, 118161.2501 487299.6644, 118155.4239 487297.1948, 118149.14968391 487295.00350435, 118137.7735 487292.0752, 118134.7641 487291.5516, 118131.6577 487291.0242, 118128.6559 487290.5272, 118126.7089 487290.2182, 118124.7007 487289.9126, 118122.6373 487289.6114, 118120.6081 487289.3282, 118118.5411 487289.0528, 118116.5037 487288.7944, 118114.5311 487288.5566, 118112.8965 487288.3042, 118110.1559 487287.989, 118107.4831 487287.6972, 118104.7857 487287.4186, 118102.1143 487287.1584, 118099.4503 487286.9144, 118096.7391 487286.6822, 118094.0719 487286.4692, 118091.3933 487286.271, 118088.6869 487286.0866, 118086.0147 487285.9198, 118083.2959 487285.7664, 118080.6257 487285.631, 118077.9427 487285.5108, 118075.2299 487285.405, 118072.5527 487285.316, 118069.8383 487285.2418, 118067.2661 487285.1864, 118065.8585 487285.0804, 118033.79784581 487284.314990447, 117911.819250897 487288.676972135, 117880.174010815 487290.863551629, 117878.622845639 487254.234045621, 117877.526618042 487209.535645621, 117877.632513753 487140.809329215, 117879.119148167 486995.130506801, 118088.077483429 486997.841249949, 118095.325909237 486998.079368076, 118099.516517651 486998.248449119, 118104.687088264 486998.50211543, 118108.209745461 486998.701242391, 118112.588597899 486998.977326101, 118116.467278427 486999.24886125, 118122.061179576 486999.684127009, 118126.22242183 487000.059098823, 118131.127578585 487000.501105179, 118139.787337058 487001.41026303, 118147.2550205 487002.295781954, 118153.926836532 487003.166798043, 118162.154443077 487004.345223338, 118169.193217782 487005.445282005, 118175.828609992 487006.560346314, 118183.229420854 487007.893927336, 118189.448414467 487009.088297399, 118195.6545516 487010.34776685, 118201.030600597 487011.49755925, 118202.776563283 487011.892105785, 118209.515268312 487013.414894754, 118210.723929811 487013.689581625, 118215.694021974 487014.957254224, 118218.948284179 487015.769593152, 118221.588444589 487016.428638104, 118225.442866488 487017.377822822, 118228.451052521 487018.111808538, 118235.547408781 487019.81966684, 118243.143516247 487021.616772267, 118246.173232087 487022.333549749, 118252.23870745 487023.768534542, 118259.930306758 486995.530852313, 118301.771866455 487006.826568979, 118324.91 486922.4, 118322.865 486919.463, 118331.425 486888.237, 118336.225 486872.547, 118340.442 486855.218, 118343.124 486845.604, 118371.018 486743.084, 118373.486 486734.307, 118374.966 486729.044, 118379.41 486713.24, 118385.492 486690.871, 118387.11 486684.916, 118390.975758382 486671.219545704, 118431.976 486521.799, 118439.883 486509.312, 118440.335273369 486507.2088152, 118440.738748766 486505.095726203, 118441.093210035 486502.97386506, 118441.57 486498.57, 118442.756 486413.142, 118443.219270987 486408.34205145, 118444.359803251 486396.525001951, 118409.956755369 486396.121761132, 118410.17 486376.4, 118418.66 486366.64, 118418.76 486357.28, 118411.25 486357.28, 118411.757 486353.838, 118408.85 486353.81, 118398.328 486353.662, 118389.842420048 486353.542078365, 118390.812671607 486299.739836496))
Re: Load WKT using sql*loader [message #456425 is a reply to message #456071] Mon, 17 May 2010 17:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Does this post:

http://www.orafaq.com/forum/t/157802/43710/

mean that you have found a workaround by loading into a column object or is this still a problem?
Re: Load WKT using sql*loader [message #456458 is a reply to message #446795] Tue, 18 May 2010 01:21 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
Hi Barbara,

we want to be able to serve both WKT and via geometry object. The second one is the 'old' method, which we still have to support. WKT is a new method and more ideal, since it is not only limited to oracle.

greetz Rob
Re: Load WKT using sql*loader [message #456471 is a reply to message #456458] Tue, 18 May 2010 02:14 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Can't we Treat as Varchar datatype ?

[Updated on: Tue, 18 May 2010 02:15]

Report message to a moderator

Re: Load WKT using sql*loader [message #456612 is a reply to message #456458] Tue, 18 May 2010 14:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you don't provide a field length, sql*loader uses a default length of 255 characters, so if you use char(9400) that solves the length problem. However, 4000 is the max size for varchar2, so it expects a long or clob and there is some sort of problem with trying to use that with boundfiller. As a workaround, you can create a clob column in your table and use that in your control file and use that as the input parameter instead of the boundfiller. You can then drop the clob column aftr loading if you like. Please see the demo below.

-- test.ctl:
LOAD DATA
INFILE 'gbd_stadsdeel_geometrie.dat'
APPEND INTO TABLE gbd_stadsdeel_geometrie
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '$$'
TRAILING NULLCOLS
(
ID DECIMAL EXTERNAL
,SDC_ID DECIMAL EXTERNAL
,SCHAAL DECIMAL EXTERNAL
,GEOMETRIE_DATA CHAR(9400)
,GEOMETRIE EXPRESSION "sdo_util.from_wktgeometry (:GEOMETRIE_DATA)"
)


-- table, load, and results:
SCOTT@orcl_11g> CREATE TABLE gbd_stadsdeel_geometrie
  2    (id		NUMBER,
  3  	sdc_id		NUMBER,
  4  	schaal		NUMBER,
  5  	geometrie_data	CLOB,
  6  	geometrie	mdsys.sdo_geometry)
  7  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> ALTER TABLE gbd_stadsdeel_geometrie
  2  DROP COLUMN geometrie_data
  3  /

Table altered.

SCOTT@orcl_11g> SELECT * FROM gbd_stadsdeel_geometrie
  2  /

        ID     SDC_ID     SCHAAL
---------- ---------- ----------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
        44       1004       1000
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(118390.813, 486299.74, 118371.618, 486299.859, 118373.342, 486030.931, 118330
.598, 486010.009, 118330.29, 486009.858, 118328.03, 485707.98, 118328.347, 48569
3.446, 118406.703, 485693.772, 118423.417, 485693.111, 118443.031, 485692.409, 1
18552.085, 485692.852, 118559.605, 485692.148, 118565.977, 485691.302, 118567.41
3, 485691.136, 118569.128, 485690.996, 118571.153, 485690.918, 118572.936, 48569
0.923, 118574.917, 485691.011, 118579.571, 485691.326, 118584.36, 485691.702, 11
8588.965, 485692.34, 118593.359, 485693.222, 118597.572, 485694.311, 118608.781,
 485699.701, 118635.925, 485708.942, 118645.861, 485711.504, 118648.118, 485711.
454, 118650.806, 485710.955, 118669.404, 485703.152, 118729.449, 485672.871, 118
735.228, 485684.606, 118830.728, 485874.684, 118834.885, 485882.958, 118840.404,
 485893.941, 118844.275, 485901.647, 118848.136, 485908.501, 118853.952, 485918.
859, 118862.357, 485934.967, 118871.195, 485953.838, 118875.993, 485965.119, 118
880.755, 485977.271, 118885.079, 485989.203, 118888.806, 486000.485, 118892.693,
 486013.364, 118895.811, 486024.943, 118899.12, 486038.589, 118903.066, 486054.9
83, 118905.911, 486065.616, 118908.973, 486076.034, 118911.951, 486085.532, 1189
14.728, 486093.713, 118916.013, 486097.343, 118917.327, 486100.962, 118918.671,
486104.57, 118920.044, 486108.167, 118921.447, 486111.753, 118922.878, 486115.32
7, 118924.338, 486118.89, 118925.828, 486122.441, 118927.345, 486125.979, 118928
.892, 486129.505, 118930.467, 486133.018, 118932.071, 486136.519, 118933.703, 48
6140.006, 118935.363, 486143.48, 118937.051, 486146.941, 118938.768, 486150.387,
 118940.512, 486153.82, 118942.284, 486157.238, 118944.084, 486160.642, 118945.9
11, 486164.031, 119037.888, 486328.783, 119095.168, 486431.384, 119095.307, 4864
31.652, 119180.348, 486595.835, 119246.355, 486711.346, 119317.075, 486835.105,
119437.335, 487045.556, 119529.09, 487206.125, 119589.702, 487311.78, 119445.363
, 487512.445, 119442.334, 487516.308, 119438.747, 487520.882, 119435.065, 487525
.945, 119432.095, 487530.351, 119429.286, 487534.879, 119425.984, 487540.833, 11
9423.068, 487546.849, 119420.4, 487553.094, 119418.033, 487559.586, 119416.288,
487565.743, 119414.523, 487571.987, 119413.714, 487575.761, 119412.258, 487582.5
56, 119410.773, 487589.486, 119408.455, 487600.301, 119401.51, 487632.706, 11929
4.285, 487609.617, 119274.622, 487605.448, 119240.005, 487598.142, 119201.794, 4
87591.662, 119129.954, 487579.139, 119091.835, 487568.846, 119081.716, 487565.79
1, 119063.027, 487561.803, 119010.337, 487552.297, 119001.16, 487551.341, 118993
.21, 487550.513, 118941.382, 487545.509, 118797.855, 487519.916, 118796.575, 487
519.688, 118769.384, 487513.972, 118767.045, 487513.47, 118764.78, 487512.953, 1
18762.948, 487512.459, 118761.261, 487512.004, 118759.507, 487511.461, 118757.75
9, 487510.919, 118756.303, 487510.424, 118754.63, 487509.854, 118753.126, 487509
.275, 118751.252, 487508.552, 118749.349, 487507.758, 118749.21, 487507.7, 11874
9.081, 487507.646, 118747.722, 487507.068, 118746.439, 487506.473, 118744.924, 4
87505.77, 118743.631, 487505.12, 118743.322, 487504.964, 118741.974, 487504.287,
 118740.485, 487503.487, 118739.159, 487502.765, 118737.958, 487502.062, 118736.
304, 487501.08, 118734.297, 487499.839, 118730.843, 487497.545, 118727.689, 4874
95.338, 118724.414, 487493.054, 118645.765, 487441.01, 118643.664, 487439.62, 11
8642.483, 487438.83, 118640.849, 487437.724, 118639.169, 487436.613, 118638.655,
 487436.275, 118637.565, 487435.582, 118636.007, 487434.609, 118634.444, 487433.
671, 118632.885, 487432.762, 118631.052, 487431.734, 118628.852, 487430.551, 118
626.972, 487429.585, 118625.291, 487428.749, 118623.345, 487427.833, 118622.007,
 487427.211, 118621.061, 487426.795, 118619.483, 487426.107, 118617.709, 487425.
367, 118615.875, 487424.637, 118614.014, 487423.952, 118612.438, 487423.456, 118
610.033, 487422.699, 118607.027, 487421.884, 118581.194, 487414.88, 118575.852,
487413.432, 118492.906, 487390.088, 118457.809, 487378.953, 118407.891, 487362.6
72, 118369.415, 487351.229, 118339.813, 487343.432, 118317.807, 487337.636, 1182
64.97, 487323.505, 118241.394, 487316.823, 118219.968, 487310.842, 118204.563, 4
87308.721, 118196.753, 487307.646, 118192.554, 487307.129, 118185.17, 487305.877
, 118174.116, 487303.562, 118168.374, 487302.085, 118166.757, 487301.636, 118165
.165, 487301.138, 118164.04, 487300.746, 118162.81, 487300.295, 118161.25, 48729
9.664, 118155.424, 487297.195, 118149.15, 487295.004, 118137.773, 487292.075, 11
8134.764, 487291.552, 118131.658, 487291.024, 118128.656, 487290.527, 118126.709
, 487290.218, 118124.701, 487289.913, 118122.637, 487289.611, 118120.608, 487289
.328, 118118.541, 487289.053, 118116.504, 487288.794, 118114.531, 487288.557, 11
8112.897, 487288.304, 118110.156, 487287.989, 118107.483, 487287.697, 118104.786
, 487287.419, 118102.114, 487287.158, 118099.45, 487286.914, 118096.739, 487286.
682, 118094.072, 487286.469, 118091.393, 487286.271, 118088.687, 487286.087, 118
086.015, 487285.92, 118083.296, 487285.766, 118080.626, 487285.631, 118077.943,
487285.511, 118075.23, 487285.405, 118072.553, 487285.316, 118069.838, 487285.24
2, 118067.266, 487285.186, 118065.859, 487285.08, 118033.798, 487284.315, 117911
.819, 487288.677, 117880.174, 487290.864, 117878.623, 487254.234, 117877.527, 48
7209.536, 117877.633, 487140.809, 117879.119, 486995.131, 118088.077, 486997.841
, 118095.326, 486998.079, 118099.517, 486998.248, 118104.687, 486998.502, 118108
.21, 486998.701, 118112.589, 486998.977, 118116.467, 486999.249, 118122.061, 486
999.684, 118126.222, 487000.059, 118131.128, 487000.501, 118139.787, 487001.41,
118147.255, 487002.296, 118153.927, 487003.167, 118162.154, 487004.345, 118169.1
93, 487005.445, 118175.829, 487006.56, 118183.229, 487007.894, 118189.448, 48700
9.088, 118195.655, 487010.348, 118201.031, 487011.498, 118202.777, 487011.892, 1
18209.515, 487013.415, 118210.724, 487013.69, 118215.694, 487014.957, 118218.948
, 487015.77, 118221.588, 487016.429, 118225.443, 487017.378, 118228.451, 487018.
112, 118235.547, 487019.82, 118243.144, 487021.617, 118246.173, 487022.334, 1182
52.239, 487023.769, 118259.93, 486995.531, 118301.772, 487006.827, 118324.91, 48
6922.4, 118322.865, 486919.463, 118331.425, 486888.237, 118336.225, 486872.547,
118340.442, 486855.218, 118343.124, 486845.604, 118371.018, 486743.084, 118373.4
86, 486734.307, 118374.966, 486729.044, 118379.41, 486713.24, 118385.492, 486690
.871, 118387.11, 486684.916, 118390.976, 486671.22, 118431.976, 486521.799, 1184
39.883, 486509.312, 118440.335, 486507.209, 118440.739, 486505.096, 118441.093,
486502.974, 118441.57, 486498.57, 118442.756, 486413.142, 118443.219, 486408.342
, 118444.36, 486396.525, 118409.957, 486396.122, 118410.17, 486376.4, 118418.66,
 486366.64, 118418.76, 486357.28, 118411.25, 486357.28, 118411.757, 486353.838,
118408.85, 486353.81, 118398.328, 486353.662, 118389.842, 486353.542, 118390.813
, 486299.74))


SCOTT@orcl_11g>

Re: Load WKT using sql*loader [message #461310 is a reply to message #446795] Thu, 17 June 2010 06:25 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
Hi again,

when trying to import wkt into a table, I discovered that an entire record is ignored if the geometrie column is empty. I have tried to use NULLIF, but in combination with EXPRESSION it generates an error. I do want to record to be imported and a null for the geometry.

Any suggestions? thank you

Errormessage:
Record 4: geweigerd - fout in tabel TEST_EXP2SQLLDR, kolom GEOMETRIE.
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "MDSYS.SDO_UTIL", line 147

data:
3806360|3630039852233|POINT (120890.0 485964.0)
3806361|3630039852234|POINT (120890.0 485964.0)
3806362|3630039852235|POINT (120890.0 485964.0)
3806363|3630039852236|
3806364|3630039852237|POINT (122060.0 488406.0)
3806365|3630039852238|POINT (122081.0 488443.0)
3806366|3630039852239|POINT (122085.0 488446.0)
3806367|3630039852240|POINT (122079.0 488495.0)
3806368|3630039852241|POINT (122347.0 488581.0)
3806369|3630039852242|POINT (122275.0 488538.0)

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
,GEOMETRIE_DATA BOUNDFILLER
,GEOMETRIE EXPRESSION "sdo_util.from_wktgeometry(:GEOMETRIE_DATA)" NULLIF :GEOMETRIE_DATA=blanks
)

[Updated on: Thu, 17 June 2010 06:26]

Report message to a moderator

Re: Load WKT using sql*loader [message #461435 is a reply to message #461310] Thu, 17 June 2010 23:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The problem is that the from_wktgeometry function does not accept a null value as an input parameter, as shown below.

SCOTT@orcl_11g> select sdo_util.from_wktgeometry('') from dual;
select sdo_util.from_wktgeometry('') from dual
       *
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.NullPointerException
ORA-06512: at "MDSYS.SDO_UTIL", line 177


SCOTT@orcl_11g>


A workaround is to use a wrapper function to handle the null value, as shown below.

SCOTT@orcl_11g> create or replace function from_wktgeom_wrapper
  2    (p_data in clob)
  3    return	  mdsys.sdo_geometry
  4  as
  5  begin
  6    if p_data is null
  7    then return null;
  8    else return sdo_util.from_wktgeometry (p_data);
  9    end if;
 10  end from_wktgeom_wrapper;
 11  /

Function created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> select from_wktgeom_wrapper ('')
  2  from   dual
  3  /

FROM_WKTGEOM_WRAPPER('')(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO,
--------------------------------------------------------------------------------


SCOTT@orcl_11g>


The following applies this to your problem.

LOAD DATA
INFILE *
APPEND INTO TABLE test_exp2sqlldr
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '$$'
TRAILING NULLCOLS
(
ID DECIMAL EXTERNAL
,VOT_ID DECIMAL EXTERNAL
,GEOMETRIE_DATA BOUNDFILLER
,GEOMETRIE EXPRESSION "from_wktgeom_wrapper (:GEOMETRIE_DATA)"
)
begindata:
3806360|3630039852233|POINT (120890.0 485964.0)
3806361|3630039852234|POINT (120890.0 485964.0)
3806362|3630039852235|POINT (120890.0 485964.0)
3806363|3630039852236|
3806364|3630039852237|POINT (122060.0 488406.0)
3806365|3630039852238|POINT (122081.0 488443.0)
3806366|3630039852239|POINT (122085.0 488446.0)
3806367|3630039852240|POINT (122079.0 488495.0)
3806368|3630039852241|POINT (122347.0 488581.0)
3806369|3630039852242|POINT (122275.0 488538.0)

SCOTT@orcl_11g> CREATE TABLE test_exp2sqlldr
  2    (id	   NUMBER,
  3  	vot_id	   NUMBER,
  4  	geometrie  mdsys.sdo_geometry)
  5  /

Table created.

SCOTT@orcl_11g> create or replace function from_wktgeom_wrapper
  2    (p_data in clob)
  3    return	  mdsys.sdo_geometry
  4  as
  5  begin
  6    if p_data is null
  7    then return null;
  8    else return sdo_util.from_wktgeometry (p_data);
  9    end if;
 10  end from_wktgeom_wrapper;
 11  /

Function created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> hoSt Sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11g> select * from test_exp2sqlldr
  2  /

        ID     VOT_ID
---------- ----------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
   3806360 3.6300E+12
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(120890, 485964, NULL), NULL, NULL)

   3806361 3.6300E+12
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(120890, 485964, NULL), NULL, NULL)

   3806362 3.6300E+12
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(120890, 485964, NULL), NULL, NULL)

   3806363 3.6300E+12


   3806364 3.6300E+12
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(122060, 488406, NULL), NULL, NULL)

   3806365 3.6300E+12
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(122081, 488443, NULL), NULL, NULL)

   3806366 3.6300E+12
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(122085, 488446, NULL), NULL, NULL)

   3806367 3.6300E+12
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(122079, 488495, NULL), NULL, NULL)

   3806368 3.6300E+12
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(122347, 488581, NULL), NULL, NULL)

   3806369 3.6300E+12
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(122275, 488538, NULL), NULL, NULL)


10 rows selected.

SCOTT@orcl_11g>







Re: Load WKT using sql*loader [message #461450 is a reply to message #446795] Fri, 18 June 2010 01:21 Go to previous message
bibber
Messages: 38
Registered: August 2006
Member
mmm, from_wktgeometry has some various drawbacks (null values and long strings), which seem common situations to me.

Thanks anyhow Barbara. Smile
Previous Topic: data pump worker parallelism
Next Topic: moving database 9i to 10g new hardware
Goto Forum:
  


Current Time: Thu Mar 28 11:14:43 CDT 2024