9 Replies Latest reply: Jul 25, 2014 4:11 PM by spiderman21 RSS

    Transform issue, Alaska Albers

    spiderman21

      Hi,

       

      I seem to have an issue with transforming coordinate values from Alaska Albers NAD 83 (EPSG:3338) to WGS84 (EPSG:4326) when

      all or part of the SDO_GEOMETRY is located on the left side of the 180th meridian.

       

      We store a lot of our data in NAD 83 Alaska Albers (Please no discussion about why, that's another issue altogether).

       

      We are running Oracle 11.2.0.4.0.

       

      Our Oracle settings for 3338:

       

      select srid, table_name, diminfo from all_sdo_geom_metadata where table_name = 'MY_TABLE';

      select wktext from cs_srs where srid = 3338;

       

      SRID    : 3338

      DIMINFO : MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-2556059,1901144,0.0005),MDSYS.SDO_DIM_ELEMENT('X',-202628,2948232,0.0005))

      WKTEXT  : PROJCS["NAD83 / Alaska Albers", GEOGCS [ "NAD83", DATUM ["North American Datum 1983 (EPSG ID 6269)", SPHEROID ["GRS 1980 (EPSG ID 7019)", 6378137.0, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.0174532925199433]], PROJECTION ["Albers Conical Equal Area"], PARAMETER ["Latitude_Of_Origin", 50.0], PARAMETER ["Central_Meridian", -154.0], PARAMETER ["Standard_Parallel_1", 55.0], PARAMETER ["Standard_Parallel_2", 65.0], PARAMETER ["False_Easting", 0.0], PARAMETER ["False_Northing", 0.0], UNIT ["Meter", 1.0]]

       

      Here is the basic problem:

       

      Below is a sequence of SQL transactions and results (run in Oracle's SqlDeveloper V 4.0.0.13).

       

      1.  select shape from my_table where objectid = 8078

       

          ==>  MDSYS.SDO_GEOMETRY(2001,3338,MDSYS.SDO_POINT_TYPE(-1824752.05401816,525402.950982795,NULL),NULL,NULL)

       

      2.  select mdsys.sdo_cs.transform(shape, 4326) from my_table where objectid = 8078;

       

          ==>  MDSYS.SDO_GEOMETRY(2001,4326,MDSYS.SDO_POINT_TYPE(179.228105400585,51.3814747403352,NULL),NULL,NULL)

       

      3.  select mdsys.sdo_cs.transform((select mdsys.sdo_cs.transform(shape, 4326) from my_table where objectid=8078), 3338) from dual;

       

          ==>  MDSYS.SDO_GEOMETRY(2001,3338,MDSYS.SDO_POINT_TYPE(-4436432.78053883,3406016.62003979,NULL),NULL,NULL)

       

      4.  select mdsys.sdo_cs.transform(mdsys.sdo_cs.transform((select mdsys.sdo_cs.transform(shape, 4326) from my_table where objectid=8078), 3338), 4326) from dual;

       

          ==>  MDSYS.SDO_GEOMETRY(2001,4326,MDSYS.SDO_POINT_TYPE(121.955143420207,51.3814747375669,NULL),NULL,NULL)

       

      As you can plainly see the ordinate values for items 1 and 3 are monumentally off and the item 4 result's show a

      location approximately 1/6th of the way around the planet.

       

      Being in Alaska means that we actually have responsibility for "things" on both sides of the 180th Meridian and we would like

      to handle all the data within a single mapping application.

       

      Any suggestions would be appreciated.  Thank you,

      Peter

        • 1. Re: Transform issue, Alaska Albers
          Paul Dziemiela

          Hi Peter,

           

          First of all thanks for the info that this old USGS Albers projection now has an EPSG code.  I did not know that.  Is that a recent thing?

           

          I believe you have something messed up in your custom transformation for your 3338 srid.  Do you know who set that up for you?  You might want to push the issue back on them.  I use the same Albers projection for EPA Alaska data as we document here though its srid 1000003 for me (I generally do not change SRIDs under 1000000).

          Spatial Data in WATERS | Docs | US EPA

           

          When I make the following call

          SELECT

          SDO_CS.TRANSFORM(

              SDO_CS.TRANSFORM(

                  MDSYS.SDO_GEOMETRY(2001,1000003,MDSYS.SDO_POINT_TYPE(-1824752.05401816,525402.950982795,NULL),NULL,NULL)

                ,4326

              )

            ,1000003

          )

          FROM dual;

           

          I get back -1824752.05401816, 525402.950982791 which seems pretty reasonable.  I would check your MDSYS.SDO_COORD_OPS and MDSYS.SDO_COORD_OP_PARAM_VALS tables to see if they match what I have documented on that site above.

           

          Cheers,

           

          Paul

          • 2. Re: Transform issue, Alaska Albers
            spiderman21

            Hi Paul,

            Thanks for responding. Looking at the EPA site and the WKT, it appears our projection information (3338) is the same.  Would it be possible for you to post the WKT for your 1000003?

            DNR submitted information for 3338 to EPSG around 2007 or so.


            Thanks again,

            Peter

            • 3. Re: Transform issue, Alaska Albers
              Paul Dziemiela

              Hi Peter,

               

              It's been a while since I looked at this but I did not think the legacy wkt entry into the spatial cs tables really did anything.  I always thought it was just for reference.  I am not sure if that has changed.  In any case I dutifully populate it with

               

              PROJCS["NAD83 Alaska Albers Equal Area USGS",GEOGCS["NAD 83",DATUM["NAD 83",SPHEROID["GRS 80",6378137,298.2572221010002]],PRIMEM["Greenwich",0],UNIT["Decimal Degree",0.0174532925199433]],PROJECTION["Albers Conical Equal Area"],PARAMETER["False_Easting",0.0],PARAMETER["False_Northing",0.0],PARAMETER["Central_Meridian",-154.0],PARAMETER["Standard_Parallel_1",55],PARAMETER["Standard_Parallel_2",65],PARAMETER["Latitude_Of_Origin",50.0],UNIT["Meter",1.0]]


              So it is helpful that these old USGS projections are getting EPSG codes.  I was pleased to see that the continental US version was registered as 5070 and Oracle picked it up for the 12c release.  So just now I checked and 12c indeed also has 3338.  Now this is where things get whacky.  I ran your test on 12c as

               

              SELECT

              SDO_CS.TRANSFORM(

                  SDO_CS.TRANSFORM(

                      MDSYS.SDO_GEOMETRY(2001,3338,MDSYS.SDO_POINT_TYPE(-1824752.05401816,525402.950982795,NULL),NULL,NULL)

                    ,4326

                  )

                ,3338

              )

              FROM dual;


              and got back the bad result of -4436432.78053883,3406016.62003979.


              So I am unclear exactly how new SRIDs get added to existing systems but looking at my 11g production dbs I don't think 3338 ever was included.  So perhaps someone on your end did the very reasonable task of copying the 12c definition of 3338 back onto 11g without noticing that the 12c definition is hosed.  So when you put in an SR, you probably need to do so for 12c. 


              So why is the thing hosed?  It should be easy to spot but I am not spotting it.  The transformations seem equivalent to me.  I am mulling the IS_LEGACY difference and if that steers things differently within the chain.  The next step would be to start experimenting with the various options but mucking about in my stock tables is not very appealing, I'd say this is clear SR for our friends at Oracle.   


              Cheers,


              Paul


              • 4. Re: Transform issue, Alaska Albers
                spiderman21

                Thanks again Paul.  We came to the same conclusion after inserting the DDL for 1000003.  It works fine on the transforms.  So we tried to modify that for 3338 but like you found out, it doesn't work.    We also confirmed the WKT didn't make any difference either.

                 

                Our DBA is sure he didn't copy anything in for 3338, so I am not sure how it got there.

                 

                I will probably install a local version an dink around myself.  In the meantime, it sure would be nice if our Oracle friends here could look into this.  I will also have our DBA submit a ticket for this.

                 

                Peter

                • 5. Re: Transform issue, Alaska Albers
                  spiderman21

                  Paul,

                  We were able to put together a script that updates the 3338 CRS with the parameters derived by EPA and it works.  I have updated two databases so far and the transformation to/from WGS84 is working correctly, YMMV.  As depicted above, the statement:

                  select SDO_CS.TRANSFORM(MDSYS.SDO_GEOMETRY(2001,3338,MDSYS.SDO_POINT_TYPE(-1824752.05401816,525402.950982795,NULL),NULL,NULL),4326) from dual;

                  reveals:

                  MDSYS.SDO_GEOMETRY(2001,4326,MDSYS.SDO_POINT_TYPE(179.228105400585,51.3814747431036,NULL),NULL,NULL)

                   

                  We submitted an SR for creation of a functioning 3338 for 11g.  Not sure how far that will get us.

                   

                  The statement:

                  REM remove existing CRS if it exists...

                  delete from mdsys.SDO_COORD_REF_SYSTEM where srid = 3338;

                  delete from MDSYS.SDO_COORD_OP_PARAM_VALS where coord_op_id = 3338;

                  delete from MDSYS.SDO_COORD_OPS where coord_op_id = 3338;

                  COMMIT;

                   

                  Insert into MDSYS.SDO_COORD_OPS(coord_op_id, coord_op_name, coord_op_type, coord_op_method_id, data_source, show_operation, is_legacy, reverse_op, is_implemented_forward, is_implemented_reverse) Values (3338, 'NAD83 Alaska Albers Equal Area (meters)', 'CONVERSION', 9822, 'EPA OW', 1, 'FALSE', 1, 1, 1);

                  COMMIT;

                  /* Second Insert the parameter values for each custom coordinate operation into SDO_COORD_OP_PARAM_VALS */

                  Insert into MDSYS.SDO_COORD_OP_PARAM_VALS (coord_op_id, coord_op_method_id, parameter_id, parameter_value, uom_id) Values (3338, 9822, 8821, 50, 9102);

                  Insert into MDSYS.SDO_COORD_OP_PARAM_VALS (coord_op_id, coord_op_method_id, parameter_id, parameter_value, uom_id) Values (3338, 9822, 8822, -154, 9102);

                  Insert into MDSYS.SDO_COORD_OP_PARAM_VALS (coord_op_id, coord_op_method_id, parameter_id, parameter_value, uom_id) Values (3338, 9822, 8823, 55, 9110);

                  Insert into MDSYS.SDO_COORD_OP_PARAM_VALS (coord_op_id, coord_op_method_id, parameter_id, parameter_value, uom_id) Values (3338, 9822, 8824, 65, 9110);

                  Insert into MDSYS.SDO_COORD_OP_PARAM_VALS (coord_op_id, coord_op_method_id, parameter_id, parameter_value, uom_id) Values (3338, 9822, 8826, 0, 9001);

                  Insert into MDSYS.SDO_COORD_OP_PARAM_VALS (coord_op_id, coord_op_method_id, parameter_id, parameter_value, uom_id) Values (3338, 9822, 8827, 0, 9001);

                  COMMIT;

                  REM make sure SRID is completely removed…

                  delete from mdsys.SDO_COORD_REF_SYS where srid = 3338;

                  REM Have to directly and manually create an entry and delete it before it will go…

                  Insert into MDSYS.SDO_COORD_REF_SYS (srid, coord_ref_sys_name, coord_ref_sys_kind, coord_sys_id, geog_crs_datum_id, source_geog_srid, projection_conv_id, information_source, is_legacy, legacy_wktext, is_valid, supports_sdo_geometry) Values (3338, 'NAD83 Alaska Albers Equal Area USGS', 'PROJECTED', 4400, 20006, 2000006, 3338, 'EPA OW', 'TRUE', 'PROJCS["NAD83 Alaska Albers Equal Area USGS",GEOGCS["NAD 83",DATUM["NAD 83",SPHEROID["GRS 80",6378137,298.2572221010002]],PRIMEM["Greenwich",0],UNIT["Decimal Degree",0.0174532925199433]],PROJECTION["Albers Conical Equal Area"],PARAMETER["False_Easting",0.0],PARAMETER["False_Northing",0.0],PARAMETER["Central_Meridian",-154.0],PARAMETER["Standard_Parallel_1",55],PARAMETER["Standard_Parallel_2",65],PARAMETER["Latitude_Of_Origin",50.0],UNIT["Meter",1.0]]', 'TRUE', 'TRUE');

                  delete from mdsys.SDO_COORD_REF_SYSTEM where srid = 3338;

                  Insert into MDSYS.SDO_COORD_REF_SYSTEM (srid, coord_ref_sys_name, coord_ref_sys_kind, coord_sys_id, geog_crs_datum_id, source_geog_srid, projection_conv_id, information_source, is_legacy, legacy_wktext, is_valid, supports_sdo_geometry) Values (3338, 'NAD83 Alaska Albers Equal Area USGS', 'PROJECTED', 4400, 20006, 2000006, 3338, 'EPA OW', 'TRUE', 'PROJCS["NAD83 Alaska Albers Equal Area USGS",GEOGCS["NAD 83",DATUM["NAD 83",SPHEROID["GRS 80",6378137,298.2572221010002]],PRIMEM["Greenwich",0],UNIT["Decimal Degree",0.0174532925199433]],PROJECTION["Albers Conical Equal Area"],PARAMETER["False_Easting",0.0],PARAMETER["False_Northing",0.0],PARAMETER["Central_Meridian",-154.0],PARAMETER["Standard_Parallel_1",55],PARAMETER["Standard_Parallel_2",65],PARAMETER["Latitude_Of_Origin",50.0],UNIT["Meter",1.0]]', 'TRUE', 'TRUE');

                  COMMIT;

                  • 6. Re: Transform issue, Alaska Albers
                    Paul Dziemiela

                    Glad things are working out,

                     

                    I would be interested to hear what Oracle says the problem with the original 3338 is.  I eyeballed it and just did not see the problem.

                     

                    There are some great questions embedded in all this that I admit I have no idea about.  I am hoping maybe your SR response could shed some light on things.

                     

                    (1) Does Oracle support cover "stock" projections?  In this case I think we have determined that the 3338 provided by Oracle is not working correctly.  I think we've been talking like Oracle is beholden to fix this.  But is that true?  I am curious what your SR will say.
                     

                    (2) Now that you have "fixed" your stock 3338, how does that affect support for this SRID?  If some other user comes along in your organization with an SR about this now altered 3338, how does that work?  How does Oracle know or not know that a given SRID has been altered?

                     

                    (3) Does anyone have an idea how new SRIDs come along?  My production 11g boxes were upgraded from 10g (and from 9i before that).  I get the impression that there is a kind of "universe" of stock SRIDs over in Reston that "come along" perhaps with fresh installs but not (?) from upgrades?  I believe you that you got 3338 on 11gR2 from some kind of stock install.  Its just that none of my 11g machines have 3338.  I should ask if your machines have 5070.

                     

                    (4) So assume for the moment that 5070 is working fine (should probably test that).  So right now I got some 11gR2 boxes without 5070.  How do I get myself that 5070 short of a reinstall of spatial?  I mean sure I can hand copy the SQL statements but then after doing that do I then have support for that 5070? 

                     

                    The whole thing seems kind of nebulous to me about who has what and why and from where they got what they have and how they get what they don't have.  Over on PostGIS the SRIDs are (by design I think) a kind of free-for-all.  But heck that is PostGIS, no support contracts there.  ESRI on the other hand seems to provide a consistent "stable" of projections that seem tied to the given software release (note the confusing name change for NAD83 in 10.2)  and can't be messed with (you can always add your own).

                     

                    It almost seems like we need some kind of audit script that would scrape the Oracle SRIDs into WKT that you could then diff against some kind of "master" list that may or may not exist.  For example, some bloke comes along and reads this thread a year from now, how will they know if their stock 3338 has been fixed or not?

                     

                    Cheers,

                     

                    Paul

                    • 7. Re: Transform issue, Alaska Albers
                      spiderman21

                      Sorry Paul,

                      Didn't see your last message until now.

                       

                      We are asking a lot of the same questions ourselves.  We are pretty sure we created the entry in 11g for 3338 (possibly with Oracle's help). This would only be the entry in the mdsys.SDO_COORD_REF_SYS table. Which from what we see probably means that our queries (across the date line) that required a transformation never ever worked and went unnoticed.  Fact is, we just don't have a lot of data out there.

                       

                      Our 11g install did not have 3338 nor 5070.

                       

                      Currently we have the SR in and it has finally made its way to the proper person so it seems.  They are looking at this thread as a reference and using the example as a test case.  Looks like they are going to submit a bug on our behalf.

                       

                      Will let you know what happens.

                       

                      Peter

                      • 8. Re: Transform issue, Alaska Albers
                        Stefan Jager

                        Hi Paul,

                         

                        For what it's worth, here's my 2 cents on this. I have some experience with custom coordinate systems with a client that worked very close with Oracle support (because they were always pushing the boundaries of what Spatial could do ):

                        Paul Dziemiela wrote:

                        (2) Now that you have "fixed" your stock 3338, how does that affect support for this SRID?  If some other user comes along in your organization with an SR about this now altered 3338, how does that work?  How does Oracle know or not know that a given SRID has been altered?

                        They don't. If you move your data to another database without your mods, you're SOL. If you create a dump of your data to supply to someone else who does not have your SRID, you're SOL. Which is why I tend to avoid using custom or modified SRID's, especially if it is data that is also to be delivered to external users. As far as support goes: they deliver the option of modifying your SRID's, and that they will support, no more no less. So it is your own responsibility to make sure your custom SRID is in every database where it is needed (something that is often overlooked, believe me).

                        Paul Dziemiela wrote:

                         

                        (3) Does anyone have an idea how new SRIDs come along?  My production 11g boxes were upgraded from 10g (and from 9i before that).  I get the impression that there is a kind of "universe" of stock SRIDs over in Reston that "come along" perhaps with fresh installs but not (?) from upgrades?  I believe you that you got 3338 on 11gR2 from some kind of stock install.  Its just that none of my 11g machines have 3338.  I should ask if your machines have 5070.

                        According to the documentation (Coordinate Systems (Spatial Reference Systems)), Oracle loosely follows the EPSG. So if EPSG decides a new coordinate system is needed, Oracle will most likely follow. The EPSG list plus Oracle's additions and modifications (the couple of "local" SRID', for example) is probably indeed maintained in Reston. These will arrive with a new install, but I have no experience if any changes/additions come over with an upgrade. I suppose it also depends on how you do your upgrade. I usually do a clean install then move everything over through a DB link, let the old version shadowrun for a while, then kill it after the new one has been up and running for a while.

                         

                        Cheers,

                        Stefan

                        • 9. Re: Transform issue, Alaska Albers
                          spiderman21

                          Paul,

                          Oracle has created an SR for the issue: 3-9311607651 with a level 3... whatever that means.  Guess we will find out where that leads.

                           

                          Peter