Search code examples
sqloraclespatialoracle-spatial

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

I have a table NEW_TABLE ID and ORA_GEOMETRY

where id = 1 then 

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 its draw one polygon

where id = 2 then 

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 this one's turn will draw one polygon.

I want to combine these two lines into one, so that if I look at it I show two polygons where id = 3

Solution

  • Assume your table is like this:

    create table new_table (
  id number,
  ora_geometry sdo_geometry
);

    and populated like this

    insert into 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 into 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 a new polygon that is a union of the first two, do like this:

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

    Note that this does not necessarily give the same result as the manual union in the 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 is because the union aggregation does not define the order in which it assembles the polygons. The order of the polygons in a multi-polygon is immaterial.