Home » RDBMS Server » Server Utilities » Re: Load WKT using sql*loader (split from http://www.orafaq.com/forum/t/155866/ by bb)
| Re: Load WKT using sql*loader (split from http://www.orafaq.com/forum/t/155866/ by bb) [message #666590] |
Wed, 15 November 2017 09:29  |
 |
frank.reimann
Messages: 8
Registered: November 2017
|
Junior Member |
|
|
Hello Barbara,
let me ask a question to the format referenced by bibber. I found this data as some kind of examples. Now I'm not really sure how these data was generated (terminated by '|' and continued lines start with '#').
We've to load some data w/o using datapump or imp/exp utility, only sqlplus is in this case usable and I can tell You, that we've to deal with geometry objects (polygons) that are defined by more than 500 points.
So, every hint or advise is very appreciated.
Thank You
Frank
|
|
|
|
| Re: Load WKT using sql*loader [message #666596 is a reply to message #666590] |
Wed, 15 November 2017 12:45  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Load data without using imp, or impdp?
you didn't mention sqlloader or external tables, you said only sql*plus. Well, sql*plus is not a loader, it is a query language, so you must be leaving something out. If you are saying you can only use sql*plus as a tool, you would have to write pl/sql and use UTL_FILE, but the files would have to reside on the server.
Please provide more information as the answer to your literal question is that it cannot be done unless you create a script with ACCEPT commands and have the user type in all the data while interactively running the script.
|
|
|
|
| Re: Load WKT using sql*loader [message #666597 is a reply to message #666596] |
Thu, 16 November 2017 01:13 |
|
frank.reimann
Messages: 8
Registered: November 2017
|
Junior Member |
|
|
Hello joy_division,
thank You for response.
Let me tell something more to the situation.
Yes, sqlplus isn't a tool for importing data. So in the described scenario we only have access to the database via a sqlplus connection, no further acces to database tool such as datapump or imp/exp ore something. Even utl_file isn't an option because of not permitted access to local resources on database server.
The only way to get data out of the system is to connect via a client system (oracle instant client incl. sqlplus) to the database host.
From the technical point of view we've to unload geometrical data (polygons) describing areas (ocassionally huge amount of vertices > 500) for processing issues and data matching in our local enviroment.
So our aproach is to spool out tha data via sqlplus into a local file on the client side host. This file will be transfered in the local enviroment for further processing as mentioned earlier. To load the data into out loacal database we'll use sql loader.
So far so good.
Assuming the working of a couple of examples in the oracle documentations (spatial, sdo_geometry objects, bulk loading geometrical data, ...) the question cames up, how to get the data into the format as given in the loader control file examples:
"LOAD DATA
INFILE *
...
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|/
"
At the moment I do not have any idea, how to get the data with a "simple" select into this format. Ok, we could transform it with some text processing (sed or awk). So this is what I wanted to know about target format in the referenced in examples.
Thanks for being patient 
Ciao Frank
|
|
|
|
| Re: Load WKT using sql*loader [message #666598 is a reply to message #666597] |
Thu, 16 November 2017 02:43 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
If you can connect to the database with SQL*Plus, then you must have Oracle Net configured correctly. So I would use the legacy exp/imp tools, they should be able to extract what you want and save it to your local system no problem. I don't think exp/imp is included in the Instant Client, you'll need to install the full client if that has it, or just install a database home (which is what I always do).
|
|
|
|
|
|
| Re: Load WKT using sql*loader [message #666610 is a reply to message #666597] |
Thu, 16 November 2017 05:56 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Instead of trying to get your data into the format that somebody else's data happened to be in, it might be better to start with whatever format your data is in. Please post a copy and paste of what you get when you select a few simple records, then we can see how that can be loaded or how the select statement can be modified to make it easier to load.
[Updated on: Thu, 16 November 2017 05:58] Report message to a moderator |
|
|
|
| Re: Load WKT using sql*loader [message #666611 is a reply to message #666606] |
Thu, 16 November 2017 08:22 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
cookiemonster wrote on Thu, 16 November 2017 05:45joy_division wrote on Wed, 15 November 2017 18:45Well, sql*plus is not a loader, it is a query language
?
Yeah, yeah, yeah, bad choice of words. I was trying to make the point that it is not a command line tool for loading data like the others. I failed miserably. And if the emojis were working, I'd post one of a dog with it's tail between it's legs. Thankfully, they ain't, so I don't have to.
|
|
|
|
| Re: Load WKT using sql*loader [message #666613 is a reply to message #666610] |
Thu, 16 November 2017 09:12 |
|
frank.reimann
Messages: 8
Registered: November 2017
|
Junior Member |
|
|
Hello and tahnks for all responses,
...
@John Watson:
unfortunately the access on tools is restricted to the use of instant client or sqlplus. So no exp/imp functionality, sorry.
@Barbara:
We executed a select and we received some files with spooled data in. After some basic transformation we've got the following (You ask for some sample lines):
1062855537641571522|SDO_GEOMETRY(2003, 25832, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(669066.8736127, 5793671.4546435, 669105.1839686, 5793710.044637, 669091.5126917, 5793724.5777019, 669088.8882946, 5793727.2650303, 669079.4172321, 5793736.9634712, 669181.6056487, 5793835.49253, 669204.2388145, 5793857.3149339, 669209.2094669, 5793855.1993833, 669215.6271489, 5793862.1332542, 669187.2532584, 5793873.3263198, 669154.1222577, 5793886.3959927, 669110.8767115, 5793903.7067864, 669016.6453966, 5793941.1893832, 668983.1799101, 5793954.4404563, 668967.1496823, 5793960.8723442, 668965.1622643, 5793924.6152744, 668964.6138034, 5793903.5604697, 668961.9109217, 5793784.03893, 668960.8006845, 5793725.0729991, 668959.2715314, 5793667.3249793, 668958.6479436, 5793639.7499012, 668958.5266101, 5793589.4728605, 668958.0945732, 5793555.9643007, 669066.8736127, 5793671.4546435))
1062855537641571519|SDO_GEOMETRY(2003, 25832, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 39, 2003, 1), SDO_ORDINATE_ARRAY(671295.3986489, 5794146.3087128, 671294.5815726, 5794152.1508086, 671293.1604897, 5794161.3232528, 671291.8999348, 5794162.0795857, 671291.0321766, 5794162.4449576, 671226.3319301, 5794154.6385883, 671222.8748716, 5794154.2505512, 671187.6127874, 5794149.8254002, 671187.6684831, 5794150.2988137, 671187.4323954, 5794150.272314, 671180.9560352, 5794123.6799865, 671178.8238143, 5794114.421771, 671211.6669471, 5794115.3468049, 671243.4000933, 5794117.4591011, 671269.7744557, 5794119.1435831, 671289.3395718, 5794121.7763051, 671290.9969412, 5794122.0846529, 671298.2614089, 5794123.0090272, 671295.3986489, 5794146.3087128, 671226.1259999, 5794140.8129466, 671225.995095, 5794143.9723272, 671229.1544794, 5794144.10323, 671229.2853842, 5794140.9438494, 671226.1259999, 5794140.8129466))
-54037204203259280|SDO_GEOMETRY(2003, 25832, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(689559.8011975, 5672364.2559434, 689557.4642833, 5672363.369525, 689570.054728, 5672318.4776917, 689579.2702821, 5672298.1285621, 689589.1756461, 5672268.6315194, 689596.0252765, 5672244.2306245, 689606.6365525, 5672209.0668987, 689612.7318988, 5672195.3933617, 689618.7111999, 5672184.5628324, 689617.0384607, 5672191.5761073, 689615.3658051, 5672202.2770235, 689611.0513018, 5672219.3139846, 689605.1973598, 5672239.465882, 689601.6454122, 5672255.1215352, 689595.3076796, 5672276.3128392, 689591.5451314, 5672286.3105473, 689586.4501435, 5672300.6988749, 689559.8011975, 5672364.2559434))
-54037204203259278|SDO_GEOMETRY(2003, 25832, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 475, 2003, 1), SDO_ORDINATE_ARRAY(689178.599027, 5672282.9508707, 689176.4947509, 5672281.9086575, 689165.9626015, 5672277.7271185, 689150.3159048, 5672271.7923389, 689136.3421536, 5672268.1325362, 689115.6487595, 5672264.1984827, 689113.2314449, 5672263.8156026, 689112.3065927, 5672263.6002931, 689108.3433661, 5672263.0413766, 689092.1880457, 5672260.4825245, 689004.0502973, 5672246.8441851, 688987.3645809, 5672244.0781038, 688926.5375489, 5672234.4197575, 688923.0260607, 5672234.2078573, 688919.9396743, 5672233.7017081, 688910.3696812, 5672232.9800268, 688897.2522081, 5672232.6652507, 688895.1106135, 5672232.6198844, 688876.8073403, 5672231.7483, 688858.1318895, 5672229.5511881, 688824.3995781, 5672225.2784512, 688810.4121604, 5672223.5879136, 688786.5942759, 5672220.4459799, 688740.1520149, 5672213.2535943, 688739.1683355, 5672213.243711, 688725.9113375, 5672210.609368, 688724.7347724, 5672210.4209417, 688721.3567656, 5672209.5417235, 688722.2640151, 5672194.1144568, 688722.4792962, 5672193.5259082, 688722.9254021, 5672193.1189365, 688740.0053419, 5672197.8904258, 688750.7150613, 5672200.4239513, 688759.8174475, 5672201.7884995, 688767.7261477, 5672202.6629666, 688773.8902743, 5672203.0248691, 688779.5846251, 5672201.381617, 688779.9661364, 5672200.926693, 688780.0183805, 5672200.9237905, 688780.2349599, 5672200.6061407, 688782.7161693, 5672197.6474816, 688787.3875168, 5672191.328072, 688790.7619994, 5672187.0523459, 688793.7031353, 5672185.1499921, 688797.8589849, 5672183.4799905, 688804.6189014, 5672182.7628309, 688822.4236433, 5672184.4825477, 688825.2771922, 5672184.9089463, 688840.2279165, 5672187.1429952, 688840.3528318, 5672187.1661276, 688840.3929148, 5672187.1676504, 688841.7252146, 5672187.3667326, 688857.9264851, 5672187.8337778, 688860.1302423, 5672187.8973071, 688862.4560599, 5672187.7978288, 688863.2910428, 5672187.4799545, 688869.7786402, 5672186.6233313, 688876.2452788, 5672185.9107064, 688880.0369423, 5672185.4928642, 688887.3905916, 5672184.3376827, 688889.3570255, 5672184.193872, 688894.7358622, 5672183.8005029, 688909.7162875, 5672185.8765828, 688929.4994245, 5672195.1026968, 688934.527611, 5672199.5889372, 688944.2518154, 5672204.7277444, 688951.6833211, 5672207.2576187, 688962.2771698, 5672210.4199616, 688971.9223157, 5672212.3173673, 688980.5397, 5672213.2660702, 688986.9434444, 5672213.2660702, 689003.7038617, 5672209.234083, 689019.9899276, 5672208.0482044, 689041.2566836, 5672207.0995015, 689073.2754053, 5672207.3366773, 689088.8499441, 5672209.1550244, 689105.2150685, 5672211.7639573, 689121.580193, 5672215.2425345, 689143.6143583, 5672219.2900115, 689146.3030548, 5672220.3890619, 689151.0386937, 5672220.6537948, 689166.2197529, 5672220.0103688, 689177.2539249, 5672218.5566474, 689197.3773051, 5672219.2380055, 689207.3427181, 5672219.5754247, 689207.3466499, 5672219.5761867, 689233.2260149, 5672224.5914525, 689239.9071182, 5672225.5196698, 689247.1628566, 5672226.5276634, 689251.249516, 5672226.4796659, 689254.0797907, 5672224.7193348, 689255.8714114, 5672221.372052, 689258.846377, 5672212.4758426, 689258.8467061, 5672212.4748585, 689264.1862085, 5672195.0597142, 689266.7307578, 5672186.7604582, 689267.9214248, 5672183.6492769, 689268.0521668, 5672183.3500818, 689271.0942263, 5672176.3885103, 689273.8041834, 5672170.1869346, 689287.0751811, 5672147.502818, 689292.5191401, 5672142.2847366, 689299.3829221, 5672138.5557194, 689306.2610179, 5672136.0258451, 689314.7202851, 5672135.3143179, 689348.685221, 5672134.9519443, 689396.0309948, 5672133.5363983, 689416.3354092, 5672132.6880578, 689418.3812525, 5672132.7522219, 689425.5665287, 5672132.3023716, 689464.8694628, 5672129.0514206, 689485.290895, 5672126.1333665, 689503.0850162, 5672121.6374386, 689514.0326594, 5672120.044678, 689527.0463784, 5672119.5619159, 689540.411092, 5672119.224758, 689555.9846643, 5672120.4865145, 689570.481319, 5672123.2104577, 689599.3912896, 5672129.5715281, 689624.529635, 5672135.378406, 689659.3268291, 5672144.2701246, 689680.240749, 5672148.213174, 689691.0782101, 5672147.7788539, 689691.489964, 5672147.6865915, 689702.15725, 5672145.2963554, 689703.5737165, 5672144.9789655, 689709.1463313, 5672142.8829847, 689713.3569435, 5672139.7445624, 689714.8699614, 5672137.8201391, 689715.0744592, 5672135.5114556, 689713.4941594, 5672133.6814377, 689709.3977153, 5672132.6773369, 689696.5518903, 5672135.0197776, 689695.4265584, 5672135.2249823, 689692.3509273, 5672135.3563285, 689687.6547636, 5672134.1716091, 689679.6202212, 5672130.9749773, 689669.0408472, 5672125.2467629, 689666.8969027, 5672123.6477029, 689658.8682662, 5672117.6595484, 689651.2133725, 5672111.0194657, 689649.4612871, 5672109.4996547, 689646.3016558, 5672106.2617253, 689630.9396058, 5672094.7948978, 689616.7240175, 5672089.7510428, 689597.1558766, 5672086.6352826, 689590.6261149, 5672085.5955736, 689563.7168614, 5672084.4973399, 689523.2634145, 5672087.1382932, 689472.9955469, 5672090.8834003, 689471.8472257, 5672090.9293331, 689457.1838324, 5672091.5158689, 689447.6177452, 5672091.1996346, 689438.2097751, 5672090.2509317, 689428.3274535, 5672087.4048231, 689422.4676892, 5672083.3680689, 689418.0498391, 5672077.6806187, 689432.2406549, 5672032.8963104, 689440.065904, 5672009.7467589, 689441.175454, 5672004.5414301, 689447.4418864, 5671975.9512454, 689478.3301604, 5671990.7887839, 689489.3204371, 5671996.06809, 689495.779407, 5671999.3739366, 689597.5277667, 5672052.5933863, 689602.3895798, 5672055.1858974, 689657.1801227, 5672084.4023839, 689693.9809168, 5672104.6548554, 689703.0011218, 5672109.9741831, 689743.7507426, 5672135.7990826, 689761.4802772, 5672145.829108, 689778.1616359, 5672154.3674338, 689788.7554846, 5672159.4271825, 689796.819459, 5672164.0125797, 689803.1655894, 5672170.7415994, 689807.0043986, 5672175.8017781, 689810.1788388, 5672185.1254099, 689810.0683971, 5672189.3752825, 689810.0094402, 5672191.6439821, 689809.7492257, 5672201.6572087, 689808.8538055, 5672204.3847946, 689808.6891154, 5672204.6142022, 689785.6530398, 5672273.7224291, 689762.0676297, 5672342.4904189, 689759.5582625, 5672351.4243475, 689752.7289776, 5672373.9988578, 689752.6065068, 5672375.1725361, 689748.0148942, 5672390.8581395, 689747.0752117, 5672397.6607347, 689746.4443331, 5672413.7599364, 689748.851602, 5672421.3250603, 689751.6915973, 5672432.314692, 689751.8031959, 5672434.1746685, 689748.6290872, 5672439.5971041, 689741.857233, 5672444.2274703, 689723.5849996, 5672451.4304949, 689691.3885007, 5672461.8541881, 689679.1719849, 5672448.0873248, 689671.3681308, 5672441.8442414, 689653.5307501, 5672426.6824678, 689643.0512889, 5672419.3245483, 689630.7880897, 5672413.973334, 689605.1468548, 5672405.2776109, 689562.5601083, 5672391.4536408, 689518.8585254, 5672375.8459326, 689485.0600692, 5672362.7971616, 689483.6296984, 5672362.2449299, 689480.5081568, 5672360.0152572, 689480.1867627, 5672357.926196, 689482.003957, 5672349.9285335, 689484.252284, 5672340.0094439, 689488.3521744, 5672330.4871178, 689492.5843193, 5672320.3035191, 689493.9068646, 5672314.4843198, 689490.1760856, 5672311.2632911, 689490.1798946, 5672311.2358662, 689488.6150134, 5672310.6579801, 689484.2740947, 5672309.0549467, 689482.7378294, 5672308.5098203, 689381.5106935, 5672272.3891242, 689294.5534624, 5672240.0588717, 689287.6356851, 5672237.7005385, 689274.9323435, 5672233.3698538, 689267.298928, 5672232.2527686, 689265.7906859, 5672232.0320503, 689265.5698898, 5672232.2843887, 689262.6691443, 5672235.5995265, 689258.8907546, 5672245.6267913, 689250.9848715, 5672265.8640125, 689239.8580839, 5672297.2574489, 689238.428683, 5672296.8737963, 689231.5180497, 5672295.0189758, 689214.5352266, 5672290.4983682, 689208.4881002, 5672288.8887015, 689194.6258187, 5672286.7560428, 689191.0806672, 5672285.9143327, 689178.599027, 5672282.9508707, 689586.4501435, 5672300.6988749, 689591.5451314, 5672286.3105473, 689595.3076796, 5672276.3128392, 689601.6454122, 5672255.1215352, 689605.1973598, 5672239.465882, 689611.0513018, 5672219.3139846, 689615.3658051, 5672202.2770235, 689617.0384607, 5672191.5761073, 689618.7111999, 5672184.5628324, 689612.7318988, 5672195.3933617, 689606.6365525, 5672209.0668987, 689596.0252765, 5672244.2306245, 689589.1756461, 5672268.6315194, 689579.2702821, 5672298.1285621, 689570.054728, 5672318.4776917, 689557.4642833, 5672363.369525, 689559.8011975, 5672364.2559434, 689586.4501435, 5672300.6988749))
1062855540751902662|SDO_GEOMETRY(2003, 25832, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(655463.756251, 5776594.6645664, 655460.229957, 5776593.6645726, 655454.2685859, 5776591.3072371, 655453.2542305, 5776590.8875038, 655433.050204, 5776584.9638101, 655405.1899461, 5776578.8153394, 655364.4451972, 5776569.8233949, 655341.0213922, 5776564.6540034, 655308.335297, 5776557.6725073, 655292.0569576, 5776554.3088709, 655274.044575, 5776550.5869245, 655266.8694416, 5776549.1043076, 655259.681051, 5776548.3426239, 655251.8690337, 5776547.3327483, 655249.3239935, 5776547.3681395, 655243.2804473, 5776547.2002632, 655235.6642698, 5776547.9407249, 655221.1379607, 5776549.7979699, 655218.8829871, 5776549.9923642, 655217.1966495, 5776550.3018819, 655212.4983965, 5776550.9025718, 655186.8995775, 5776555.5569025, 655183.3030493, 5776556.5089247, 655173.5510466, 5776558.3001088, 655165.244078, 5776559.4077046, 655165.0105957, 5776558.7072579, 655166.811325, 5776555.1057994, 655172.3293727, 5776553.5730084, 655183.9785845, 5776550.8139846, 655212.1819393, 5776546.4199836, 655236.1956654, 5776543.0478434, 655244.574923, 5776542.536913, 655252.6476224, 5776542.6390991, 655268.1799047, 5776545.2959369, 655343.8997814, 5776560.1129168, 655435.5606846, 5776580.7545026, 655459.0424116, 5776586.5490183, 655461.9820201, 5776587.0389532, 655467.989616, 5776588.0402191, 655477.8857663, 5776587.7690917, 655487.2136161, 5776587.3022762, 655490.7775477, 5776587.123918, 655493.9925512, 5776586.7127889, 655502.693924, 5776585.6000724, 655504.6543017, 5776585.6000724, 655506.1935266, 5776592.2187394, 655490.8287331, 5776594.7017507, 655490.1943268, 5776590.3666411, 655465.1352787, 5776589.8379692, 655463.756251, 5776594.6645664))
Obviously the spooled data could be in WKT form e.g. but then we were faced with the lost if information about the coordinate system (srid). Last try to generate insert statements went wrong in case of poor performance. So we decided to try loading by sql loader. The challenge is to get all properly formated.
Thank You
Frank
|
|
|
|
| Re: Load WKT using sql*loader [message #666614 is a reply to message #666613] |
Thu, 16 November 2017 17:13 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I don't know what you mean by "after some basic transformation". The point of my asking for a copy and paste of what you get when you select a few simple records was to get some sample data without any transformations, in order to show what control file to use for the data that is in the format that you have, so that you don't have to make any transformations. I copied what you posted to a file named test.dat on my system. The following demonstrates how to load that data into a table. It assumes that all of your data is in the format that you posted, including the first character of each new record being a space and all sdo_point_type being null.
-- SQL*Loader control file test.ctl:
LOAD DATA
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE test
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
col1 INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
col2 COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ',',
SDO_SRID INTEGER EXTERNAL TERMINATED BY ',',
SDO_POINT FILLER TERMINATED BY ', SDO_ELEM_INFO_ARRAY(',
SDO_ELEM_INFO VARRAY TERMINATED BY '), SDO_ORDINATE_ARRAY('
(elements FLOAT EXTERNAL),
SDO_ORDINATES VARRAY TERMINATED BY '))'
(ordinates FLOAT EXTERNAL)
)
)
-- test table to load data into:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test
2 ( col1 NUMBER
3 , col2 MDSYS.SDO_GEOMETRY )
4 /
Table created.
-- load data:
SCOTT@orcl_12.1.0.2.0> HOST SQLLDR scott/tiger CONTROL=test.ctl DATA=test.dat LOG=test.log
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Nov 16 15:01:08 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 4
Commit point reached - logical record count 5
Table TEST:
5 Rows successfully loaded.
Check the log file:
test.log
for more information about the load.
-- result:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test
2 /
COL1
------------------------------
COL2(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
1062855537641571522
SDO_GEOMETRY(2003, 25832, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(669066.8736127, 5793671.4546435, 669105.1839686, 5793710.044637, 669091.5126
917, 5793724.5777019, 669088.8882946, 5793727.2650303, 669079.4172321, 5793736.9
634712, 669181.6056487, 5793835.49253, 669204.2388145, 5793857.3149339, 669209.2
094669, 5793855.1993833, 669215.6271489, 5793862.1332542, 669187.2532584, 579387
3.3263198, 669154.1222577, 5793886.3959927, 669110.8767115, 5793903.7067864, 669
016.6453966, 5793941.1893832, 668983.1799101, 5793954.4404563, 668967.1496823, 5
793960.8723442, 668965.1622643, 5793924.6152744, 668964.6138034, 5793903.5604697
, 668961.9109217, 5793784.03893, 668960.8006845, 5793725.0729991, 668959.2715314
, 5793667.3249793, 668958.6479436, 5793639.7499012, 668958.5266101, 5793589.4728
605, 668958.0945732, 5793555.9643007, 669066.8736127, 5793671.4546435))
1062855537641571519
SDO_GEOMETRY(2003, 25832, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 39, 2003, 1), SD
O_ORDINATE_ARRAY(671295.3986489, 5794146.3087128, 671294.5815726, 5794152.150808
6, 671293.1604897, 5794161.3232528, 671291.8999348, 5794162.0795857, 671291.0321
766, 5794162.4449576, 671226.3319301, 5794154.6385883, 671222.8748716, 5794154.2
505512, 671187.6127874, 5794149.8254002, 671187.6684831, 5794150.2988137, 671187
.4323954, 5794150.272314, 671180.9560352, 5794123.6799865, 671178.8238143, 57941
14.421771, 671211.6669471, 5794115.3468049, 671243.4000933, 5794117.4591011, 671
269.7744557, 5794119.1435831, 671289.3395718, 5794121.7763051, 671290.9969412, 5
794122.0846529, 671298.2614089, 5794123.0090272, 671295.3986489, 5794146.3087128
, 671226.1259999, 5794140.8129466, 671225.995095, 5794143.9723272, 671229.154479
4, 5794144.10323, 671229.2853842, 5794140.9438494, 671226.1259999, 5794140.81294
66))
-54037204203259280
SDO_GEOMETRY(2003, 25832, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(689559.8011975, 5672364.2559434, 689557.4642833, 5672363.369525, 689570.0547
28, 5672318.4776917, 689579.2702821, 5672298.1285621, 689589.1756461, 5672268.63
15194, 689596.0252765, 5672244.2306245, 689606.6365525, 5672209.0668987, 689612.
7318988, 5672195.3933617, 689618.7111999, 5672184.5628324, 689617.0384607, 56721
91.5761073, 689615.3658051, 5672202.2770235, 689611.0513018, 5672219.3139846, 68
9605.1973598, 5672239.465882, 689601.6454122, 5672255.1215352, 689595.3076796, 5
672276.3128392, 689591.5451314, 5672286.3105473, 689586.4501435, 5672300.6988749
, 689559.8011975, 5672364.2559434))
-54037204203259278
SDO_GEOMETRY(2003, 25832, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 475, 2003, 1), S
DO_ORDINATE_ARRAY(689178.599027, 5672282.9508707, 689176.4947509, 5672281.908657
5, 689165.9626015, 5672277.7271185, 689150.3159048, 5672271.7923389, 689136.3421
536, 5672268.1325362, 689115.6487595, 5672264.1984827, 689113.2314449, 5672263.8
156026, 689112.3065927, 5672263.6002931, 689108.3433661, 5672263.0413766, 689092
.1880457, 5672260.4825245, 689004.0502973, 5672246.8441851, 688987.3645809, 5672
244.0781038, 688926.5375489, 5672234.4197575, 688923.0260607, 5672234.2078573, 6
88919.9396743, 5672233.7017081, 688910.3696812, 5672232.9800268, 688897.2522081,
5672232.6652507, 688895.1106135, 5672232.6198844, 688876.8073403, 5672231.7483,
688858.1318895, 5672229.5511881, 688824.3995781, 5672225.2784512, 688810.412160
4, 5672223.5879136, 688786.5942759, 5672220.4459799, 688740.1520149, 5672213.253
5943, 688739.1683355, 5672213.243711, 688725.9113375, 5672210.609368, 688724.734
7724, 5672210.4209417, 688721.3567656, 5672209.5417235, 688722.2640151, 5672194.
1144568, 688722.4792962, 5672193.5259082, 688722.9254021, 5672193.1189365, 68874
0.0053419, 5672197.8904258, 688750.7150613, 5672200.4239513, 688759.8174475, 567
2201.7884995, 688767.7261477, 5672202.6629666, 688773.8902743, 5672203.0248691,
688779.5846251, 5672201.381617, 688779.9661364, 5672200.926693, 688780.0183805,
5672200.9237905, 688780.2349599, 5672200.6061407, 688782.7161693, 5672197.647481
6, 688787.3875168, 5672191.328072, 688790.7619994, 5672187.0523459, 688793.70313
53, 5672185.1499921, 688797.8589849, 5672183.4799905, 688804.6189014, 5672182.76
28309, 688822.4236433, 5672184.4825477, 688825.2771922, 5672184.9089463, 688840.
2279165, 5672187.1429952, 688840.3528318, 5672187.1661276, 688840.3929148, 56721
87.1676504, 688841.7252146, 5672187.3667326, 688857.9264851, 5672187.8337778, 68
8860.1302423, 5672187.8973071, 688862.4560599, 5672187.7978288, 688863.2910428,
5672187.4799545, 688869.7786402, 5672186.6233313, 688876.2452788, 5672185.910706
4, 688880.0369423, 5672185.4928642, 688887.3905916, 5672184.3376827, 688889.3570
255, 5672184.193872, 688894.7358622, 5672183.8005029, 688909.7162875, 5672185.87
65828, 688929.4994245, 5672195.1026968, 688934.527611, 5672199.5889372, 688944.2
518154, 5672204.7277444, 688951.6833211, 5672207.2576187, 688962.2771698, 567221
0.4199616, 688971.9223157, 5672212.3173673, 688980.5397, 5672213.2660702, 688986
.9434444, 5672213.2660702, 689003.7038617, 5672209.234083, 689019.9899276, 56722
08.0482044, 689041.2566836, 5672207.0995015, 689073.2754053, 5672207.3366773, 68
9088.8499441, 5672209.1550244, 689105.2150685, 5672211.7639573, 689121.580193, 5
672215.2425345, 689143.6143583, 5672219.2900115, 689146.3030548, 5672220.3890619
, 689151.0386937, 5672220.6537948, 689166.2197529, 5672220.0103688, 689177.25392
49, 5672218.5566474, 689197.3773051, 5672219.2380055, 689207.3427181, 5672219.57
54247, 689207.3466499, 5672219.5761867, 689233.2260149, 5672224.5914525, 689239.
9071182, 5672225.5196698, 689247.1628566, 5672226.5276634, 689251.249516, 567222
6.4796659, 689254.0797907, 5672224.7193348, 689255.8714114, 5672221.372052, 6892
58.846377, 5672212.4758426, 689258.8467061, 5672212.4748585, 689264.1862085, 567
2195.0597142, 689266.7307578, 5672186.7604582, 689267.9214248, 5672183.6492769,
689268.0521668, 5672183.3500818, 689271.0942263, 5672176.3885103, 689273.8041834
, 5672170.1869346, 689287.0751811, 5672147.502818, 689292.5191401, 5672142.28473
66, 689299.3829221, 5672138.5557194, 689306.2610179, 5672136.0258451, 689314.720
2851, 5672135.3143179, 689348.685221, 5672134.9519443, 689396.0309948, 5672133.5
363983, 689416.3354092, 5672132.6880578, 689418.3812525, 5672132.7522219, 689425
.5665287, 5672132.3023716, 689464.8694628, 5672129.0514206, 689485.290895, 56721
26.1333665, 689503.0850162, 5672121.6374386, 689514.0326594, 5672120.044678, 689
527.0463784, 5672119.5619159, 689540.411092, 5672119.224758, 689555.9846643, 567
2120.4865145, 689570.481319, 5672123.2104577, 689599.3912896, 5672129.5715281, 6
89624.529635, 5672135.378406, 689659.3268291, 5672144.2701246, 689680.240749, 56
72148.213174, 689691.0782101, 5672147.7788539, 689691.489964, 5672147.6865915, 6
89702.15725, 5672145.2963554, 689703.5737165, 5672144.9789655, 689709.1463313, 5
672142.8829847, 689713.3569435, 5672139.7445624, 689714.8699614, 5672137.8201391
, 689715.0744592, 5672135.5114556, 689713.4941594, 5672133.6814377, 689709.39771
53, 5672132.6773369, 689696.5518903, 5672135.0197776, 689695.4265584, 5672135.22
49823, 689692.3509273, 5672135.3563285, 689687.6547636, 5672134.1716091, 689679.
6202212, 5672130.9749773, 689669.0408472, 5672125.2467629, 689666.8969027, 56721
23.6477029, 689658.8682662, 5672117.6595484, 689651.2133725, 5672111.0194657, 68
9649.4612871, 5672109.4996547, 689646.3016558, 5672106.2617253, 689630.9396058,
5672094.7948978, 689616.7240175, 5672089.7510428, 689597.1558766, 5672086.635282
6, 689590.6261149, 5672085.5955736, 689563.7168614, 5672084.4973399, 689523.2634
145, 5672087.1382932, 689472.9955469, 5672090.8834003, 689471.8472257, 5672090.9
293331, 689457.1838324, 5672091.5158689, 689447.6177452, 5672091.1996346, 689438
.2097751, 5672090.2509317, 689428.3274535, 5672087.4048231, 689422.4676892, 5672
083.3680689, 689418.0498391, 5672077.6806187, 689432.2406549, 5672032.8963104, 6
89440.065904, 5672009.7467589, 689441.175454, 5672004.5414301, 689447.4418864, 5
671975.9512454, 689478.3301604, 5671990.7887839, 689489.3204371, 5671996.06809,
689495.779407, 5671999.3739366, 689597.5277667, 5672052.5933863, 689602.3895798,
5672055.1858974, 689657.1801227, 5672084.4023839, 689693.9809168, 5672104.65485
54, 689703.0011218, 5672109.9741831, 689743.7507426, 5672135.7990826, 689761.480
2772, 5672145.829108, 689778.1616359, 5672154.3674338, 689788.7554846, 5672159.4
271825, 689796.819459, 5672164.0125797, 689803.1655894, 5672170.7415994, 689807.
0043986, 5672175.8017781, 689810.1788388, 5672185.1254099, 689810.0683971, 56721
89.3752825, 689810.0094402, 5672191.6439821, 689809.7492257, 5672201.6572087, 68
9808.8538055, 5672204.3847946, 689808.6891154, 5672204.6142022, 689785.6530398,
5672273.7224291, 689762.0676297, 5672342.4904189, 689759.5582625, 5672351.424347
5, 689752.7289776, 5672373.9988578, 689752.6065068, 5672375.1725361, 689748.0148
942, 5672390.8581395, 689747.0752117, 5672397.6607347, 689746.4443331, 5672413.7
599364, 689748.851602, 5672421.3250603, 689751.6915973, 5672432.314692, 689751.8
031959, 5672434.1746685, 689748.6290872, 5672439.5971041, 689741.857233, 5672444
.2274703, 689723.5849996, 5672451.4304949, 689691.3885007, 5672461.8541881, 6896
79.1719849, 5672448.0873248, 689671.3681308, 5672441.8442414, 689653.5307501, 56
72426.6824678, 689643.0512889, 5672419.3245483, 689630.7880897, 5672413.973334,
689605.1468548, 5672405.2776109, 689562.5601083, 5672391.4536408, 689518.8585254
, 5672375.8459326, 689485.0600692, 5672362.7971616, 689483.6296984, 5672362.2449
299, 689480.5081568, 5672360.0152572, 689480.1867627, 5672357.926196, 689482.003
957, 5672349.9285335, 689484.252284, 5672340.0094439, 689488.3521744, 5672330.48
71178, 689492.5843193, 5672320.3035191, 689493.9068646, 5672314.4843198, 689490.
1760856, 5672311.2632911, 689490.1798946, 5672311.2358662, 689488.6150134, 56723
10.6579801, 689484.2740947, 5672309.0549467, 689482.7378294, 5672308.5098203, 68
9381.5106935, 5672272.3891242, 689294.5534624, 5672240.0588717, 689287.6356851,
5672237.7005385, 689274.9323435, 5672233.3698538, 689267.298928, 5672232.2527686
, 689265.7906859, 5672232.0320503, 689265.5698898, 5672232.2843887, 689262.66914
43, 5672235.5995265, 689258.8907546, 5672245.6267913, 689250.9848715, 5672265.86
40125, 689239.8580839, 5672297.2574489, 689238.428683, 5672296.8737963, 689231.5
180497, 5672295.0189758, 689214.5352266, 5672290.4983682, 689208.4881002, 567228
8.8887015, 689194.6258187, 5672286.7560428, 689191.0806672, 5672285.9143327, 689
178.599027, 5672282.9508707, 689586.4501435, 5672300.6988749, 689591.5451314, 56
72286.3105473, 689595.3076796, 5672276.3128392, 689601.6454122, 5672255.1215352,
689605.1973598, 5672239.465882, 689611.0513018, 5672219.3139846, 689615.3658051
, 5672202.2770235, 689617.0384607, 5672191.5761073, 689618.7111999, 5672184.5628
324, 689612.7318988, 5672195.3933617, 689606.6365525, 5672209.0668987, 689596.02
52765, 5672244.2306245, 689589.1756461, 5672268.6315194, 689579.2702821, 5672298
.1285621, 689570.054728, 5672318.4776917, 689557.4642833, 5672363.369525, 689559
.8011975, 5672364.2559434, 689586.4501435, 5672300.6988749))
1062855540751902662
SDO_GEOMETRY(2003, 25832, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(655463.756251, 5776594.6645664, 655460.229957, 5776593.6645726, 655454.26858
59, 5776591.3072371, 655453.2542305, 5776590.8875038, 655433.050204, 5776584.963
8101, 655405.1899461, 5776578.8153394, 655364.4451972, 5776569.8233949, 655341.0
213922, 5776564.6540034, 655308.335297, 5776557.6725073, 655292.0569576, 5776554
.3088709, 655274.044575, 5776550.5869245, 655266.8694416, 5776549.1043076, 65525
9.681051, 5776548.3426239, 655251.8690337, 5776547.3327483, 655249.3239935, 5776
547.3681395, 655243.2804473, 5776547.2002632, 655235.6642698, 5776547.9407249, 6
55221.1379607, 5776549.7979699, 655218.8829871, 5776549.9923642, 655217.1966495,
5776550.3018819, 655212.4983965, 5776550.9025718, 655186.8995775, 5776555.55690
25, 655183.3030493, 5776556.5089247, 655173.5510466, 5776558.3001088, 655165.244
078, 5776559.4077046, 655165.0105957, 5776558.7072579, 655166.811325, 5776555.10
57994, 655172.3293727, 5776553.5730084, 655183.9785845, 5776550.8139846, 655212.
1819393, 5776546.4199836, 655236.1956654, 5776543.0478434, 655244.574923, 577654
2.536913, 655252.6476224, 5776542.6390991, 655268.1799047, 5776545.2959369, 6553
43.8997814, 5776560.1129168, 655435.5606846, 5776580.7545026, 655459.0424116, 57
76586.5490183, 655461.9820201, 5776587.0389532, 655467.989616, 5776588.0402191,
655477.8857663, 5776587.7690917, 655487.2136161, 5776587.3022762, 655490.7775477
, 5776587.123918, 655493.9925512, 5776586.7127889, 655502.693924, 5776585.600072
4, 655504.6543017, 5776585.6000724, 655506.1935266, 5776592.2187394, 655490.8287
331, 5776594.7017507, 655490.1943268, 5776590.3666411, 655465.1352787, 5776589.8
379692, 655463.756251, 5776594.6645664))
5 rows selected.
|
|
|
|
|
|
| Re: Load WKT using sql*loader [message #666877 is a reply to message #666622] |
Thu, 30 November 2017 01:44 |
|
frank.reimann
Messages: 8
Registered: November 2017
|
Junior Member |
|
|
Hello Barbara,
the last discussed issue works fine for the moment, but I'm faced with a new situation.
In delivered csv files different kinds of SDO_GEOMETRY data is mixed up. As You can see in my provided example data there are polygons as well as points. My first try was to separate polygon data from the point data in different files and then load them in separately executed sql loader sessions. But this works fine only for the part with the ploygon data. All point data could only be loaded replacing the decimal point by a comma in the values of x and y.
What is Your recommendation for processing (loading) the data?
Thanks in advance,
Bye Frank
1) Example Data
-126094947963131504|SDO_GEOMETRY(2003, 4647, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(32512555.70613779873, 6025825.636290729977, 32512550.83485529944, 6025818.708380140364, 32512545.65460190177, 6025807.123500409536, 32512543.01040489972, 6025796.54514419008, 32512541.95162370056, 6025786.892361969687, 32512544.33238830045, 6025769.436931439675, 32512551.20756930113, 6025705.831552520394, 32512562.5785018988, 6025613.928488469683, 32512574.47800410166, 6025510.389547759667, 32512586.28864509985, 6025412.926016679965, 32512593.51787060127, 6025359.377372499555, 32512595.10525650159, 6025358.848599120043, 32512596.95658250153, 6025360.435900770128, 32512634.33800140023, 6025393.832178629935, 32512652.23450290039, 6025409.891077009961, 32512678.4189410992, 6025430.519918469712, 32512685.56031170115, 6025435.014912510291, 32512698.5194851011, 6025440.30552764982, 32512745.33299810067, 6025456.701080350205, 32512763.58318740129, 6025463.445011160336, 32512819.1908344999, 6025482.257517449558, 32512828.11704799905, 6025484.205474649556, 32512893.97324709967, 6025496.635563739575, 32512900.05696219951, 6025498.089879569598, 32512909.31347610056, 6025499.809057270177, 32512949.51546369866, 6025506.684701099992, 32513032.29969409853, 6025522.552019570023, 32513033.09342209995, 6025523.873427590355, 32513032.82858930156, 6025527.31190478988, 32513012.73076440021, 6025660.339014720172, 32512993.95423660055, 6025783.051627789624, 32512986.28517020121, 6025836.47393022012, 32512984.96268809959, 6025837.531464920379, 32512954.01840750128, 6025842.027569389902, 32512940.265602801, 6025843.350026310422, 32512915.66795140132, 6025847.846151470207, 32512901.12153270096, 6025850.888019329868, 32512898.21265760064, 6025851.945593480021, 32512877.31870840117, 6025858.028224219568, 32512858.53989829868, 6025861.20111544989, 32512846.10960549861, 6025860.673540069722, 32512839.23323610052, 6025861.732180359773, 32512826.2731859982, 6025863.318683080375, 32512787.39401879907, 6025867.287543689832, 32512769.14391890168, 6025865.964408989996, 32512764.01986249909, 6025865.913511140272, 32512761.71175070107, 6025866.542271340266, 32512751.01079960167, 6025865.913713710383, 32512743.87751070037, 6025866.122733259574, 32512718.27815920115, 6025862.766601050273, 32512706.52661399916, 6025862.556875119917, 32512693.51854560152, 6025862.347168579698, 32512689.0062229, 6025860.544026600197, 32512683.71620459855, 6025859.221686209552, 32512662.822015699, 6025853.139663719572, 32512639.28282539919, 6025846.792803750373, 32512636.63779349998, 6025844.94065484032, 32512631.08388070017, 6025843.883201629855, 32512619.18233110011, 6025840.445884689689, 32512588.23686730117, 6025829.603083129972, 32512585.32792830095, 6025827.35510734003, 32512581.4704034999, 6025827.939908660017, 32512579.50916180015, 6025828.676615930162, 32512569.19405639917, 6025827.619228740223, 32512560.46637370065, 6025827.884241269901, 32512555.70613779873, 6025825.636290729977))
-126094947963131501|SDO_GEOMETRY(2003, 4647, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(32494647.07723389938, 6047361.438862780109, 32494605.25351950154, 6047464.256969889626, 32494566.37276430055, 6047556.029602379538, 32494507.12915069982, 6047539.896046349779, 32494535.43056929857, 6047473.248942770064, 32494572.45794180036, 6047386.369259879924, 32494634.93126599863, 6047243.130144639872, 32494670.85081690177, 6047160.773290139623, 32494698.35719859973, 6047095.580389400013, 32494726.12941139936, 6047030.916237530299, 32494730.36098229885, 6047017.42883421015, 32494733.00575039908, 6047004.734055100009, 32494745.16904219985, 6046971.277592049912, 32494765.79468239844, 6046923.672004629858, 32494788.80091699958, 6046894.71200618986, 32494830.58370070159, 6046846.048484040424, 32494852.7997232005, 6046846.048807200044, 32494873.42733690143, 6046825.155797810294, 32494881.89153400064, 6046831.503292440437, 32494884.40214810148, 6046837.892668030225, 32494884.80108710006, 6046838.90925018955, 32494883.47932570055, 6046846.049253280275, 32494861.85975300148, 6046864.027479980141, 32494834.81968170032, 6046886.513855709694, 32494826.09152489901, 6046890.216236299835, 32494830.05942770094, 6046895.769989670254, 32494858.81460030004, 6046870.828620689921, 32494885.85949439928, 6046847.371736220084, 32494898.02668900043, 6046858.877099599689, 32494881.29416010156, 6046877.558092970401, 32494877.48571319878, 6046877.768960709684, 32494837.28901730105, 6046923.46963045001, 32494832.74034240097, 6046929.060290849768, 32494819.72998049855, 6046945.051623810083, 32494811.69136070088, 6046958.16919447016, 32494802.5945844017, 6046976.153786879964, 32494772.55169799924, 6047047.455390949734, 32494719.86514930055, 6047178.000199080445, 32494703.1739689, 6047218.369418010116, 32494676.91208469868, 6047281.885990509763, 32494650.0393531993, 6047347.263761419803, 32494643.69158110023, 6047360.380292469636, 32494647.07723389938, 6047361.438862780109))
-126094933102632175|SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32513401.70741016418, 6025310.709279053845, null), null, null)
-126094944850181618|SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32493526.18633791059, 6026872.772411953658, null), null, null)
2) content of loader control file for loading only polygons (works fine)
LOAD DATA
infile poly.csv
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE PNT
APPEND
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ',',
SDO_SRID INTEGER EXTERNAL TERMINATED BY ',',
SDO_POINT FILLER TERMINATED BY ', SDO_ELEM_INFO_ARRAY(',
SDO_ELEM_INFO VARRAY TERMINATED BY '), SDO_ORDINATE_ARRAY('
(elements FLOAT EXTERNAL),
SDO_ORDINATES VARRAY TERMINATED BY '))'
(ordinates FLOAT EXTERNAL)
)
)
3) sql loader control file for loading points only
LOAD DATA
infile point.csv
APPEND
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE PNT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
(
X FLOAT EXTERNAL TERMINATED BY ', ',
Y FLOAT EXTERNAL TERMINATED BY ', null'
)
)
)
|
|
|
|
| Re: Load WKT using sql*loader [message #666881 is a reply to message #666877] |
Thu, 30 November 2017 03:53 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> HOST TYPE test.ctl
LOAD DATA
infile point.csv
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE PNT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
(
X FLOAT EXTERNAL TERMINATED BY ', ',
Y FLOAT EXTERNAL TERMINATED BY ', null'
)
)
)
SCOTT@orcl_12.1.0.2.0> CREATE TABLE pnt
2 (id NUMBER,
3 geometrie MDSYS.SDO_GEOMETRY)
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Nov 30 01:51:12 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Table PNT:
2 Rows successfully loaded.
Check the log file:
test.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM pnt
2 /
ID
------------------------------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
-126094933102632175
SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32513401.70741016418, 6025310.7092790538
45, NULL), NULL, NULL)
-126094944850181618
SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32493526.18633791059, 6026872.7724119536
58, NULL), NULL, NULL)
2 rows selected.
|
|
|
|
| Re: Load WKT using sql*loader [message #666883 is a reply to message #666881] |
Thu, 30 November 2017 04:11 |
|
frank.reimann
Messages: 8
Registered: November 2017
|
Junior Member |
|
|
Thanks for response:
my ctl file:
oracle@sv111:~/importST> cat point_3.ctl
LOAD DATA
infile point_1.csv
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE PNT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
(
X FLOAT EXTERNAL TERMINATED BY ', ',
Y FLOAT EXTERNAL TERMINATED BY ', null'
)
)
)
Content of the data file:
oracle@sv111:~/importST> cat point_1.csv
-126094933102632175|SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32513401.70741016418, 6025310.709279053845, null), null, null)
-126094944850181618|SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32493526.18633791059, 6026872.772411953658, null), null, null)
oracle@sv111:~/importST>
I just tried:
sqlldr user/pass control=point_3.ctl
and this is what I received in the point_3.log with ora-01722 :/
oracle@sv111:~/importST> cat point_3.log
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Nov 30 11:02:51 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Control File: point_3.ctl
Data File: point_1.csv
Bad File: point_1.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: 1:1 != 0X20(character ' '), in next physical record
Path used: Conventional
Table PNT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * CHARACTER
Terminator string : '|SDO_GEOMETRY('
GEOMETRIE DERIVED * COLUMN OBJECT
*** Fields in GEOMETRIE
SDO_GTYPE NEXT * CHARACTER
Terminator string : ', '
SDO_SRID NEXT * CHARACTER
Terminator string : ', SDO_POINT_TYPE('
SDO_POINT DERIVED * COLUMN OBJECT
*** Fields in GEOMETRIE.SDO_POINT
X NEXT * CHARACTER
Terminator string : ', '
Y NEXT * CHARACTER
Terminator string : ', null'
*** End of fields in GEOMETRIE.SDO_POINT
*** End of fields in GEOMETRIE
Record 1: Rejected - Error on table PNT, column GEOMETRIE.SDO_POINT.X.
ORA-01722: Ungultige Zahl
Record 2: Rejected - Error on table PNT, column GEOMETRIE.SDO_POINT.X.
ORA-01722: Ungultige Zahl
Table PNT:
0 Rows successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 82560 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 2
Total logical records discarded: 0
Run began on Thu Nov 30 11:02:51 2017
Run ended on Thu Nov 30 11:02:51 2017
Elapsed time was: 00:00:00.06
CPU time was: 00:00:00.03
oracle@sv111:~/importST>
What did I wrong?
Thx Frank
|
|
|
|
| Re: Load WKT using sql*loader [message #666884 is a reply to message #666883] |
Thu, 30 November 2017 06:42 |
|
frank.reimann
Messages: 8
Registered: November 2017
|
Junior Member |
|
|
some replacements solve the issue, see content of control file:
LOAD DATA
infile point_1.csv
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE PNT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
(
X TERMINATED BY ', ' "to_number(replace(:GEOMETRIE.SDO_POINT.X,'.',','))",
Y TERMINATED BY ', null' "to_number(replace(:GEOMETRIE.SDO_POINT.Y,'.',','))"
)
)
)
But this is not an explanation, why the loader handles point data and polygons defferently. I was expecting, that the formatting of ordinates (in case of polygons) and the point data would be treated in the same manner. In both cases we have floats seperated by "." but ...
Next I try to write a control file handling both geometries, points and polygons in a single load run.
thx and bye
Frank
|
|
|
|
| Re: Load WKT using sql*loader [message #666909 is a reply to message #666884] |
Thu, 30 November 2017 14:56 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Apparently your nls_numeric_characters in your data and your database are different and you found a workaround using to_number and replace.
If you want to use one control file to load points and polygons, you can use two separate INTO and WHEN clauses, as shown below. I used GEOMETRIE.SDO_GTYPE='2003' and GEOMETRIE.SDO_GTYPE='2001'. If you have more sdo_gtype's than the 2001 and 2003, then you will need to modify that. Since my system has different nls_numeric_characters than yours, you will also need to add your to_number and replace. I used your example data for the example_data.csv. Note that you have to reset the position to 1 for the first field after each INTO and WHEN clause after the first one. I also allowed for a Z point.
SCOTT@orcl_12.1.0.2.0> HOST TYPE test.ctl
LOAD DATA
infile example_data.csv
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE test
WHEN GEOMETRIE.SDO_GTYPE='2003'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ',',
SDO_SRID INTEGER EXTERNAL TERMINATED BY ',',
SDO_POINT FILLER TERMINATED BY ', SDO_ELEM_INFO_ARRAY(',
SDO_ELEM_INFO VARRAY TERMINATED BY '), SDO_ORDINATE_ARRAY('
(elements FLOAT EXTERNAL),
SDO_ORDINATES VARRAY TERMINATED BY '))'
(ordinates FLOAT EXTERNAL)
)
)
INTO TABLE test
WHEN GEOMETRIE.SDO_GTYPE='2001'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID POSITION(1) INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
(
X TERMINATED BY ', ',
Y TERMINATED BY ', ',
Z TERMINATED BY ')' NULLIF GEOMETRIE.SDO_POINT.Z='null'
)
)
)
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test
2 (id NUMBER,
3 geometrie MDSYS.SDO_GEOMETRY)
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Nov 30 12:45:55 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 3
Commit point reached - logical record count 4
Table TEST:
2 Rows successfully loaded.
Table TEST:
2 Rows successfully loaded.
Check the log file:
test.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test
2 /
ID
------------------------------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
-126094947963131504
SDO_GEOMETRY(2003, 4647, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(32512555.70613779873, 6025825.636290729977, 32512550.83485529944, 6025818.708
380140364, 32512545.65460190177, 6025807.123500409536, 32512543.01040489972, 602
5796.54514419008, 32512541.95162370056, 6025786.892361969687, 32512544.332388300
45, 6025769.436931439675, 32512551.20756930113, 6025705.831552520394, 32512562.5
785018988, 6025613.928488469683, 32512574.47800410166, 6025510.389547759667, 325
12586.28864509985, 6025412.926016679965, 32512593.51787060127, 6025359.377372499
555, 32512595.10525650159, 6025358.848599120043, 32512596.95658250153, 6025360.4
35900770128, 32512634.33800140023, 6025393.832178629935, 32512652.23450290039, 6
025409.891077009961, 32512678.4189410992, 6025430.519918469712, 32512685.5603117
0115, 6025435.014912510291, 32512698.5194851011, 6025440.30552764982, 32512745.3
3299810067, 6025456.701080350205, 32512763.58318740129, 6025463.445011160336, 32
512819.1908344999, 6025482.257517449558, 32512828.11704799905, 6025484.205474649
556, 32512893.97324709967, 6025496.635563739575, 32512900.05696219951, 6025498.0
89879569598, 32512909.31347610056, 6025499.809057270177, 32512949.51546369866, 6
025506.684701099992, 32513032.29969409853, 6025522.552019570023, 32513033.093422
09995, 6025523.873427590355, 32513032.82858930156, 6025527.31190478988, 32513012
.73076440021, 6025660.339014720172, 32512993.95423660055, 6025783.051627789624,
32512986.28517020121, 6025836.47393022012, 32512984.96268809959, 6025837.5314649
20379, 32512954.01840750128, 6025842.027569389902, 32512940.265602801, 6025843.3
50026310422, 32512915.66795140132, 6025847.846151470207, 32512901.12153270096, 6
025850.888019329868, 32512898.21265760064, 6025851.945593480021, 32512877.318708
40117, 6025858.028224219568, 32512858.53989829868, 6025861.20111544989, 32512846
.10960549861, 6025860.673540069722, 32512839.23323610052, 6025861.732180359773,
32512826.2731859982, 6025863.318683080375, 32512787.39401879907, 6025867.2875436
89832, 32512769.14391890168, 6025865.964408989996, 32512764.01986249909, 6025865
.913511140272, 32512761.71175070107, 6025866.542271340266, 32512751.01079960167,
6025865.913713710383, 32512743.87751070037, 6025866.122733259574, 32512718.2781
5920115, 6025862.766601050273, 32512706.52661399916, 6025862.556875119917, 32512
693.51854560152, 6025862.347168579698, 32512689.0062229, 6025860.544026600197, 3
2512683.71620459855, 6025859.221686209552, 32512662.822015699, 6025853.139663719
572, 32512639.28282539919, 6025846.792803750373, 32512636.63779349998, 6025844.9
4065484032, 32512631.08388070017, 6025843.883201629855, 32512619.18233110011, 60
25840.445884689689, 32512588.23686730117, 6025829.603083129972, 32512585.3279283
0095, 6025827.35510734003, 32512581.4704034999, 6025827.939908660017, 32512579.5
0916180015, 6025828.676615930162, 32512569.19405639917, 6025827.619228740223, 32
512560.46637370065, 6025827.884241269901, 32512555.70613779873, 6025825.63629072
9977))
-126094947963131501
SDO_GEOMETRY(2003, 4647, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(32494647.07723389938, 6047361.438862780109, 32494605.25351950154, 6047464.256
969889626, 32494566.37276430055, 6047556.029602379538, 32494507.12915069982, 604
7539.896046349779, 32494535.43056929857, 6047473.248942770064, 32494572.45794180
036, 6047386.369259879924, 32494634.93126599863, 6047243.130144639872, 32494670.
85081690177, 6047160.773290139623, 32494698.35719859973, 6047095.580389400013, 3
2494726.12941139936, 6047030.916237530299, 32494730.36098229885, 6047017.4288342
1015, 32494733.00575039908, 6047004.734055100009, 32494745.16904219985, 6046971.
277592049912, 32494765.79468239844, 6046923.672004629858, 32494788.80091699958,
6046894.71200618986, 32494830.58370070159, 6046846.048484040424, 32494852.799723
2005, 6046846.048807200044, 32494873.42733690143, 6046825.155797810294, 32494881
.89153400064, 6046831.503292440437, 32494884.40214810148, 6046837.892668030225,
32494884.80108710006, 6046838.90925018955, 32494883.47932570055, 6046846.0492532
80275, 32494861.85975300148, 6046864.027479980141, 32494834.81968170032, 6046886
.513855709694, 32494826.09152489901, 6046890.216236299835, 32494830.05942770094,
6046895.769989670254, 32494858.81460030004, 6046870.828620689921, 32494885.8594
9439928, 6046847.371736220084, 32494898.02668900043, 6046858.877099599689, 32494
881.29416010156, 6046877.558092970401, 32494877.48571319878, 6046877.76896070968
4, 32494837.28901730105, 6046923.46963045001, 32494832.74034240097, 6046929.0602
90849768, 32494819.72998049855, 6046945.051623810083, 32494811.69136070088, 6046
958.16919447016, 32494802.5945844017, 6046976.153786879964, 32494772.55169799924
, 6047047.455390949734, 32494719.86514930055, 6047178.000199080445, 32494703.173
9689, 6047218.369418010116, 32494676.91208469868, 6047281.885990509763, 32494650
.0393531993, 6047347.263761419803, 32494643.69158110023, 6047360.380292469636, 3
2494647.07723389938, 6047361.438862780109))
-126094933102632175
SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32513401.70741016418, 6025310.7092790538
45, NULL), NULL, NULL)
-126094944850181618
SDO_GEOMETRY(2001, 4647, SDO_POINT_TYPE(32493526.18633791059, 6026872.7724119536
58, NULL), NULL, NULL)
4 rows selected.
|
|
|
|
| Re: Load WKT using sql*loader [message #666911 is a reply to message #666909] |
Fri, 01 December 2017 01:47 |
|
frank.reimann
Messages: 8
Registered: November 2017
|
Junior Member |
|
|
Hello Barbara,
the following control file worked for me:
LOAD DATA
infile ktr_all_awk.csv
APPEND
CONTINUEIF NEXT(1:1) != ' '
INTO TABLE PNT
WHEN GEOMETRIE.SDO_GTYPE='2003'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ',',
SDO_SRID INTEGER EXTERNAL TERMINATED BY ',',
SDO_POINT FILLER TERMINATED BY ', SDO_ELEM_INFO_ARRAY(',
SDO_ELEM_INFO VARRAY TERMINATED BY '), SDO_ORDINATE_ARRAY('
(elements FLOAT EXTERNAL),
SDO_ORDINATES VARRAY TERMINATED BY '))'
(ordinates FLOAT EXTERNAL)
)
)
INTO TABLE PNT
WHEN GEOMETRIE.SDO_GTYPE='2001'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS (
ID POSITION(1) INTEGER EXTERNAL TERMINATED BY '|SDO_GEOMETRY(',
GEOMETRIE COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL TERMINATED BY ', ',
SDO_SRID INTEGER EXTERNAL TERMINATED BY ', SDO_POINT_TYPE(',
SDO_POINT COLUMN OBJECT
(
X TERMINATED BY ', ' "to_number(replace(:GEOMETRIE.SDO_POINT.X,'.',','))",
Y TERMINATED BY ', ' "to_number(replace(:GEOMETRIE.SDO_POINT.Y,'.',','))",
Z TERMINATED BY ')' NULLIF GEOMETRIE.SDO_POINT.Z='null'
)
)
)
I agree with You, that some enviroment settings do not fit the needs, You remember the "." / "," issue... If the replacement will not be included in the control file sql loader only processes the polygon data, not any point geometry, strange. But anyway for the moment I've got the import working.
Thanks a lot and kind regards
Frank
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 02:50:11 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
