1 2 3 Previous Next 34 Replies Latest reply: Apr 16, 2010 3:26 AM by 765578 RSS

    Support for Google Maps Projection 900913

    Paul Dziemiela
      Hello,
      This is a question similar to the guy asking about the Winkely projection earlier this week. Do we need to submit an enhancement request to get the rather odd Google Maps/Virtual Earth spherical mercator projection added to Oracle Spatial? Its an EPSG registered projection (or maybe they are working on being a registered projection) as code 900913.

      I would think I woudn't be the only one wanting to serve out some data in this projection.

      I found some folks trying to create their own here: Oracle 10g R2 and MS Virtual Earth projection but its unclear if they ever succeeded.

      How and when do new projection SRIDs get added? I looked at two boxes running 10.2.0.3 and 10.2.0.4 and the SDO_CS_CRS tables seem exactly the same so nothing was added recently I suppose.

      Thanks,

      Paul
        • 1. Re: Support for Google Maps Projection 900913
          Paul Dziemiela
          Hello,
          It looks as if EPSG is now including the Google Maps/Virtual Earth projection as EPSG 3785.

          See here:
          http://www.iter.dk/post/2008/05/SphericalWeb-Mercator-EPSG-code-3785.aspx

          Does this make it more likely that Oracle will support this projection with a stock SRID? Should we submit an SR for this or will Oracle pick up the new projections as a matter of course?

          Cheers,
          Paul
          • 2. Re: Support for Google Maps Projection 900913
            mhorhamm-Oracle
            I'll file an enhancement request for this, and let you know.
            • 3. Re: Support for Google Maps Projection 900913
              mhorhamm-Oracle
              It's enhancement request 7047527.
              • 4. RE: Support for EPSG 3785 Projection (Google Maps/Virtual Earth)
                Paul Dziemiela
                Hey folks,

                Got a response on the SRID enhancement for the Google Maps Mercator projects today from metalink. So now we just need to wait for a patch to come out.

                Cheers,
                Paul


                UPDATE
                ======
                Hi Paul,

                The enhancement request (bug# 7113148) to add the new EPSG projection 3785 has been implemented an
                d will be included in the future 11.2 release.
                We will also have this included in the later patchsets for the 10.2 and 11.1 releases.

                Kind regards,
                Klaas de Graaf
                Global Customer Services
                • 5. Re: RE: Support for EPSG 3785 Projection (Google Maps/Virtual Earth)
                  514256
                  Could someone from Oracle perhaps post the SQL inserts that need to be made to SDO_COORD_REF_SYSTEM and associated tables to enable this projection?
                  • 6. Re: RE: Support for EPSG 3785 Projection (Google Maps/Virtual Earth)
                    269171
                    I think this is it:

                    Please note the SRID 54004 is the SRID that I believe will used by Oracle.


                    -- Latitude of natural origin
                    UPDATE MDSYS.SDO_COORD_OP_PARAM_USE
                    SET LEGACY_PARAM_NAME = 'Standard_Parallel_1'
                    WHERE (COORD_OP_METHOD_ID = 9804 AND PARAMETER_ID = 8801);
                    -- longitude of natural origin
                    UPDATE MDSYS.SDO_COORD_OP_PARAM_USE
                    SET LEGACY_PARAM_NAME = 'Central_Meridian'
                    WHERE (COORD_OP_METHOD_ID = 9804 AND PARAMETER_ID = 8802);
                    -- scale factor at natural origin
                    UPDATE MDSYS.SDO_COORD_OP_PARAM_USE
                    SET LEGACY_PARAM_NAME = 'Scale_Factor'
                    WHERE (COORD_OP_METHOD_ID = 9804 AND PARAMETER_ID = 8805);
                    -- false easting
                    UPDATE MDSYS.SDO_COORD_OP_PARAM_USE
                    SET LEGACY_PARAM_NAME = 'False_Easting'
                    WHERE (COORD_OP_METHOD_ID = 9804 AND PARAMETER_ID = 8806);
                    -- false northing
                    UPDATE MDSYS.SDO_COORD_OP_PARAM_USE
                    SET LEGACY_PARAM_NAME = 'False_Northing'
                    WHERE (COORD_OP_METHOD_ID = 9804 AND PARAMETER_ID = 8807);

                    INSERT INTO sdo_coord_ops (
                    coord_op_id,
                    coord_op_name,
                    coord_op_type,
                    source_srid,
                    target_srid,
                    coord_tfm_version,
                    coord_op_variant,
                    coord_op_method_id,
                    UOM_ID_SOURCE_OFFSETS,
                    UOM_ID_TARGET_OFFSETS,
                    information_source,
                    data_source,
                    show_operation,
                    is_legacy,
                    legacy_code,
                    reverse_op,
                    is_implemented_forward,
                    is_implemented_reverse)
                    VALUES (
                    54004,
                    'World Mercator',
                    'CONVERSION',
                    null,
                    null,
                    '',
                    null,
                    9804,
                    null,
                    null,
                    null,
                    null,
                    1,
                    'FALSE',
                    null,
                    1,
                    1,
                    1);

                    insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
                    COORD_OP_ID,
                    COORD_OP_METHOD_ID,
                    PARAMETER_ID,
                    PARAMETER_VALUE,
                    PARAM_VALUE_FILE_REF,
                    UOM_ID)
                    VALUES (
                    54004,
                    9804,
                    8801, -- Latitude of natural origin
                    0,
                    NULL,
                    9102);

                    insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
                    COORD_OP_ID,
                    COORD_OP_METHOD_ID,
                    PARAMETER_ID,
                    PARAMETER_VALUE,
                    PARAM_VALUE_FILE_REF,
                    UOM_ID)
                    VALUES (
                    54004,
                    9804,
                    8802, -- longitude of natural origin
                    0,
                    NULL,
                    9102);

                    insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
                    COORD_OP_ID,
                    COORD_OP_METHOD_ID,
                    PARAMETER_ID,
                    PARAMETER_VALUE,
                    PARAM_VALUE_FILE_REF,
                    UOM_ID)
                    VALUES (
                    54004,
                    9804,
                    8805, -- scale factor at natural origin
                    1,
                    NULL,
                    9201);

                    insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
                    COORD_OP_ID,
                    COORD_OP_METHOD_ID,
                    PARAMETER_ID,
                    PARAMETER_VALUE,
                    PARAM_VALUE_FILE_REF,
                    UOM_ID)
                    VALUES (
                    54004,
                    9804,
                    8806, -- false easting
                    0,
                    NULL,
                    9001);

                    insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
                    COORD_OP_ID,
                    COORD_OP_METHOD_ID,
                    PARAMETER_ID,
                    PARAMETER_VALUE,
                    PARAM_VALUE_FILE_REF,
                    UOM_ID)
                    VALUES (
                    54004,
                    9804,
                    8807, -- false northing
                    0,
                    NULL,
                    9001);

                    INSERT INTO sdo_coord_ref_system (
                    srid,
                    coord_ref_sys_name,
                    coord_ref_sys_kind,
                    coord_sys_id,
                    datum_id,
                    geog_crs_datum_id,
                    source_geog_srid,
                    projection_conv_id,
                    cmpd_horiz_srid,
                    cmpd_vert_srid,
                    information_source,
                    data_source,
                    is_legacy,
                    legacy_code,
                    legacy_wktext,
                    legacy_cs_bounds,
                    is_valid,
                    supports_sdo_geometry)
                    VALUES (
                    54004,
                    'World Mercator',
                    'PROJECTED',
                    4499,
                    null,
                    6326,
                    4326,
                    54004,
                    Null,
                    Null,
                    null,
                    null,
                    'FALSE',
                    null,
                    null,
                    null,
                    'TRUE',
                    'TRUE');
                    • 7. Re: RE: Support for EPSG 3785 Projection (Google Maps/Virtual Earth)
                      514256
                      Thanks. This projection seems to be close, but it's shifted about 15 km to the south relative to other EPSG 3785 (900913) data in Northern Europe. Maybe this is because we are missing the datum and ellipsoid used by EPSG 3785?

                      Also, does anyone know when the aforementioned patch is going to be released?
                      • 8. Re: RE: Support for EPSG 3785 Projection (Google Maps/Virtual Earth)
                        122352
                        Exactly.

                        Google Map, Virtual Earth and Yahoo Map uses a similar tile system with small variation. The Same tile set can be used in all three APIs with simple conversion.

                        The actual Coordinate system is Sphere_Mercator (53004) instead of World_Mercator (54004).

                        Some small gaps between tiles maybe visible at smaller scale if the projection is not set correctly.

                        We hope for the EPSG 3785 (900913) data scripts.

                        Regards,
                        Friedhold
                        • 9. Re: RE: Support for EPSG 3785 Projection (Google Maps/Virtual Earth)
                          269171
                          Does anyone have the SQL insert statement's for Sphere_Mercator (53004) ?
                          • 10. Re: RE: Support for EPSG 3785 Projection (Google Maps/Virtual Earth)
                            vr*446726*ik
                            Hi,
                            could you describe how did you do the tests and what coordinates have you tested?

                            The coordinate system conversion must be configured separately by command:
                            (this is copied from different coordinate system definition)
                            TFM_PLAN(SDO_TFM_CHAIN(8307, 1000000000, 1000003, 1000002, 1000002)),
                            When transforming between 8307 and 1000002, then transform first via SRID 1000003 (using no-op operation 1000000000), and then to 1000002, using projection operation 1000002.

                            Here is my test:
                            -700774.6752209 7055443.457852785
                            -6.295166 53.389881

                            select
                            sdo_cs.transform(
                            SDO_GEOMETRY(
                            2001,
                            1000002,
                            SDO_POINT_TYPE(
                            -700774.6752209459,
                            7055443.457852785,
                            NULL),
                            NULL,
                            NULL),
                            8307)
                            from
                            dual;

                            Results:

                            SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,1000002,SDO_POINT_TYPE(-700774.6752209459,705
                            --------------------------------------------------------------------------------
                            SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-6.295166, 53.3898813, NULL), NULL, NULL
                            )


                            And here is the coordinate system:
                            delete from sdo_coord_ref_system where srid = 1000002;
                            delete from sdo_coord_op_param_vals where coord_op_id = 1000002;
                            delete from sdo_coord_ops where coord_op_id = 1000002;
                            delete from sdo_coord_ref_system where srid = 1000003;
                            delete from sdo_datums where datum_id = 1000003;
                            delete from sdo_ellipsoids where ellipsoid_id = 1000003;

                            insert into MDSYS.SDO_ELLIPSOIDS (
                            ELLIPSOID_ID,
                            ELLIPSOID_NAME,
                            SEMI_MAJOR_AXIS,
                            UOM_ID,
                            INV_FLATTENING,
                            SEMI_MINOR_AXIS,
                            INFORMATION_SOURCE,
                            DATA_SOURCE,
                            IS_LEGACY,
                            LEGACY_CODE)
                            VALUES (
                            1000003,
                            'Sphere_ArcSDE',
                            6378137.0,
                            9001,
                            100000000,
                            NULL,
                            null,
                            null,
                            'FALSE',
                            null);

                            insert into MDSYS.SDO_DATUMS (
                            DATUM_ID,
                            DATUM_NAME,
                            DATUM_TYPE,
                            ELLIPSOID_ID,
                            PRIME_MERIDIAN_ID,
                            INFORMATION_SOURCE,
                            DATA_SOURCE,
                            SHIFT_X,
                            SHIFT_Y,
                            SHIFT_Z,
                            ROTATE_X,
                            ROTATE_Y,
                            ROTATE_Z,
                            SCALE_ADJUST,
                            IS_LEGACY,
                            LEGACY_CODE)
                            VALUES (
                            1000003,
                            'Sphere_ArcSDE',
                            'GEODETIC',
                            1000003,
                            8901,
                            null,
                            null,
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            'FALSE',
                            NULL);

                            insert into MDSYS.SDO_COORD_REF_SYSTEM (
                            SRID,
                            COORD_REF_SYS_NAME,
                            COORD_REF_SYS_KIND,
                            COORD_SYS_ID,
                            DATUM_ID,
                            SOURCE_GEOG_SRID,
                            PROJECTION_CONV_ID,
                            CMPD_HORIZ_SRID,
                            CMPD_VERT_SRID,
                            INFORMATION_SOURCE,
                            DATA_SOURCE,
                            IS_LEGACY,
                            LEGACY_CODE,
                            LEGACY_WKTEXT,
                            LEGACY_CS_BOUNDS)
                            VALUES (
                            1000003,
                            'Sphere_ArcSDE',
                            'GEOGRAPHIC2D',
                            6422,
                            1000003,
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            null,
                            null,
                            'FALSE',
                            NULL,
                            NULL,
                            NULL);

                            insert into MDSYS.SDO_COORD_OPS (
                            COORD_OP_ID,
                            COORD_OP_NAME,
                            COORD_OP_TYPE,
                            SOURCE_SRID,
                            TARGET_SRID,
                            COORD_TFM_VERSION,
                            COORD_OP_VARIANT,
                            COORD_OP_METHOD_ID,
                            UOM_ID_SOURCE_OFFSETS,
                            UOM_ID_TARGET_OFFSETS,
                            INFORMATION_SOURCE,
                            DATA_SOURCE,
                            SHOW_OPERATION,
                            IS_LEGACY,
                            LEGACY_CODE,
                            REVERSE_OP,
                            IS_IMPLEMENTED_FORWARD,
                            IS_IMPLEMENTED_REVERSE)
                            VALUES (
                            1000002,
                            'Mercator_Sphere_ArcSDE',
                            'CONVERSION',
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            9805,
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            1,
                            'FALSE',
                            NULL,
                            1,
                            1,
                            1);


                            -- Now,let's configure the projection parameters:
                            -- 8823: Latitude_Of_Origin
                            insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
                            COORD_OP_ID,
                            COORD_OP_METHOD_ID,
                            PARAMETER_ID,
                            PARAMETER_VALUE,
                            PARAM_VALUE_FILE_REF,
                            UOM_ID)
                            VALUES (
                            1000002,
                            9805,
                            8823,
                            0.0,
                            NULL,
                            9102);
                            -- 8802: Central_Meridian
                            insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
                            COORD_OP_ID,
                            COORD_OP_METHOD_ID,
                            PARAMETER_ID,
                            PARAMETER_VALUE,
                            PARAM_VALUE_FILE_REF,
                            UOM_ID)
                            VALUES (
                            1000002,
                            9805,
                            8802,
                            0.0,
                            NULL,
                            9102);
                            -- 8806: False_Easting
                            insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
                            COORD_OP_ID,
                            COORD_OP_METHOD_ID,
                            PARAMETER_ID,
                            PARAMETER_VALUE,
                            PARAM_VALUE_FILE_REF,
                            UOM_ID)
                            VALUES (
                            1000002,
                            9805,
                            8806,
                            0.0,
                            NULL,
                            9001);
                            -- 8807: False_Northing
                            insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
                            COORD_OP_ID,
                            COORD_OP_METHOD_ID,
                            PARAMETER_ID,
                            PARAMETER_VALUE,
                            PARAM_VALUE_FILE_REF,
                            UOM_ID)
                            VALUES (
                            1000002,
                            9805,
                            8807,
                            0.0,
                            NULL,
                            9001);

                            insert into MDSYS.SDO_COORD_REF_SYSTEM (
                            SRID,
                            COORD_REF_SYS_NAME,
                            COORD_REF_SYS_KIND,
                            COORD_SYS_ID,
                            DATUM_ID,
                            SOURCE_GEOG_SRID,
                            PROJECTION_CONV_ID,
                            CMPD_HORIZ_SRID,
                            CMPD_VERT_SRID,
                            INFORMATION_SOURCE,
                            DATA_SOURCE,
                            IS_LEGACY,
                            LEGACY_CODE,
                            LEGACY_WKTEXT,
                            LEGACY_CS_BOUNDS,
                            GEOG_CRS_DATUM_ID)
                            VALUES (
                            1000002,
                            'Mercator_Sphere_ArcSDE',
                            'PROJECTED',
                            4530,
                            NULL,
                            1000003,
                            1000002,
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            'FALSE',
                            NULL,
                            NULL,
                            NULL,
                            1000003);

                            -- DEFINE TRANSFORMATION to avoid sphere -> elipsoid transformation
                            call sdo_cs.create_pref_concatenated_op(
                            300,
                            'CONCATENATED OPERATION',
                            TFM_PLAN(SDO_TFM_CHAIN(8307, 1000000000, 1000003, 1000002, 1000002)),
                            NULL);

                            I got same results with cstocs util with this configuration:
                            cs2cs command to verify Mercator conversions

                            1. to lat long

                            cs2cs proj=merc a=6378137 b=6378137 lat_ts=0.0 lon_0=0.0 x_0=0.0 y_0=0 k=1.0 units=m nadgrids=@null +no_defs -f "%.6f"

                            1. to Spherical Mercator

                            cs2cs proj=latlong datum=WGS84 to proj=merc a=6378137 b=6378137 lat_ts=0.0 lon_0=0.0 x_0=0.0 y_0=0 k=1.0 units=m nadgrids=@null no_defs
                            • 11. Re: RE: Support for EPSG 3785 Projection (Google Maps/Virtual Earth)
                              vr*446726*ik
                              I did some test.
                              Does anybody made any similar test or does anybody to recheck my tests? Thanks.
                              All test has been done by: proj.4 tests Rel. 4.4.9, 29 Oct 2004(cs2cs utility)
                              Some test has been verified through Proj.4 4.6(through Python pyproj)
                              And Oracle 10gR2 patch 4.
                              The original 4326 coordinates has been captured by Google maps, I took the coordinates from link. It is fine because I don't have any database with points in Mercator and in epsg:4326

                              name               |epsg:4326(x,y order)          |mercator(x,y order)
                              -------------------------------------------------------------------------------------
                              Nordkapp(Sweden)      25.778046 71.162939          2869598.95 11458216.29
                              Cape Town          18.367767 -33.920002          2044690.47 -4018065.30
                              Dublin                -6.295166 53.389881          -700774.6752209 7055443.457852785
                              California          -121.5527 37.579413          -13531184.49327148 4520180.10404296
                              Puerto Montt(Chile)     -72.95831 -41.460096          -8121681.92 -5080444.43
                              Melbourne(Australia)     145.00476 -37.816836          16141856.05 -4553583.10
                              Astana(Kazakhstan)     71.448255 51.179074          7953583.36 6653031.18
                              Dyjakovice(Czech Rep.) 16.303046 48.772666          1814846.78 6236375.24


                              Oracle tests:

                              SRID 43261 is same as 8307 but without ellipsoid -> spheroid shift

                              select sdo_cs.transform(
                              SDO_GEOMETRY(
                              2001,
                              54004,
                              SDO_POINT_TYPE(
                              (-700774.6752209459),
                              (7055443.457852785),
                              NULL),
                              NULL,
                              NULL),
                              43261)
                              from dual;

                              SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,54004,SDO_POINT_TYPE((-700774.6752209459),(70
                              --------------------------------------------------------------------------------
                              SDO_GEOMETRY(2001, 43261, SDO_POINT_TYPE(-6.295166, 53.3897482, NULL), NULL, NUL
                              L)

                              select sdo_cs.transform(
                              SDO_GEOMETRY(
                              2001,
                              54004,
                              SDO_POINT_TYPE(
                              (1814846.78),
                              ( 6236375.24),
                              NULL),
                              NULL,
                              NULL),
                              43261)
                              from dual;

                              SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,54004,SDO_POINT_TYPE((1814846.78),(6236375.24
                              --------------------------------------------------------------------------------
                              SDO_GEOMETRY(2001, 43261, SDO_POINT_TYPE(16.303046, 48.7725455, NULL), NULL, NUL
                              L)

                              updated California mercator result
                              • 12. Re: RE: Support for EPSG 3785 Projection (Google Maps/Virtual Earth)
                                514256
                                I don't have any quantified results yet, but this coordinate system transformation generates a good WMS overlay on other data in the Google/VE projection.
                                • 13. Re: Support for Google Maps Projection 900913
                                  122352
                                  Hi,

                                  I have tried to verify the projection 54004 used in Oracles great GeoCoding Demo:

                                  http://elocation.oracle.com/elocation/ajax/

                                  many thanks to share with us !!

                                  @Zdenek:
                                  The transformation is on the Oracle Maps client side:
                                   var loc = new MVSdoGeometry.createPoint(x,y, 8307);
                                      var m   = mapview.transformGeom(loc, 54004);
                                      var i   = mapview.transformGeom(m, 8307);
                                  and I get the results from your points:
                                  8307 : 25.778046 / 71.162939 | 54004 : 2869598.9543655827 / 11417724.416751824 | inverse 8307 : 25.778046 / 71.16353542051434
                                  8307 : 18.367767 / -33.920002 | 54004 : 2044690.469449494 / -3994221.934505624 | inverse 8307 : 18.367767 / -33.92008343671353
                                  8307 : -6.295166 / 53.389881 | 54004 : -700774.6735791287 / 7021120.155795602 | inverse 8307 : -6.295166 / 53.39044629876871
                                  8307 : -121.5527 / 37.579413 | 54004 : -13531184.668547545 / 4494118.907455366 | inverse 8307 : -121.5527 / 37.57958565969177
                                  8307 : -72.95831 / -41.460096 | 54004 : -8121681.918337799 / -5052146.668173538 | inverse 8307 : -72.95831 / -41.460372386875115
                                  8307 : 145.00476 / -37.816836 | 54004 : 16141856.045800844 / -4527381.485583959 | inverse 8307 : 145.00476 / -37.81701486313872
                                  8307 : 71.448255 / 51.179074 | 54004 : 7953583.364667962 / 6619719.8643950345 | inverse 8307 : 71.448255 / 51.179595275682516
                                  8307 : 16.303046 / 48.772666 | 54004 : 1814846.7790993154 / 6204221.664135924 | inverse 8307 : 16.303046000000002 / 48.77313278316515
                                  @Ronan:
                                  Did you try the 54004 transformation with the SRID's in a 11g database ??

                                  I don't have the 53004 parameters or SRID inserts but you can found 53004 WKT's in :

                                  http://52north.org/cgi-bin/viewvc.cgi/trunk/Sources/Engine/DataExchange/gdal_data/esri_extra.wkt?view=markup&root=ilwis&pathrev=32

                                  http://maps.charmeck.org/gmap_ags/sphere_mercator.xml

                                  Hope it helps,
                                  Friedhold
                                  • 14. Re: RE: Support for EPSG 3785 Projection (Google Maps/Virtual Earth)
                                    514256
                                    I was able to run vrablik's script in 11g and successfully transform data between 8307 and 1000002. I was not successful when using 10gR2. I still saw the y shift. It looks like the TFM_PLAN was not being properly applied.
                                    1 2 3 Previous Next