sql - how to update or insert MDSYS.SDO_GEOMETRY more than one polygon -


i have table new_table id , ora_geometry

where id = 1 

ora_geometry = mdsys.sdo_geometry(2003, 32638, null, mdsys.sdo_elem_info_array(1, 1003, 1), mdsys.sdo_ordinate_array(462897.8239, 4551252.4903, 462869.6794, 4551237.1391, 462821.0656, 4551321.5734, 462770.4983, 4551381.3337, 462764.7756, 4551388.0969, 462754.1560, 4551377.4776, 462741.7481, 4551365.07, 462741.7481, 4551363.7611, 462741.7474, 4551329.2490, 462764.7750, 4551290.8697, 462808.2715, 4551232.0215, 462821.0646, 4551201.3181, 462803.1544, 4551183.4076, 462757.10, 4551173.1735, 462747.3533, 4551152.4610, 462764.0828, 4551144.0964, 462767.3352, 4551142.4702, 462780.1283, 4551122.0012, 462785.2455, 4551093.8564, 462785.2455, 4551032.4494, 462790.3627, 4550930.1047, 462823.6248, 4550861.0220, 462850.7758, 4550815.7704, 462849.2111, 4550881.4909, 462826.1833, 4551001.7460, 462826.1833, 4551065.7114, 462826.1833, 4551132.2357, 462844.0938, 4551145.0287, 462854.3284, 4551083.6219, 462867.1215, 4550983.8357, 462879.9146, 4550863.5805, 462885.0318, 4550812.4081, 462882.4728, 4550727.9744, 462869.6799, 4550633.3048, 462844.0937, 4550513.0495, 462795.4799, 4550446.5256, 462770.4995, 4550405.3075, 462775.6698, 4550405.3077, 462795.8360, 4550409.5531, 462802.7176, 4550412.9935, 462812.8183, 4550418.0442, 462829.8008, 4550425.4738, 462842.5375, 4550429.7193, 462853.1510, 4550429.7193, 462864.8268, 4550426.5353, 462879.6863, 4550415.9213, 462886.6694, 4550410.2476, 462946.4704, 4550462.0752, 462920.8524, 4550490.0220, 462915.7352, 4550538.6358, 462925.9698, 4550561.6634, 462959.2317, 4550561.6634, 462983.6238, 4550561.6634, 462972.0247, 4550648.6564, 462954.1144, 4550707.5047, 462931.0868, 4550776.5875, 462918.2937,  4550845.6702, 462897.8247, 4550978.7186, 462897.8247, 4551068.2701, 462897.8247, 4551175.7321, 462897.8239, 4551252.4903))

and draw 1 polygon

where id = 2 

ora_geometry = mdsys.sdo_geometry(2003, 32638, null, mdsys.sdo_elem_info_array(1, 1003, 1), mdsys.sdo_ordinate_array(511185.4848, 4522136.2067, 511182.8401, 4522157.3649, 511174.9057, 4522175.8784, 511153.7474,  4522183.8128, 511145.8131, 4522204.9710, 511151.1026, 4522228.7741, 511156.3922, 4522265.8010, 511161.6818, 4522318.6967, 511160.8519, 4522347.7646, 511140.5235, 4522337.2102, 511118.7925, 4522328.3526,  511098.2070, 4522310.7624, 511089.6745, 4522286.2932, 511086.4391, 4522234.5278, 511084.9831, 4522191.7471, 511079.9685, 4522163.3504, 511057.3211, 4522130.9970, 511037.9091, 4522127.7617, 511005.5557, 4522134.2324,  510957.0257, 4522143.9384, 510934.2304, 4522157.3649, 510898.7896, 4522166.5858, 510859.9656, 4522173.0564, 510831.0838, 4522162.6545, 510799.3465, 4522181.1680, 510772.8986, 4522178.5232, 510759.6747, 4522162.6545,  510743.8060, 4522152.0754, 510725.2925, 4522152.0754, 510709.4238, 4522146.7858, 510690.9103, 4522146.7858, 510704.0644, 4522097.1338, 510691.7281, 4522059.8196, 510675.0416, 4522011.9018, 510643.3042, 4521966.9405,  510630.0803, 4521943.1374, 510619.5012, 4521911.4001, 510659.1729, 4521935.2031, 510693.5551000010, 4521956.3614, 510735.8716, 4521969.5853, 510786.1225, 4521982.8092, 510839.0182, 4522003.9675, 510899.8482, 4522030.4153,  510958.0335, 4522046.2840, 511002.9948, 4522046.2840, 511066.4696, 4522046.2840, 511106.1413, 4522059.5079, 511143.1683, 4522085.9558, 511185.4848, 4522136.2067))

