This discussion is archived
7 Replies Latest reply: Jan 9, 2012 10:16 PM by 538825 RSS

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

538825 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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)
    538825 Newbie
    Currently Being Moderated
    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)
    538825 Newbie
    Currently Being Moderated
    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)
    538825 Newbie
    Currently Being Moderated
    Is there any way to count DISTANCE without transformation into 8307 ?
  • 5. Re: SDO_DISTANCE for different coordinate systems (SRID=8307 and SRID=3857)
    538825 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
  • 7. Re: SDO_DISTANCE for different coordinate systems (SRID=8307 and SRID=3857)
    538825 Newbie
    Currently Being Moderated
    Thanks, Paul,
    You are right.

Legend

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