This discussion is archived
4 Replies Latest reply: Mar 13, 2013 2:34 AM by Simon Greener RSS

How to Create Spatial Data from the scratch

JoeSSI Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Joe,


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


    Regards

    Ivan
  • 4. Re: How to Create Spatial Data from the scratch
    Simon Greener Journeyer
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points