13 Replies Latest reply: Jul 22, 2014 1:08 PM by Bkazar-Oracle RSS

    SDO_UTIL.APPEND strips away LRS dimensions

    Paul Dziemiela

      Hi folks,

       

      Checking over the forum it seems I did note this issue in passing a year ago

      https://community.oracle.com/message/11033886#11033886

      But it came out to bite me today.  It does seem true that I flog poor old SDO_UTIL.APPEND every-so-often and perhaps I should find other interests.

       

      I am thinking this is probably a "feature" and a small blurb should be added to the documentation for the function.  SDO_UTIL.APPEND simply says of itself, "Appends one geometry to another geometry to create a new geometry."

       

      There is no mention of what types of geometries you should or should not feed to it.

       

      However feeding LRS geometries to SDO_UTIL.APPEND is a bad idea (see below) as the function destroys the LRS dimension from the SDO_GTYPE producing a 3D geometry.  I guess that I should be using SDO_LRS.CONCATENATE_GEOM_SEGMENTS though I am not interested in this case in merging the geometries, I just want to house them together in a multi-geometry.  I think this is what SDO_UTIL.APPEND was meant for.

       

      Assuming this is just a feature, perhaps Oracle could add a note on the SDO_UTIL.APPEND page that the function is not valid for LRS geometries?  Or is this a bug? 

       

      Cheers,

      Paul

       

      ON 11GR2:


      SELECT

      SDO_UTIL.APPEND(

          SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(1,1,1, 2,2,2))

         ,SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(3,3,3, 4,4,4))

      )

      FROM dual;

       

      RESULT:

      SDO_GEOMETRY(3006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1),SDO_ORDINATE_ARRAY(1,1,1,2,2,2,3,3,3,4,4,4))

        • 1. Re: SDO_UTIL.APPEND strips away LRS dimensions
          B Hall

          Paul,

           

          I would say the documentation should say it is not for LRS, since simply appending two LRS linestrings together makes no sense whatsoever.

           

          Bryan

          • 2. Re: SDO_UTIL.APPEND strips away LRS dimensions
            Paul Dziemiela

            Hi Bryan,

             

            I am leaning your way, I just think Oracle is not doing a great job here with saying what works with what.

             

            What is so nonsensical about this geometry?

             

            SELECT

            SDO_LRS.VALIDATE_LRS_GEOMETRY(

               SDO_GEOMETRY(3306,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1),SDO_ORDINATE_ARRAY(1,1,1,2,2,2,3,3,3,4,4,4))

            )

            FROM

            dual;


            RESULT ON 11gR2: TRUE

             

            Seems a person might come along and want to build that from two separate LRS line strings just as I did.  It would be nice if the docs should just say don't do that using APPEND.

             

            I think there are some larger issues here that we might be skirting about.  I rather think Oracle Spatial back in the day was designed with a kind of notion that there was one universal geometry type no matter the dimensions, geodetic, gtype etc.  So if you want to do thing X to geometry Y, you just use one function Z.  But looking at all the 3D functions I think it safe to say that 2D and 3D have gone (mostly?) their separate ways and anyone working in 3D is probably spending a bit of time puzzling over when to use the "generic" function and when to search for the 3D specific function.  LRS has kind of - sort of - gone that route but not totally.  LRS users probably want to use LRS_INTERSECTION (ugh) and make sure you use SDO_LRS.CONCATENATE_GEOM_SEGMENTS, not SDO_UTIL.CONCAT_LINES and again make sure you don't use SDO_UTIL.APPEND ever, etc.  Its a little confusing is all I am saying and often the response of the spatial functions is to return null or an messed-up result rather than a hard error.  Perhaps SDO_UTIL.APPEND should abend with an error code when it encounters LRS inputs.  That would probably be less of a gotch-cha. 

             

            Thanks for the feedback,

             

            Paul

            • 3. Re: SDO_UTIL.APPEND strips away LRS dimensions
              B Hall

              Paul,

               

              My take on append, is that it will just stuff the first and the second geometries together with no logic applied. So although your example result geometry just happens to work out, there is no logic given to the measure portion of the LRS. The measure can be a distance, say meters for Lat/Lon - or it can be some arbitrary scale such as the percentage of a city block going from 0 to 1. Since append would have no knowledge of how your two line stings are to be stuck together, you could easily wind up with:

               

              SELECT

              SDO_UTIL.APPEND(  

                  SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(3,3,3, 4,4,4)),

                  SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(1,1,1, 2,2,2))

              )

              FROM dual;

               

              RESULT:

              SDO_GEOMETRY(3006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1),SDO_ORDINATE_ARRAY(3,3,3,4,4,4,1,1,1,2,2,2))


              ...that of course is completely invalid as the measure is not progressing in order:


              SELECT

              SDO_LRS.VALIDATE_LRS_GEOMETRY(

                 SDO_GEOMETRY(3306,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1),SDO_ORDINATE_ARRAY(3,3,3,4,4,4,1,1,1,2,2,2))

              )

              FROM

              dual;

               

               

               

              RESULT ON 11.2.0.4.2: 13335

               

              ORA-13335 LRS segment is not defined

              Cause: The given start or end measures are not defined, or some assigned measures in between are not in an ascending order.

              Action: An LRS geometric segment is defined if its start and end measure are assigned (non-null). Any other measures assigned on the segment must be in an ascending order.

               

              Bryan

               

              PS - Yes, I agree on the error message - or more so, on checking for valid inputs to start with. However, I do get the idea that doing so on something that is called millions or billions of times in a row, can cause it to run slightly slower. All in all, I'd personally rather have validity checks that a routine that runs 2% faster and can give me invalid answers.

              • 4. Re: SDO_UTIL.APPEND strips away LRS dimensions
                B Hall

                Of course, you could also make it a NLRS (ha!) by just changing the coordinates or measures... but still have a "valid" LRS string.

                 

                SELECT

                SDO_LRS.VALIDATE_LRS_GEOMETRY(

                   SDO_GEOMETRY(3306,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1),SDO_ORDINATE_ARRAY(1,1,1,2,2,2,4,4,3,5,5,6))

                )

                FROM

                dual;

                 

                RESULT ON 11.2.0.4.2: TRUE

                 

                or going further...

                 

                SELECT

                SDO_LRS.IS_GEOM_SEGMENT_DEFINED(

                    SDO_GEOMETRY(3306,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1),SDO_ORDINATE_ARRAY(1,1,1,2,2,2,4,4,3,5,5,6))

                     )

                FROM

                dual;

                 

                RESULT ON 11.2.0.4.2: TRUE

                 

                Well, true according to the docs on what it checks, but rather non-linear for sure.

                 

                Bryan

                • 5. Re: SDO_UTIL.APPEND strips away LRS dimensions
                  Stefan Jager

                  I think it's an error in the docs:

                   

                  SDO_UTIL Package (Utility):

                  The geometry type (SDO_GTYPE value) of the resulting geometry reflects the types of the input geometries and the append operation. For example, if the input geometries are two-dimensional polygons (SDO_GTYPE = 2003), the resulting geometry is a two-dimensional multipolygon (SDO_GTYPE = 2007).

                   

                  That says to me that if I feed it LRS geometries, the result should also be LRS geometries. Thanks for the heads up though, these days I'm doing a lot of stuff with LRS for a national railroad company so this is good to keep in mind.

                   

                  And to be honest, it does happen that I want to add LRS geometries (compare for example a local traintrack that's also used for a regional train: the regional train would use two or more tracks between stations as one, since it won't stop anywhere, while the local train uses all the separate segments). But I guess I should never use APPEND for that

                   

                  Just my 2 cents,

                  Stefan

                   

                  PS. maybe someone should add a comment to the documentation, at the bottom of the page? Maybe that would at least correct the docs...

                   

                  Message was edited by: Stefan Jager Added the PS

                  • 6. Re: SDO_UTIL.APPEND strips away LRS dimensions
                    B Hall

                    Stefan Jager wrote:

                     

                    PS. maybe someone should add a comment to the documentation, at the bottom of the page? Maybe that would at least correct the docs...

                     

                    Go for it! You found it, you own it.

                    • 7. Re: SDO_UTIL.APPEND strips away LRS dimensions
                      Stefan Jager

                       

                      D*mn! I knew when I wrote that I was going to get such a reply from someone

                       

                       

                      I'll post a comment on the docs tonight, maybe that'll help other confused souls with this problem.

                      • 8. Re: SDO_UTIL.APPEND strips away LRS dimensions
                        Bkazar-Oracle

                        Hi,-

                        What is your database version?

                        I will file a bug for this issue for that database version.

                         

                        We have this bug

                        Bug 16223317 - SDO_UTIL.APPEND DOES NOT MAINTIN LRS SDO_GTYPE

                        We will file a backport bug onto 12.1.0.1.

                        Thanks

                        • 9. Re: SDO_UTIL.APPEND strips away LRS dimensions
                          Paul Dziemiela

                          Hi Baris,

                           

                          Thanks, I did not know that bug was in there.  My original question to some extent was whether this a bug or a feature.  Myself, Stefan and Bryan were kind of saying we think this is poorly documented feature and one should just "know" never to use SDO_UTIL.APPEND upon LRS geometries. 

                           

                          But Oracle thinks its a bug? 

                           

                          That works for me.

                           

                          Thanks,

                          Paul

                          • 10. Re: SDO_UTIL.APPEND strips away LRS dimensions
                            Bkazar-Oracle

                            Hi Paul,-

                            Please file a backport request for 12.1.0.1.

                            Thanks

                            • 11. Re: SDO_UTIL.APPEND strips away LRS dimensions
                              Paul Dziemiela

                              Hi Baris,

                               

                              Could you expound a bit more on what you mean by requesting a backport?  The existing bug is marked for 11.2.0.3 which matches my production environment and the defect is slated for correction in "12.2"  .  I don't have anything yet on 12.1 and my organization won't submit SRs for things we don't have in production (I have tried before).  In addition my production is still all on an OS which doesn't support 12c so any creative truth stretching is unlikely to succeed.

                               

                              Knowing there is a bug filed and knowing that the issue is considered by Oracle to be a "defect" kind of closes the topic for me.

                               

                              Cheers,

                               

                              Paul

                              • 12. Re: SDO_UTIL.APPEND strips away LRS dimensions
                                Bkazar-Oracle

                                Hi Paul,-

                                I will get back to you about 11.2.0.3, i missed that it was filed for 11.2.0.3.

                                Thanks

                                • 13. Re: SDO_UTIL.APPEND strips away LRS dimensions
                                  Bkazar-Oracle

                                  Hi,-

                                  We still need the backport patch request for this bug onto 11.2.0.3.

                                  Could you please file one?

                                  Please let us know if you need help with filing it.

                                  Thanks