and one's turn draw 1 polygon.

i want combine these 2 lines one, if @ show 2 polygons id = 3

assume table this:

create table new_table (   id number,   ora_geometry sdo_geometry );

and populated this

insert new_table (id,ora_geometry) values (   1,   sdo_geometry(2003, 32638, null,      sdo_elem_info_array(1, 1003, 1),      sdo_ordinate_array(       462897.8239, 4551252.4903, 462869.6794, 4551237.1391, 462821.0656, 4551321.5734, 462770.4983, 4551381.3337, 462764.7756, 4551388.0969, 462754.1560, 4551377.4776, 462741.7481, 4551365.07, 462741.7481, 4551363.7611, 462741.7474, 4551329.2490, 462764.7750, 4551290.8697, 462808.2715, 4551232.0215, 462821.0646, 4551201.3181, 462803.1544, 4551183.4076, 462757.10, 4551173.1735, 462747.3533, 4551152.4610, 462764.0828, 4551144.0964, 462767.3352, 4551142.4702, 462780.1283, 4551122.0012, 462785.2455, 4551093.8564, 462785.2455, 4551032.4494, 462790.3627, 4550930.1047, 462823.6248, 4550861.0220, 462850.7758, 4550815.7704, 462849.2111, 4550881.4909,        462826.1833, 4551001.7460, 462826.1833, 4551065.7114, 462826.1833, 4551132.2357, 462844.0938, 4551145.0287, 462854.3284, 4551083.6219, 462867.1215, 4550983.8357, 462879.9146, 4550863.5805, 462885.0318, 4550812.4081, 462882.4728, 4550727.9744, 462869.6799, 4550633.3048, 462844.0937, 4550513.0495, 462795.4799, 4550446.5256, 462770.4995, 4550405.3075, 462775.6698, 4550405.3077, 462795.8360, 4550409.5531, 462802.7176, 4550412.9935, 462812.8183, 4550418.0442, 462829.8008, 4550425.4738, 462842.5375, 4550429.7193, 462853.1510, 4550429.7193, 462864.8268, 4550426.5353, 462879.6863, 4550415.9213, 462886.6694, 4550410.2476, 462946.4704, 4550462.0752, 462920.8524, 4550490.0220, 462915.7352, 4550538.6358, 462925.9698, 4550561.6634, 462959.2317, 4550561.6634, 462983.6238, 4550561.6634, 462972.0247, 4550648.6564, 462954.1144, 4550707.5047, 462931.0868, 4550776.5875, 462918.2937, 4550845.6702, 462897.8247, 4550978.7186, 462897.8247, 4551068.2701, 462897.8247, 4551175.7321, 462897.8239, 4551252.4903     )   )  );  insert new_table (id,ora_geometry) values (   2,   sdo_geometry(     2003, 32638, null,      sdo_elem_info_array(1, 1003, 1),      sdo_ordinate_array(       511185.4848, 4522136.2067, 511182.8401, 4522157.3649, 511174.9057, 4522175.8784, 511153.7474, 4522183.8128, 511145.8131, 4522204.9710, 511151.1026, 4522228.7741, 511156.3922, 4522265.8010, 511161.6818, 4522318.6967,        511160.8519, 4522347.7646, 511140.5235, 4522337.2102, 511118.7925, 4522328.3526, 511098.2070, 4522310.7624, 511089.6745, 4522286.2932, 511086.4391, 4522234.5278, 511084.9831, 4522191.7471, 511079.9685, 4522163.3504,        511057.3211, 4522130.9970, 511037.9091, 4522127.7617, 511005.5557, 4522134.2324, 510957.0257, 4522143.9384, 510934.2304, 4522157.3649, 510898.7896, 4522166.5858, 510859.9656, 4522173.0564, 510831.0838, 4522162.6545,        510799.3465, 4522181.1680, 510772.8986, 4522178.5232, 510759.6747, 4522162.6545, 510743.8060, 4522152.0754, 510725.2925, 4522152.0754, 510709.4238, 4522146.7858, 510690.9103, 4522146.7858, 510704.0644, 4522097.1338,        510691.7281, 4522059.8196, 510675.0416, 4522011.9018, 510643.3042, 4521966.9405, 510630.0803, 4521943.1374, 510619.5012, 4521911.4001, 510659.1729, 4521935.2031, 510693.5551000010, 4521956.3614, 510735.8716, 4521969.5853,        510786.1225, 4521982.8092, 510839.0182, 4522003.9675, 510899.8482, 4522030.4153, 510958.0335, 4522046.2840, 511002.9948, 4522046.2840, 511066.4696, 4522046.2840, 511106.1413, 4522059.5079, 511143.1683, 4522085.9558, 511185.4848, 4522136.2067     )   ) ); commit;

