This discussion is archived
4 Replies Latest reply: Apr 5, 2013 4:52 PM by Simon Greener RSS

ORACLE 11g Spatial and GRS 80

1001141 Newbie
Currently Being Moderated
Hi,
I am working on a project that will require both projected coordinates and geographic coordinates for different product files coming in. I have no issues defining, storing, extracting or matching the geographic coordinates. My columns for those coordinates with SRID 8307 are defined in the user_sdo_geom_metadata table as follows:
INSERT INTO
USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
('INVENTORYGEOMETRY','INVGEO',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .05),MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .05)),8307);

the index:
create index "MYDB"."INVGEO_IDX" on "MYDB"."INVENTORYGEOMETRY"("INVGEO") indextype is MDSYS.SPATIAL_INDEX;

However, I am confused as to how I am going to have a column that has both Geographic and Projected coordinates. Am I allowed to define more SDO_DIM_ELEMENT(s) fro the SDO_DIM_ARRAY with different min/max values. As an example, would I be able to still use an SRID of 8307 for both WGS 84 and GRS80 data (ellipsoid), something like the following:
INSERT INTO
USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
('INVENTORYGEOMETRY','INVGEO',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .05),MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .05),
(MDSYS.SDO_DIM_ELEMENT('FIXEDGRID_.5Resolution', 0, 26916, .5)),8307);

Is that possible or do i have to create a new column in the database?

Also, I don't know the relationship between the SDO_ELLIPSOIDS table and the SDO_GEOMETRY table. Will you please explain it to me?

I thank you for any help you can give. I'm confused as to how I'm going to make this work for both the geographic and projected coordinates or if I'm going to be able to make it work.

Thank you,
Erica
  • 1. Re: ORACLE 11g Spatial and GRS 80
    B Hall Explorer
    Currently Being Moderated
    Erica,

    I take it you want both geodetic and projected geometries in the same table (correct me if I am wrong). You can't put them both in the same column, but you certainly (at least from the database perspective) add two or more sdo_geometry data type columns to a single table - one of each in your case.

    Now - certain software does not like this at all, but for some you can get around it by using updateable views.

    Can you provide details on what this is to work with?

    Thanks,

    Bryan
  • 2. Re: ORACLE 11g Spatial and GRS 80
    Simon Greener Journeyer
    Currently Being Moderated
    Erica,

    You can have geodetic and projected data in a single column of a table BUT you can't spatially index them as the index can't work on both projections.

    You have to, as Bryan says, create two columns:
    create table INVENTORYGEOMETRY (
      fid integer,
      INVGEO sdo_geometry, /* For the geodetic data */
      INVPROJ sdo_geometry /* for the projected data */
    );
    If you do this you need to create two user_sdo_geom_metadata entries. One as you have already done, and the other for the projected data:
    INSERT INTO 
     USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) 
     VALUES ('INVENTORYGEOMETRY','INVPROJ',
     MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',0,1000, .05),MDSYS.SDO_DIM_ELEMENT('Y',0,1000, .05)),XXXXX);
    Where you replace 0,1000 ranges with your actual data range and XXXXX with your actual projected SRID (eg 28355 as in the example below).

    Now, assuming this table gets the INVGEO column populated first (via some external dynamic feed) you could synchronise the projected column via a trigger such as the following:
    create trigger inventorygeometry_proj_bi 
    before insert 
    on inventorygeometry
    FOR EACH ROW
    begin
       if (:new.invgeo is not null) then
         :new.invproj := mdsys.sdo_cs.transform(:new.invgeo,28355);
       end if;
    end;
    /
    Finally....
    Also, I don't know the relationship between the SDO_ELLIPSOIDS table and the SDO_GEOMETRY table. Will you please explain it to me?
    The SDO_ELLIPSOIDS table is part of the implementation of projections within the Oracle Spatial package. When you use a SRID in an SDO_GEOMETRY, Oracle uses that SRID to query the underlying tables (of which SDO_ELLIPSOIDS is only one member) to get the properties of the projection eg the definition of the ellipsoid being one.

    I hope this all helps.

    Don't forget to award points as you see fit if our answers are deemed correct or helpful.

    regards
    Simon
  • 3. Re: ORACLE 11g Spatial and GRS 80
    1001141 Newbie
    Currently Being Moderated
    Hi - I thank you both for your replies. I didn't know how to mark both of them as correct or I would have.

    Thank you again for your extremely good answers to my question.

    R/Erica
  • 4. Re: ORACLE 11g Spatial and GRS 80
    Simon Greener Journeyer
    Currently Being Moderated
    Erica,

    Thank you for awarding points to both of us. The awarding of points to those who regularly take the time to help posters is something that is greatly appreciated and ensures that that help is forthcoming to others.

    regards
    Simon

Legend

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