4 Replies Latest reply: Mar 13, 2013 4:34 AM by Simon Greener RSS

    How to Create Spatial Data from the scratch

    JoeSSI
      Dear Gurus,

      I am newbie in OBIEE Spatial Intelligence, I tried to register map to spatial table (OBIEE_NAVTEQ schema).
      But I have no idea how to do it.

      I see the example:

      INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME,DIMINFO, SRID)
      VALUES ('cola_markets','geometry',
      SDO_DIM_ARRAY(-- 20X20 grid
      SDO_DIM_ELEMENT('X', 0, 20, 0.005),
      SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
      ),
      NULL
      );

      INSERT INTO cola_markets
      VALUES(2,'cola_b',200,
      SDO_GEOMETRY(2003, -- two-dimensional polygon
      NULL,
      NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
      SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
      )
      );

      ***

      -How to define SDO_DIM_ELEMENT ?
      How to know the value of SDO_DIM_ELEMENT('X', 0, 20, 0.005) from the map? is there any tool which can help it?
      -what different SDO_DIM_ELEMENT , SDO_ELEM_INFO_ARRAY and SDO_ORDINATE_ARRAY?
      How to define SDO_ELEM_INFO_ARRAY and SDO_ORDINATE_ARRAY from the map?

      Any help really really appreciated

      Regards

      JOE
        • 1. Re: How to Create Spatial Data from the scratch
          Simon Greener
          Joe,

          Try using the GeoRaptor extension for SQL Developer. Download SQL Developer from OTN and then get GeoRaptor from Sourceforge.
          How to define SDO_DIM_ELEMENT ?
          You must mean logically as your example shows how to do it manually using SQL.

          No hard and fast rules. Generally most people would determine the rectangular extent (or MBR) of their data and enter the X and Y range as determined by the lower left coordinate and upper right coordinate.

          For geodetic data (ie long/lat) most simply put in the full world extents of -180,180 and -90,90. But others put in the extent of their data. Read the documentation on this.
          How to know the value of SDO_DIM_ELEMENT('X', 0, 20, 0.005) from the map? is there any tool which can help it?
          See above. Try any GIS package, or GeoRaptor or
          select SDO_GEOM.SDO_MIN_MBR_ORDINATE(mbr,1) as minx,
                 SDO_GEOM.SDO_MIN_MBR_ORDINATE(mbr,3) as maxx,
                 SDO_GEOM.SDO_MIN_MBR_ORDINATE(mbr,2) as miny,
                 SDO_GEOM.SDO_MIN_MBR_ORDINATE(mbr,4) as maxx
            from (select sdo_aggr_mbr(geom) as mbr
                    from PLANNING_NEIGHBORHOODS);
          -- Result
          --
                MINX       MAXX       MINY       MAXX
          ---------- ---------- ---------- ----------
          5979385.646    6024741 2085840.482    2131294 
          Or...
          select 'SDO_DIM_ARRAY(' ||
                 'SDO_DIM_ELEMENT(''X'','|| 
                    SDO_GEOM.SDO_MIN_MBR_ORDINATE(mbr,1) || ',' ||
                    SDO_GEOM.SDO_MIN_MBR_ORDINATE(mbr,3) || ',0.05' ||
              '),SDO_DIM_ELEMENT(''Y'',' ||
                 SDO_GEOM.SDO_MIN_MBR_ORDINATE(mbr,2) || ',' ||
                 SDO_GEOM.SDO_MIN_MBR_ORDINATE(mbr,4) || ',0.05))' as dimarray
            from (select sdo_aggr_mbr(geom) as mbr
                    from PLANNING_NEIGHBORHOODS);
          -- Result
          --
          DIMARRAY
          -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',5979385.6456569,6024740.99982789,0.05),SDO_DIM_ELEMENT('Y',2085840.48152296,2131294.0001958,0.05))
          The sdo_tolerance in the SDO_DIM_ELEMENT is your guess as to how accurate the data is. Think about how close two points are allowed to be before they are considered equal. So, 0.05 is 5cm (standard for long/lat data) and means two vertices cannot be closer than that otherwise you will get a duplicate vertices error (13356) when validating your data using sdo_geom.validate_geometry().
          what different SDO_DIM_ELEMENT
          Metadata that describes the extent of your data. Written to user_sdo_geom_metadata as it is needed for correct spatiali indexing.
          SDO_ELEM_INFO_ARRAY and SDO_ORDINATE_ARRAY?
          objects that are used to describe an individual spatial object.
          How to define SDO_ELEM_INFO_ARRAY and SDO_ORDINATE_ARRAY from the map?
          I don't think this is the place to start reinventing the wheel trying to explain what is well explained in the Oracle documentation.

          As such, I would suggest that you read chapter 1 Spatial Concepts (http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_intro.htm#i884907) and then come back and ask us questions.

          regards
          Simon

          Edited by: Simon Greener on Mar 13, 2013 5:23 PM
          • 2. Re: How to Create Spatial Data from the scratch
            JoeSSI
            Thank you for the explanation, it is really help me.
            And do you have link for download georaptor?it is free?

            Edited by: JoeSSI on Mar 12, 2013 11:55 PM
            • 3. Re: How to Create Spatial Data from the scratch
              Ivan Bush
              Joe,


              http://sourceforge.net/projects/georaptor/


              Regards

              Ivan
              • 4. Re: How to Create Spatial Data from the scratch
                Simon Greener
                Joe,

                Thank you for the points.

                I am GeoRaptor's main developer. Feel free to ask questions on sourceforge or here.

                Note: there is no user documentation. There is some on-line help, but until the project attracts some more people to help write online help and doco you will have to learn by hacking around.

                regards
                Simon

                Edit: Yes it is free.