This discussion is archived
11 Replies Latest reply: Sep 30, 2012 3:20 PM by John O'Toole RSS

SRIDs Oracle Spatial and Google Maps

PedroF Newbie
Currently Being Moderated
Hi,

I've been struggling with a problem related with Google Maps visualization. I'm aware that Google Maps uses spherical math in its projections while Oracle Spatial uses ellipsodial math and that this difference may lead to inconsistencies.

I've data that uses SRID 82086 (Irish Transverse Mercator Grid) and I want to see this data displayed on a Google Map on OBIEE. However when I try this my layers are shifted in relation to where they should be. I know that for this to work correctly we need to make some kind of transformation and I've read this 2 links:

http://docs.oracle.com/cd/E16338_01/appdev.112/e11830/sdo_cs_concepts.htm#CIHJFBFG

http://yangiiiyang.blogspot.co.uk/2012/01/miss-is-as-good-as-mile.html

and I've done my fair share of searching here in the forums but I'm still having troubles.

The first option of using the SRID 4055 isn't really an option since the data I got has the SRID 82086 like I said before. So what I tried to do was to declare an EPSG rule between my ellipsoidal and spherical coordinate systems just like those 2 links mention.

I did something like this:

CALL sdo_cs.create_pref_concatenated_op(
8208623785,
'CONCATENATED OPERATION 82086',
TFM_PLAN(SDO_TFM_CHAIN(82086, 1000000000, 4055, 19847, 3785)),
NULL);

However I don't know if I'm doing this as I should and I also don't know if after doing this I should doing something more... What I can tell you is that by doing only this I still can't get the correct results!

If you can help me and give me an explanation step by step of what I should do I would appreciate that.