to insert new polygon union of first two, this:

insert new_table (id, ora_geometry) select 3, sdo_aggr_union (sdoaggrtype(ora_geometry, 0.0005)) new_table  id in (1,2); commit;

note not give same result manual union in first answer:

sdo_geometry(   2007, 32638, null,    sdo_elem_info_array(1, 1003, 1, 99, 1003, 1),    sdo_ordinate_array(     511185.485, 4522136.21, 511182.84, 4522157.36, 511174.906, 4522175.88, 511153.747, 4522183.81, 511145.813, 4522204.97, 511151.103, 4522228.77, 511156.392, 4522265.8, 511161.682, 4522318.7, 511160.852, 4522347.76,      511140.524, 4522337.21, 511118.793, 4522328.35, 511098.207, 4522310.76, 511089.675, 4522286.29, 511086.439, 4522234.53, 511084.983, 4522191.75, 511079.969, 4522163.35, 511057.321, 4522131, 511037.909, 4522127.76,      511005.556, 4522134.23, 510957.026, 4522143.94, 510934.23, 4522157.36, 510898.79, 4522166.59, 510859.966, 4522173.06, 510831.084, 4522162.65, 510799.347, 4522181.17, 510772.899, 4522178.52, 510759.675, 4522162.65,      510743.806, 4522152.08, 510725.293, 4522152.08, 510709.424, 4522146.79, 510690.91, 4522146.79, 510704.064, 4522097.13, 510691.728, 4522059.82, 510675.042, 4522011.9, 510643.304, 4521966.94, 510630.08, 4521943.14,      510619.501, 4521911.4, 510659.173, 4521935.2, 510693.555, 4521956.36, 510735.872, 4521969.59, 510786.123, 4521982.81, 510839.018, 4522003.97, 510899.848, 4522030.42, 510958.034, 4522046.28, 511002.995, 4522046.28,      511066.47, 4522046.28, 511106.141, 4522059.51, 511143.168, 4522085.96, 511185.485, 4522136.21,      462897.824, 4551252.49, 462869.679, 4551237.14, 462821.066, 4551321.57, 462770.498,4551381.33, 462764.776, 4551388.1, 462754.156, 4551377.48, 462741.748, 4551365.07, 462741.748, 4551363.76, 462741.747, 4551329.25,      462764.775, 4551290.87, 462808.272, 4551232.02, 462821.065, 4551201.32, 462803.154, 4551183.41, 462757.1,4551173.17, 462747.353, 4551152.46, 462764.083, 4551144.1, 462767.335, 4551142.47, 462780.128, 4551122,      462785.246, 4551093.86, 462785.246, 4551032.45, 462790.363, 4550930.1, 462823.625, 4550861.02, 462850.776, 4550815.77, 462849.211, 4550881.49, 462826.183, 4551001.75, 462826.183, 4551065.71, 462826.183, 4551132.24,      462844.094, 4551145.03, 462854.328, 4551083.62, 462867.122, 4550983.84, 462879.915, 4550863.58, 462885.032, 4550812.41, 462882.473, 4550727.97, 462869.68,4550633.3, 462844.094, 4550513.05, 462795.48, 4550446.53,      462770.5, 4550405.31, 462775.67, 4550405.31, 462795.836, 4550409.55, 462802.718, 4550412.99, 462812.818, 4550418.04, 462829.801, 4550425.47, 462842.538, 4550429.72, 462853.151, 4550429.72, 462864.827, 4550426.54,      462879.686, 4550415.92, 462886.669, 4550410.25, 462946.47, 4550462.08, 462920.852, 4550490.02, 462915.735, 4550538.64, 462925.97, 4550561.66, 462959.232, 4550561.66, 462983.624, 4550561.66, 462972.025, 4550648.66,      462954.114, 4550707.5, 462931.087, 4550776.59, 462918.294, 4550845.67, 462897.825, 4550978.72, 462897.825, 4551068.27, 462897.825, 4551175.73, 462897.824, 4551252.49   ) )

that because union aggregation not define order in assembles polygons. order of polygons in multi-polygon immaterial.


Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more