7 Replies Latest reply: Jan 10, 2012 12:16 AM by user535822 RSS

    SDO_DISTANCE for different coordinate systems (SRID=8307 and SRID=3857)

    user535822
      Why does the query return 2 unequal numbers?
      It counts distance between two exactly the same points, but using different coordinate systems

      SELECT
           SDO_GEOM.SDO_DISTANCE(
                SDO_GEOMETRY(     2001,     8307,     SDO_POINT_TYPE(27.3744901,53.871729, NULL),     NULL,     NULL),
                SDO_GEOMETRY(     2001,     8307,     SDO_POINT_TYPE(30.8066023,52.4506705, NULL),     NULL,     NULL),
                0.05,     'unit=KM') as Dist8307,
           SDO_GEOM.SDO_DISTANCE(
                sdo_cs.transform(MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(27.3744901,53.871729, NULL), NULL, NULL), 3857),
                sdo_cs.transform(MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(30.8066023,52.4506705, NULL), NULL, NULL), 3857),
                0.05,     'unit=KM') as Dist3857
      FROM dual;


      DIST8307 | DIST3857
      278,716792718778 | 464,327762196335

      Oracle DB 11.2.0.1.0

      Edited by: user535822 on Jan 5, 2012 2:28 PM
        • 1. Re: SDO_DISTANCE for different coordinate systems (SRID=8307 and SRID=3857)
          Fa
          I have the same version of oracle db installed. But when I use the following query, it returns nothing, which means there is no default srid (3857) in the database!
          select * FROM SDO_COORD_REF_SYS WHERE SRID=3857; 
          I suppose it ([url http://spatialreference.org/ref/sr-org/6864/ ]3857 ) is an EPSG cs, but this query returns null ,too!
          SELECT SDO_CS.MAP_EPSG_SRID_TO_ORACLE(3857) FROM DUAL; 
          • 2. Re: SDO_DISTANCE for different coordinate systems (SRID=8307 and SRID=3857)
            user535822
            There is one in my database.
            ... Ok, then use 3785
            They both (3785 and 3857) are "google" coordinate systems.

            SELECT
            SDO_GEOM.SDO_DISTANCE(
            SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(27.3744901,53.871729, NULL), NULL, NULL),
            SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(30.8066023,52.4506705, NULL), NULL, NULL),
            0.05, 'unit=KM') as Dist8307,
            SDO_GEOM.SDO_DISTANCE(
            sdo_cs.transform(MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(27.3744901,53.871729, NULL), NULL, NULL),'USE_SPHERICAL', 3785),
            sdo_cs.transform(MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(30.8066023,52.4506705, NULL), NULL, NULL),'USE_SPHERICAL', 3785),
            0.05, 'unit=KM') as Dist3785
            FROM dual;
            • 3. Re: SDO_DISTANCE for different coordinate systems (SRID=8307 and SRID=3857)
              user535822
              Some interesting facts:

              equator -
              SELECT
              SDO_GEOM.SDO_DISTANCE(
                 SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(0,0, NULL), NULL, NULL),
                 SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(1,0, NULL), NULL, NULL),
                 0.05, 'unit=KM') as Dist8307,
              SDO_GEOM.SDO_DISTANCE(
                 sdo_cs.transform(MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(0,0, NULL), NULL, NULL),'USE_SPHERICAL', 3785),
                 sdo_cs.transform(MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(1,0, NULL), NULL, NULL),'USE_SPHERICAL', 3785),
                 0.05, 'unit=KM') as Dist3785
              FROM dual;
              
              DIST8307               DIST3785               
              ---------------------- ---------------------- 
              111.319490793262       111.319490793274       
              Here is latitude 53 degrees
              SELECT
              SDO_GEOM.SDO_DISTANCE(
                 SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(0,53, NULL), NULL, NULL),
                 SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(1,53, NULL), NULL, NULL),
                 0.05, 'unit=KM') as Dist8307,
              SDO_GEOM.SDO_DISTANCE(
                 sdo_cs.transform(MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(0,53, NULL), NULL, NULL),'USE_SPHERICAL', 3785),
                 sdo_cs.transform(MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(1,53, NULL), NULL, NULL),'USE_SPHERICAL', 3785),
                 0.05, 'unit=KM') as Dist3785
              FROM dual;
              
              DIST8307               DIST3785               
              ---------------------- ---------------------- 
              67.1366830300055       111.31949079327  
              It counts like on the equator!
              • 4. Re: SDO_DISTANCE for different coordinate systems (SRID=8307 and SRID=3857)
                user535822
                Is there any way to count DISTANCE without transformation into 8307 ?
                • 5. Re: SDO_DISTANCE for different coordinate systems (SRID=8307 and SRID=3857)
                  user535822
                  Is there any way to count DISTANCE without transformation into SRID 8307?
                  • 6. Re: SDO_DISTANCE for different coordinate systems (SRID=8307 and SRID=3857)
                    Paul Dziemiela
                    Hi User 535822,

                    We all come to Oracle Spatial from a variety of backgrounds. Some folks have a GIS and Cartography background, other have more a programming or database admin background. Each has their own specialties and their own deficiencies to accommodate. The nature of distance on an ellipsoid verses distance on a cartesian projection is down in the fundamentals on the GIS side of things that this group usually does not discuss. I would make a plug to get yourself a copy of my ala mater's classic Elements Of Cartography
                    http://www.amazon.com/Elements-Cartography-Arthur-H-Robinson/dp/0471555797
                    and read up on chapter 4 on Earth-Map Relations.

                    Another read on the specific issues you are facing with web mercator distances might be
                    http://blogs.esri.com/Dev/blogs/arcgisserver/archive/2010/03/05/Measuring-distances-and-areas-when-your-map-uses-the-Mercator-projection.aspx
                    The example at this article does exactly what you are doing to measure distance, e.g. convert the measurement to WGS84 and return that distance. I am not sure why you wish to avoid doing this?

                    Fa raises a good point of just how you are using 3857 on 11.2.0.1. I believe 3785 (web mercator on a sphere) was added in 11.2.0.1 and I believe 3857 (web mercator on an very spherical ellipsoid) was added in 11.2.0.2. So that perplexes me. A related question is just how do we all know when a given EPSG code is added to Oracle spatial? I do not know of any reference anywhere showing the list of SRIDs included with spatial "out of the box" by version (great topic for a blog post).

                    Anyhow, hope that helps.

                    Cheers,
                    Paul