Thank you
  • 1. Re: SRIDs Oracle Spatial and Google Maps
    FlyingGuy Explorer
    Currently Being Moderated
    I think oracle has a transform function from one srid to another srid. I am really new to spatial myself so that about the best I can do.
  • 2. Re: SRIDs Oracle Spatial and Google Maps
    John O'Toole Journeyer
    Currently Being Moderated
    Hi Pedro,

    You mentioned you are using SRID 82086 for Irish Transverse Mercator Grid data. For ITM data I believe you should be using SRID 2157.

    - Can you post some sample coordinates from your base ITM data?
    - What version of Oracle database are you running? (note that the definition of SRID 2157 was changed in recent releases... I'll see if I can dig out the details)
    - Are you using Oracle MapViewer to display the map data on OBIEE?

    Regards,
    John
  • 3. Re: SRIDs Oracle Spatial and Google Maps
    PedroF Newbie
    Currently Being Moderated
    Sorry for only answering now guys.

    @FlyingGuy: I tried to do something with transformations like it's explained here http://docs.oracle.com/cd/E16338_01/appdev.112/e11830/sdo_cs_concepts.htm#i889615 but wasn't very successful. Since I didn't know exactly what I was doing that can be the reason...

    @John: I wasn't aware that I should be using that SRID, I can try to use that SRID to see if it makes any difference.

    This is an example of a multi-polygon that specifies an area in Northern Ireland in my data (quite long I'm afraid):

    MDSYS.SDO_GEOMETRY(2007,82086,MDSYS.SDO_POINT_TYPE(292355.75986035,410493.591,NULL),MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,505,1003,1),
    MDSYS.SDO_ORDINATE_ARRAY(288569.915702892,413884.6128,288569.891,413884.8428,288569.5033,413884.7828,288569.5115,413884.5628,288569.0331,413884.5028,
    288568.9506,413884.2127,288568.8682,413883.9427,288568.2495,413881.8925,288568.9919,413881.9625,288568.8764,413881.6224,288568.1506,413881.5624,
    288556.9658,413844.5083,288488.4632,413797.833,288442.7591,413756.5584,288440.6146,413734.7359,288455.2389,413688.1807,288454.0016,413684.6003,
    288452.9871,413681.6599,288577.8754,413620.163,288616.6756,413603.3711,288616.5436,413604.0112,288621.5916,413601.8209,288621.6659,413601.2109,
    288699.3653,413561.0864,288784.9667,413522.182,288861.5774,413493.3587,288953.3815,413463.2053,289212.1157,413383.3963,289373.1895,413335.2409,289433.4932,
    413319.7392,289508.1325,413302.7173,289576.734,413293.3962,289609.2573,413289.9458,289679.4095,413284.8252,289753.158,413282.775,289763.7819,413288.0256,
    290086.1109,413263.5729,290086.3996,413260.3025,290086.5151,413258.7223,290130.7262,413235.8297,290156.5518,413190.2146,290363.3874,413038.0175,
    290558.8073,413006.4839,290634.3375,413006.4839,290882.1591,412936.516,291082.3384,412919.6041,291234.5864,412925.2448,291719.6801,412897.5716,
    291890.6766,412757.0358,291927.6045,412762.8465,291933.799,412757.2959,291939.6801,412752.0253,291910.4809,412738.7538,291910.6129,412644.3131,
    291732.3908,412587.0267,291603.6341,412592.1273,291552.4943,412597.0778,291255.5125,412631.7317,291161.473,412602.0284,291273.5104,411656.1219,
    291250.5635,411267.2781,291257.1621,411009.8991,291265.4105,410922.4493,291243.9648,410844.9105,291014.6276,410706.3149,291069.2977,410604.1534,
    291103.8171,410535.6357,291108.964,410400.0304,291133.2059,410308.6201,291137.2476,410304.8397,291143.7721,410298.749,291131.0201,410141.6813,
    291008.8373,409580.1681,291158.5366,409350.6623,291408.9318,409200.9854,291385.3332,408275.9113,291433.4046,408172.0596,291669.1342,407841.0423,
    292324.2347,407237.9344,292527.0039,406679.0215,292558.1991,406460.6569,292814.8878,406083.1444,292994.9327,405732.7649,293348.4735,405943.3286,
    293507.6089,405983.1031,293630.4516,405922.3163,293698.8387,405979.3027,294113.211,406244.4625,294074.2128,406603.863,294080.696,406607.4034,
    294351.91,406744.0088,294721.0649,406637.4268,295181.1909,406689.4226,295407.3607,406715.4156,296098.8363,406866.1925,296067.6411,406988.3663,
    296046.6078,407180.868,296007.5023,407450.7684,295927.7161,407582.4732,295844.1355,407669.843,295801.0791,407771.1544,295822.6073,407881.3268,
    295830.2041,408072.5384,295889.7242,408196.6323,296025.2363,408277.6815,296027.95,408406.686,296044.1993,408444.6003,296075.4935,408484.9148,
    296151.3123,408519.8187,296234.3567,408563.7437,296262.6403,408601.6579,296270.9134,408727.2321,296263.3167,408818.4024,296175.9337,408971.6296,
    296004.9701,409254.0214,295869.4662,409431.3014,295822.6073,409512.3505,295797.2766,409594.6598,295749.1558,409695.9712,295704.8291,409820.0651,
    295611.1196,409956.8305,295561.7285,410021.4078,295509.8052,410153.1126,295475.6075,410258.2145,295378.0955,410441.8351,295272.9868,410568.4694,
    295217.2609,410645.7081,295142.5474,410822.9981,295098.2207,410934.4306,295075.4223,411048.4034,295045.0354,411248.486,295031.1039,411324.4645,
    294981.7128,411383.9812,294898.1323,411416.9049,294847.4709,411446.0282,294782.8863,411505.5449,294717.0315,411660.0423,294624.584,411823.3907,
    294551.1324,411915.8311,294468.8139,411982.9487,294411.8261,412009.5417,294249.7295,412081.7198,294156.0117,412153.9079,294101.5561,412238.7475,
    294016.7053,412383.1137,293960.9877,412481.8848,293900.1974,412530.0003,293806.4796,412546.4621,293735.5686,412590.7871,293591.1977,412687.0279,
    293539.2744,412792.1298,293544.3388,412873.1789,293594.992,412940.2965,293654.5121,412998.543,293745.6976,413051.729,293798.8829,413122.647,293781.1572,
    413187.2243,293722.8991,413241.6804,293611.4557,413246.741,293480.3976,413264.523,293357.7034,413293.3962,293278.3048,413365.5743,293213.3491,413524.3622,
    292611.6897,412876.8593,292550.6024,412863.7678,292547.1876,412863.6078,292542.4943,412863.1278,292470.5274,412878.5195,292253.8103,412751.4852,
    292215.208,412846.5559,292133.1286,412992.2123,292002.45,412958.4285,291882.2881,413004.7637,291880.5724,413137.7987,291799.8871,413198.7256,
    291990.4322,413318.8791,291847.9584,413535.1534,291782.7222,413664.748,291750.1083,413791.7723,291729.5121,413884.4528,291753.5396,413973.7128,
    291682.3068,414081.855,291618.7945,414210.5895,291531.2466,414341.0342,291310.6692,414655.1495,291331.2653,414922.9197,291268.6108,415010.4595,
    291210.2455,415019.9006,291132.9997,415172.6678,291066.0561,415210.4221,290947.6098,415147.775,290863.5014,415043.9333,290929.5872,414813.0673,
    290788.8291,414771.0126,290633.4796,414711.7959,290575.1143,414714.3662,290534.7716,414692.9138,290503.8733,414647.4287,290450.6632,414699.7746,
    290403.4579,414674.8918,290407.7471,414555.5983,290249.8241,414510.9633,290217.2019,414534.996,290037.8251,414498.0919,289950.2771,414689.4834,
    289891.0539,414663.7305,289770.0341,414616.5252,289745.1489,414680.8924,289830.9729,414929.7805,289851.2391,415054.5645,289854.2993,415100.3997,
    289792.3212,415254.427,289741.4123,415214.7526,289725.8477,415204.3314,289512.6196,415193.4201,289461.9088,415027.6215,289526.2789,414836.2399,
    289348.6177,414789.8947,289318.5772,414865.4132,289237.9001,414851.6817,289242.1892,414763.2817,289126.3164,414706.6453,289146.0629,414631.9769,
    289364.9247,414621.6758,289340.889,414534.996,289267.9406,414461.1877,289296.2654,414376.2281,289514.2693,414453.4668,289546.0254,414319.5818,289436.1657,
    414252.6342,289488.5179,414171.9651,289715.9662,414207.1491,289800.9324,413953.9706,289741.7175,413833.8171,289788.065,413771.17,289745.1489,413436.4623,
    289171.806,413424.441,288908.3537,413526.7425,288626.2767,413607.6116,288620.2554,413610.4219,288621.4184,413603.0711,288616.3704,413605.3113,288615.1662,
    413612.8322,288462.9099,413685.4604,288454.9667,413749.2175,288562.2612,413842.6881,288569.7425,413881.6925,288569.0826,413881.6224,288569.1898,
    413881.9825,288569.8085,413882.0525,288570.3116,413884.6628,288569.9157,413884.6128,288569.495,413885.8729,288569.5033,413884.9128,288569.8827,
    413884.9728,288569.8002,413885.9229,288570.1797,413885.9829,288572.5387,413887.1831,288572.5139,413887.5931,288572.9923,413889.2433,288572.9676,
    413889.7034,288570.7818,413889.4333,288569.2888,413887.2731,288569.0331,413886.443,288568.9506,413886.173,288568.8269,413885.7629,288569.495,
    413885.87292456))

    I'm using Oracle database version 11.2 and yes, I'm using Oracle MapViewer to display the map data on OBIEE.

    Thanks
    Pedro
  • 4. Re: SRIDs Oracle Spatial and Google Maps
    John O'Toole Journeyer
    Currently Being Moderated
    Hi Pedro,

    Ok, so your data is in Irish Grid - I thought it may have been in "IRENET95 / Irish Transverse Mercator" which is SRID 2157. In the case of your data, SRID 82086 is probably ok.
    For Republic of Ireland "Irish Transverse Mercator" I use SRID 2157, for Republic of Ireland "Irish Grid" I use SRID 29903.
    You can see the various flavours by running this query:
    select * from cs_srs where cs_name like '%Irish%' order by srid;
    To try out your polygon, I created a Google Maps Map Tile Layer in MapViewer 11ps5. Then I loaded your polygon into a table and created a Geometry Theme for it in MapBuilder. I then added both of these to a html page using:
      function showMap()
      {     
        var baseURL  = "http://localhost:8888/mapviewer";
        var mapCenterLon =  -758866.8;
        var mapCenterLat =  7301258.8;
        var mapZoom      =  7;  
        var mpoint = MVSdoGeometry.createPoint(mapCenterLon,mapCenterLat,3785);
        var mapview = new MVMapView(document.getElementById("map"), baseURL);
              mapview.addMapTileLayer(new MVMapTileLayer("jot_test.google_map")); 
     
              var irish_grid_test_foi = new MVThemeBasedFOI('themebasedfoi1','jot_test.irish_grid_test');
        irish_grid_test_foi.setBringToTopOnMouseOver(true);
        mapview.addThemeBasedFOI(irish_grid_test_foi);
    
              mapview.setCenter(mpoint); 
        mapview.setZoomLevel(mapZoom);           
        mapview.display();                 
      }
    With that I get the following image: https://dl.dropbox.com/u/32771308/Irish_Grid_Polygon_on_Google_82086.png

    Is that how you expect your data to show? Or is this still shifted from where you expect it to be? If so, then where do you expect the polygon to be located?

    John
  • 5. Re: SRIDs Oracle Spatial and Google Maps
    PedroF Newbie
    Currently Being Moderated
    Hi John,

    No, your image shows the problem I have. As you can see the area of the polygon is over a small water area and it shouldn't be there, that's the problem I have, the layers are a bit shifted to the south.

    I don't have access to the machine where I have the maps right now but I'll try to give you an example that it's maybe easier to test.

    https://www.dropbox.com/s/5p8ukt6sysaferz/Multi-Polygon.txt

    https://www.dropbox.com/s/wbusynf1c67rr4y/map.jpg

    On that map you can see that the dark yellow area has a small island north of the coast. The area that defines that polygon is on the .txt file I put above. In this map the layers are correct because I'm using NAVTEQ maps and not a google map. If you try to use a google map with this polygon you will see that the area of that island will be shifted to the south and will be on the mainland.

    Pedro
  • 6. Re: SRIDs Oracle Spatial and Google Maps
    750496 Newbie
    Currently Being Moderated
    Pedro,

    The polygon you supplied via the dropbox link had lots of vertices - too many for me to copy into a sql*plus session. Can you run SDO_UTIL.SIMPLIFY() on the geometry to reduce it down to a sensible size? Then I can load it up in my MapViewer session?

    Thanks,
    John
  • 7. Re: SRIDs Oracle Spatial and Google Maps
    PedroF Newbie
    Currently Being Moderated
    John,

    I reduced the polygon quite a bit, see if you can load it now.

    https://www.dropbox.com/s/5p8ukt6sysaferz/Multi-Polygon.txt

    If it's still too big I'll try to reduce it even more.

    Pedro
  • 8. Re: SRIDs Oracle Spatial and Google Maps
    John O'Toole Journeyer
    Currently Being Moderated
    Still far to much text to copy into sql*plus. Its needs to be <2499 characters. Simplify it to a few hundred meters - we just want to see the shape in the general ball park of where the Google data is.
  • 9. Re: SRIDs Oracle Spatial and Google Maps
    PedroF Newbie
    Currently Being Moderated
    Sorry about that John.

    Use this one then, it is within the limit you need.

    https://www.dropbox.com/s/5p8ukt6sysaferz/Multi-Polygon.txt
  • 10. Re: SRIDs Oracle Spatial and Google Maps
    Stefan Jager Journeyer
    Currently Being Moderated
    Pedro,

    Take a look at this thread, maybe that is helpful?

    Support for Google Maps Projection 900913

    Cheers,
    Stefan
  • 11. Re: SRIDs Oracle Spatial and Google Maps
    John O'Toole Journeyer
    Currently Being Moderated
    Hi,

    I loaded up your polygon, but it shows up 20 or 30 south of where it should be:
    https://dl.dropbox.com/u/32771308/Irish_Grid_Polygon_Offset.png

    Can somebody from Oracle pitch in and suggest the TFM_PLAN required to transform 82086 into 3785?

    John

Legend

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