6 Replies Latest reply: Jul 12, 2013 8:17 AM by matt schell RSS

    SDO_Union Returns Null - Can Anyone Verify?

    matt schell

      Hi Spatial Experts,

       

      I've got some code that's been running sdo_geom.sdo_union for years and it seems to be unexpectedly returning NULL results.  I'm suspicious of some recent patches. We're on 11.2.0.3.

       

      Would some other folks on 11.2.0.3 be so kind as to run the SQL below and let me know what you get?  Both geometries validate for me with the specified .00000005 tolerance.

       

      Please note that the srid is intentionally NULL.  This is a technique/trick that Dan Geringer taught us to turn geodetic data, with it's lower limit .05 tolerance, into graph paper for the purposes of evaluating whether the coordinates line up.  If you'd like to weigh in on whether this trick is justifiable please do that too.

       

      Thanks

      Matt

       

      select sdo_geom.sdo_union(
      SDO_GEOMETRY( 2003, NULL,  NULL, 
         SDO_ELEM_INFO_ARRAY (  1,  1003, 1 ),
         SDO_ORDINATE_ARRAY
         (  -71.8626677830483,
            41.3108635965834,
            -71.862772,
            41.309791,
            -71.857432,
            41.306318,
            -71.856008344793,
            41.3068779738541,
            -71.833755,
            41.315631,
            -71.3814576755848,
            41.9528657989843,
            -71.381401,
            41.964799,
            -71.381501,
            41.966699,
            -71.3814702596934,
            41.9827143923185,
            -71.3814370604434,
            42.0000108695953,
            -71.381401,
            42.018798,
            -71.4144186146141,
            42.0183522075931,
            -71.4581166451263,
            42.0177622119067,
            -71.4837106505029,
            42.0174166505029,
            -71.4913674222159,
            42.0173132714208,
            -71.499905,
            42.017198,
            -71.500905,
            42.017098,
            -71.5020552773062,
            42.0170108610805,
            -71.5117025626539,
            42.0162800338128,
            -71.5246507382353,
            42.015299148575,
            -71.527306,
            42.015098,
            -71.527606,
            42.014998,
            -71.5291164669991,
            42.0149668729824,
            -71.5533625576453,
            42.0144672205631,
            -71.559439,
            42.014342,
            -71.559986284321,
            42.0143343557517,
            -71.576908,
            42.014098,
            -71.5942016093458,
            42.0136999128223,
            -71.6062561837613,
            42.0134224246813,
            -71.6614391827869,
            42.0121521494079,
            -71.6780303126541,
            42.0117702328321,
            -71.7551705643525,
            42.0099945164693,
            -71.76601,
            42.009745,
            -71.799242,
            42.008065,
            -71.7983794424545,
            41.9605785933117,
            -71.797922,
            41.935395,
            -71.7976492021458,
            41.9285555421785,
            -71.7946917826635,
            41.854408530038,
            -71.794161,
            41.841101,
            -71.794161,
            41.840141,
            -71.792786,
            41.80867,
            -71.792767,
            41.807001,
            -71.791062,
            41.770273,
            -71.7910592549218,
            41.7701826762188,
            -71.789678,
            41.724734,
            -71.7896715648332,
            41.7245691839672,
            -71.7869970249528,
            41.6560694744054,
            -71.786994,
            41.655992,
            -71.789356,
            41.59691,
            -71.7893586727423,
            41.5968521603427,
            -71.7917190617472,
            41.545772004335,
            -71.7964993738839,
            41.4423233400691,
            -71.7967394589644,
            41.4371277628393,
            -71.797235453979,
            41.426394149577,
            -71.7976736925499,
            41.4169104184967,
            -71.797683,
            41.416709,
            -71.81839,
            41.419599,
            -71.839649,
            41.412119,
            -71.842563,
            41.409855,
            -71.843472,
            41.40583,
            -71.8421876802862,
            41.3958015796247,
            -71.842131,
            41.395359,
            -71.833443,
            41.384524,
            -71.8327992762497,
            41.379731248034,
            -71.831613,
            41.370899,
            -71.8322034310941,
            41.3701885604645,
            -71.8377383924168,
            41.3635285871149,
            -71.8376954258876,
            41.3632979864021,
            -71.8359508881564,
            41.3539350787562,
            -71.8295953412471,
            41.3445440841142,
            -71.8316885115287,
            41.3422099105097,
            -71.8390128637424,
            41.3340422474403,
            -71.8605134967242,
            41.3202483178274,
            -71.862109289618,
            41.3166115880123,
            -71.8626677830483,
            41.3108635965834
         )
      ), SDO_GEOMETRY(   2003,   NULL,   NULL,
         SDO_ELEM_INFO_ARRAY   ( 1, 1003, 1 ),
         SDO_ORDINATE_ARRAY   (
            -73.727775,
            41.100696,
            -71.9603565861075,
            41.3285129899034,
            -71.9567471661891,
            41.3298707791293,
            -71.945652,
            41.337799,
            -71.9375554403829,
            41.3379372845366,
            -71.936284,
            41.337959,
            -71.923282,
            41.335113,
            -71.922092,
            41.334518,
            -71.9167101117149,
            41.332216844508,
            -71.9114582393823,
            41.33294112026,
            -71.8930858482904,
            41.3354748218679,
            -71.8863017274908,
            41.3364104070997,
            -71.8823535958622,
            41.3352152246365,
            -71.8682345726004,
            41.3309410994081,
            -71.8595697162474,
            41.3223991449254,
            -71.8605134967242,
            41.3202483178274,
            -71.8390128637424,
            41.3340422474403,
            -71.8295953412471,
            41.3445440841142,
            -71.8359508881564,
            41.3539350787562,
            -71.8377383924168,
            41.3635285871149,
            -71.8346005460792,
            41.3673042183732,
            -71.831613,
            41.370899,
            -71.8325171567796,
            41.3776307683725,
            -71.833443,
            41.384524,
            -71.8337387002957,
            41.3848927744825,
            -71.8387064264705,
            41.3910881374088,
            -71.842131,
            41.395359,
            -71.843472,
            41.40583,
            -71.8428165698118,
            41.4087322073789,
            -71.842563,
            41.409855,
            -71.839649,
            41.412119,
            -71.81839,
            41.419599,
            -71.797683,
            41.416709,
            -71.795497602244,
            41.4640022461898,
            -71.7935683158896,
            41.5057530964788,
            -71.7905084708522,
            41.5719698775019,
            -71.789356,
            41.59691,
            -71.7876366458895,
            41.6399171462982,
            -71.786994,
            41.655992,
            -71.7886475406628,
            41.6983421088834,
            -71.789678,
            41.724734,
            -71.791062,
            41.770273,
            -71.791258063576,
            41.774496473912,
            -71.7926566350564,
            41.8046235902363,
            -71.792767,
            41.807001,
            -71.792786,
            41.80867,
            -71.794161,
            41.840141,
            -71.794161,
            41.841101,
            -71.7944823560536,
            41.8491578858595,
            -71.7966877946775,
            41.9044515922139,
            -71.7978012534201,
            41.9323676992798,
            -71.797922,
            41.935395,
            -71.7988268815301,
            41.9852114703,
            -71.799242,
            42.008065,
            -71.80065,
            42.023569,
            -71.8603967710167,
            42.024098653501,
            -73.6960060114926,
            41.1154076779464,
            -73.727775,
            41.100696   )),  0.00000005) from dual
      
        • 1. Re: SDO_Union Returns Null - Can Anyone Verify?
          navaneet

          sql ouput Version  11.2.0.1.0

          SDO_GEOM.SDO_UNION(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-71.8626677830483,41.3108635965834,-71.862772,41.309791,-71.857432,41.306318,-71.856008344793,41.3068779738541,-71.833755,41.315631,-71.3814576755848,41.952865(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

          SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(-71.860513, 41.3202483, -71.862109, 41.3166116, -71.862668, 41.3108636, -71.862772, 41.309791, -71.857432, 41.306318, -71.856008, 41.306878, -71.833755, 41.315631, -71.381458, 41.9528658, -71.381401, 41.964799, -71.381501, 41.966699, -71.38147, 41.9827144, -71.381437, 42.0000109, -71.381401, 42.018798, -71.414419, 42.0183522, -71.458117, 42.0177622, -71.483711, 42.0174167, -71.491367, 42.0173133, -71.499905, 42.017198, -71.500905, 42.017098, -71.502055, 42.0170109, -71.511703, 42.01628, -71.524651, 42.0152991, -71.527306, 42.015098, -71.527606, 42.014998, -71.529116, 42.0149669, -71.553363, 42.0144672, -71.559439, 42.014342, -71.559986, 42.0143344, -71.576908, 42.014098, -71.594202, 42.0136999, -71.606256, 42.0134224, -71.661439, 42.0121521, -71.67803, 42.0117702, -71.755171, 42.0099945, -71.76601, 42.009745, -71.799242, 42.008065, -71.80065, 42.023569, -71.860397, 42.0240987, -73.696006, 41.1154077, -73

          .727775, 41.100696, -71.960357, 41.328513, -71.956747, 41.3298708, -71.945652, 41.337799, -71.937555, 41.3379373, -71.936284, 41.337959, -71.923282, 41.335113, -71.922092, 41.334518, -71.91671, 41.3322168, -71.911458, 41.3329411, -71.893086, 41.3354748, -71.886302, 41.3364104, -71.882354, 41.3352152, -71.868235, 41.3309411, -71.85957, 41.3223991, -71.860513, 41.3202483))

           

          best

          Navaneet

          • 2. Re: SDO_Union Returns Null - Can Anyone Verify?
            Stefan Jager

            On

            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
            

            I get

            MDSYS.SDO_GEOMETRY(2003,
                                                            NULL,
                                                            NULL,
                                                            MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
                                                            MDSYS.SDO_ORDINATE_ARRAY(-71.8605134967242,41.3202483178274,-71.862109289618,41.3166115880123,-71.8626677830483,41.3108635965834,-71.862772,41.309791,-71.857432,41.306318,-71.856008344793,41.3068779738541,-71.833755,41.315631,-71.3814576755848,41.9528657989843,-71.381401,41.964799,-71.381501,41.966699,-71.3814702596934,41.9827143923185,-71.3814370604434,42.0000108695953,-71.381401,42.018798,-71.4144186146141,42.0183522075931,-71.4581166451263,42.0177622119067,-71.4837106505029,42.0174166505029,-71.4913674222159,42.0173132714208,-71.499905,42.017198,-71.500905,42.017098,-71.5020552773062,42.0170108610805,-71.5117025626539,42.0162800338128,-71.5246507382353,42.015299148575,-71.527306,42.015098,-71.527606,42.014998,-71.5291164669991,42.0149668729824,-71.5533625576453,42.0144672205631,-71.559439,42.014342,-71.559986284321,42.0143343557517,-71.576908,42.014098,-71.5942016093458,42.0136999128223,-71.6062561837613,42.0134224246813,-71.6614391827869,42.0121521494079,-71.6780303126541,42.0117702328321,-71.7551705643525,42.0099945164693,-71.76601,42.009745,-71.799242,42.008065,-71.80065,42.023569,-71.8603967710167,42.024098653501,-73.6960060114926,41.1154076779464,-73.727775,41.100696,-71.9603565861075,41.3285129899034,-71.9567471661891,41.3298707791293,-71.945652,41.337799,-71.9375554403829,41.3379372845366,-71.936284,41.337959,-71.923282,41.335113,-71.922092,41.334518,-71.9167101117149,41.332216844508,-71.9114582393823,41.33294112026,-71.8930858482904,41.3354748218679,-71.8863017274908,41.3364104070997,-71.8823535958622,41.3352152246365,-71.8682345726004,41.3309410994081,-71.8595697162474,41.3223991449254,-71.8605134967242,41.3202483178274))MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-71.8605134967242,41.3202483178274,-71.862109289618,41.3166115880123,-71.8626677830483,41.3108635965834,-71.862772,41.309791,-71.857432,41.306318,-71.856008344793,41.3068779738541,-71.833755,41.315631,-71.3814576755848,41.9528657989843,-71.381401,41.964799,-71.381501,41.966699,-71.3814702596934,41.9827143923185,-71.3814370604434,42.0000108695953,-71.381401,42.018798,-71.4144186146141,42.0183522075931,-71.4581166451263,42.0177622119067,-71.4837106505029,42.0174166505029,-71.4913674222159,42.0173132714208,-71.499905,42.017198,-71.500905,42.017098,-71.5020552773062,42.0170108610805,-71.5117025626539,42.0162800338128,-71.5246507382353,42.015299148575,-71.527306,42.015098,-71.527606,42.014998,-71.5291164669991,42.0149668729824,-71.5533625576453,42.0144672205631,-71.559439,42.014342,-71.559986284321,42.0143343557517,-71.576908,42.014098,-71.5942016093458,42.0136999128223,-71.6062561837613,42.0134224246813,-71.6614391827869,42.0121521494079,-71.6780303126541,42.0117702328321,-71.7551705643525,42.0099945164693,-71.76601,42.009745,-71.799242,42.008065,-71.80065,42.023569,-71.8603967710167,42.024098653501,-73.6960060114926,41.1154076779464,-73.727775,41.100696,-71.9603565861075,41.3285129899034,-71.9567471661891,41.3298707791293,-71.945652,41.337799,-71.9375554403829,41.3379372845366,-71.936284,41.337959,-71.923282,41.335113,-71.922092,41.334518,-71.9167101117149,41.332216844508,-71.9114582393823,41.33294112026,-71.8930858482904,41.3354748218679,-71.8863017274908,41.3364104070997,-71.8823535958622,41.3352152246365,-71.8682345726004,41.3309410994081,-71.8595697162474,41.3223991449254,-71.8605134967242,41.3202483178274))
            

             

            So not null....

            Also not 11.2.0.3

             

            Regards,

            Stefan

            • 3. Re: SDO_Union Returns Null - Can Anyone Verify?
              B Hall

              Here is what I get with 11.2.0.3.5 on AIX:

               

              SDO_GEOM.SDO_UNION(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO
              --------------------------------------------------------------------------------
              SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
              AY(-73.6960060114926, 41.1154076779464, -73.727775, 41.100696, -71.9603565861075
              , 41.3285129899034, -71.9567471661891, 41.3298707791293, -71.945652, 41.337799,
              -71.9375554403829, 41.3379372845366, -71.936284, 41.337959, -71.923282, 41.33511
              3, -71.922092, 41.334518, -71.9167101117149, 41.332216844508, -71.9114582393823,
               41.33294112026, -71.8930858482904, 41.3354748218679, -71.8863017274908, 41.3364
              104070997, -71.8823535958622, 41.3352152246365, -71.8682345726004, 41.3309410994
              081, -71.8595697162474, 41.3223991449254, -71.8605134967242, 41.3202483178274, -
              71.862109289618, 41.3166115880123, -71.8626677830483, 41.3108635965834, -71.8627
              72, 41.309791, -71.857432, 41.306318, -71.856008344793, 41.3068779738541, -71.83
              3755, 41.315631, -71.3814576755848, 41.9528657989843, -71.381401, 41.964799, -71
              .381501, 41.966699, -71.3814702596934, 41.9827143923185, -71.3814370604434, 42.0
              000108695953, -71.381401, 42.018798, -71.4144186146141, 42.0183522075931, -71.45
              81166451263, 42.0177622119067, -71.4837106505029, 42.0174166505029, -71.49136742
              22159, 42.0173132714208, -71.499905, 42.017198, -71.500905, 42.017098, -71.50205
              52773062, 42.0170108610805, -71.5117025626539, 42.0162800338128, -71.52465073823
              53, 42.015299148575, -71.527306, 42.015098, -71.527606, 42.014998, -71.529116466
              9991, 42.0149668729824, -71.5533625576453, 42.0144672205631, -71.559439, 42.0143
              42, -71.559986284321, 42.0143343557517, -71.576908, 42.014098, -71.5942016093458
              , 42.0136999128223, -71.6062561837613, 42.0134224246813, -71.6614391827869, 42.0
              121521494079, -71.6780303126541, 42.0117702328321, -71.7551705643525, 42.0099945
              164693, -71.76601, 42.009745, -71.799242, 42.008065, -71.80065, 42.023569, -71.8
              603967710167, 42.024098653501, -73.6960060114926, 41.1154076779464))
              
              • 4. Re: SDO_Union Returns Null - Can Anyone Verify?
                matt schell

                Thanks everyone!  I'll post a followup if we figure out what's going on here, but it looks like this is something peculiar to our set up (11.2.0.3 on Exadata with ? patches) and none of you are gonna care.

                 

                Btw, we only get to award 2 "helpful" answer points now?  I thought it was 5.  Sorry B Hall .

                 

                Thanks again

                Matt

                • 5. Re: SDO_Union Returns Null - Can Anyone Verify?
                  B Hall

                  Actually... Exadata is something we are looking at, so I am interested.

                  • 6. Re: SDO_Union Returns Null - Can Anyone Verify?
                    matt schell

                    Hi again,

                     

                    Our in-house Oracle patch jugglers installed merge patch 16442919 and this problem was resolved.  All evidence suggests that this was a patch mismatch and nothing more.  Nothing to see here.

                     

                    Thanks again everyone for the help.

                     

                    Matt