4 Replies Latest reply on Oct 16, 2012 12:08 PM by John O'Toole

    SDO_CS.MAKE_2D for 4D LRS data

    John O'Toole
      This is a moan, not a question. I'm running Oracle database Lets say I've got a valid 4D LRS geometry:
      jot_test@11gR2> select sdo_geom.validate_geometry_with_context(
        2    sdo_geometry(4402, null, null, sdo_elem_info_array(1,2,1), sdo_ordinate_array(
        3    500100, 600100, 10, 100,
        4    500200, 600200, 9, 200,
        5    500300, 600300, 14, 300,
        6    500400, 600400, 11, 400))
        7  ,0.005) as valid
        8  from dual;
      And I want to convert this back to a simple 2D geometry. So I naively try:
      jot_test@11gR2> select sdo_cs.make_2d(
        2  sdo_geometry(4402, null, null, sdo_elem_info_array(1,2,1), sdo_ordinate_array(
        3    500100, 600100, 10, 100,
        4    500200, 600200, 9, 200,
        5    500300, 600300, 14, 300,
        6    500400, 600400, 11, 400))
        7  ) as geom2d
        8  from dual;
      SDO_GEOMETRY(3402, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(500100, 600100, 100, 500200, 9, 200, 600300, 14, 500400, 600400, NULL))
      As you can see that returns a woefully invalid geometry. So I checked the doco for SDO_CS.MAKE_2D ( http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_cs_ref.htm#CHDBCAEE ) and noticed that it only accepts a 3D geometry.

      So my moan of the day is - why can't Oracle make the extra bit of effort (extra 10 lines of code?) to make the SDO_CS.MAKE_2D function a bit more flexible? And surely the function shouldn't be returning totally invalid output for a valid input - how about some basic error checking in the function!

      In the end I am able to convert the LRS data to 2D by running Simon Greener's geom.to_2d() function which works perfectly.
        • 1. Re: SDO_CS.MAKE_2D for 4D LRS data
          Paul Dziemiela
          Hi John,

          I have been using Godfrind's snippet from the original 10g Pro Oracle Spatial for well nigh on 6 years now. I altered a second version to do what MAKE_2D does in preserving the LRS measure and a third to drop the LRS measure leaving the Z value. I have never even bothered to look twice at SDO_CS.MAKE_2D. I would think its in the CS package as its only official use is to "downgrade" spatially indexed data from XY<whatever>Z to XY<whatever>, not to help us manage our geometry dimensionality. The name is confusing but I suppose makes sense to the CS people from the indexing dimensionality perspective.

          I have some mixed feelings about several of the 11g additions to Oracle Spatial. I am not sure if some of them should not have been left in the oven a bit longer (LRS_INTERSECTION for example).

          In the end I think we all quite often end up reinventing the wheel. I just don't see a lot of activity in Oracle Spatial to build convenience tools and such for us programming joes, that would mean more code to support. Simon is a giant amongst us for actually putting his code out there. I'd ask the group in general if they have any additional suggestions for where to turn for wheel designs or new ideas. I think we can count them on one hand:

          * Simon's Excellent Resources
          * Kothuri, Godfrind and Beinat's Pro Oracle Spatial book
          * Official Oracle Documentation
          * OTN Oracle Spatial

          What else would you all suggest? I've looked at the Oracle Spatial Community pages inside metalink without seeing much traffic (one post a month) nor much that interesting. The LinkedIN groups seldom dive into code issues and overall are pretty thin gruel. Am I missing some other nifty online resources you would all recommend?

          • 2. Re: SDO_CS.MAKE_2D for 4D LRS data
            John O'Toole

            I use SDO_CS.MAKE_2D a lot as I work mainly with 2.5D data and often have to strip it back to 2D data for various reasons. I like to use SDO_CS.MAKE_2D mainly because it is in the 11g release so when I switch from database to database I know its already there without me having to load up a custom function.

            You have listed all the same sources that I use for wheel reinvention - I don't think there is anything else out there.
            On the plus side, it isn't too complicated to write pl/sql to manipulate sdo_geometry data, so it can be done without too much trouble and Simon's contribution is immense. But it is frustrating that Oracle can't be a bit more helpful in providing some of the commonly requested functions out of the box. E.g. a function to round ordinates and one to create a proper centroid of a complex polygon. The Spatial veterans will have hand cranked functions to do these things, but it is frustrating for newcomers to find that relatively simple tasks can't be achieved using out of the box functions.

            • 3. Re: SDO_CS.MAKE_2D for 4D LRS data
              What I do is convert the 4d to 3d and then use make_2d.


              Edited by: woodsmithnh on Oct 11, 2012 7:32 AM
              • 4. Re: SDO_CS.MAKE_2D for 4D LRS data
                John O'Toole
                Thanks for that Woodsmithnh, I hadn't thought of that approach.