Oracle Spatial - SRID cs_transform

From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 7 Mar 2011 11:44:19 -0300
Hello,

 I have  x=-46.627535509279 and y=-23.5411170208655 as coordinates.
 I need to convert its from 4326 to an equivalent ESRI SRID that is 102100.

 Oracle does not have 102100 it in the cs_srs view, so I need to choose one.

 I got a value using the sql statment below and with SRID 54004, but the X
result is not equal.
 The Y value is near and I think that it I can use it.
 But the x result has difference.

Look:

select
sdo_cs.transform(
sdo_geometry(2001,4326,
sdo_point_type(-46.627535509279, -23.5411170208655, null),
null,null),54004) "Forth with rule"
from dual;

-- Query result
X= -5190553,50983822
Y= -2680530,25761921

-- The expected value
From wkid = 102100:
X= -5190553.509838225
Y= -2697590.0847436045

The ESRI definition for SRID 102100 is :
WGS_1984_Web_Mercator_Auxiliary_Sphere.
I query in the cs_srs.wktext and there is no text like it.

I also did the following pl/sql block in order to identify the SRID that
should be a candidate:

DECLARE
CURSOR c1
IS
SELECT x.srid,
       x.WKTEXT
  FROM cs_srs x
 WHERE wktext like 'PROJCS%'
and srid NOT IN (2314);
v_sql      VARCHAR2(4000);
v_geometry SDO_GEOMETRY;
BEGIN
DELETE FROM ERI_TRANSFORM;
COMMIT;
FOR l IN c1
  LOOP
      v_geometry:= NULL;

      v_sql := 'select sdo_cs.transform(sdo_geometry
                 (2001,4326, sdo_point_type(-46.627535509279,
-23.5411170208655, null),
                  null,null),' || l.srid || ') from dual';

DBMS_OUTPUT.PUT_LINE (V_SQL);

      BEGIN
        EXECUTE IMMEDIATE v_sql INTO v_geometry;
        insert into ERI_TRANSFORM (SRID, GEOMETRY)
         VALUES (L.SRID, V_GEOMETRY);
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ('ERRO PARA : ' || L.SRID);
      END;
  END LOOP;
END;
/

I got three SRID`s:

Mercator    49155 (based on NAD27 datum) --> I cannot use it.
Miller Cylindrical    155648 (based on NAD27 datum) --> I cannot use it.
World Mercator    54004 (based on WGS84 datum) --> The y values has
different result

Doubts:

1.) Is there any way to create an SRID in order to reproduce the expected
value ?
2.) Is there any other way to convert SRID doing code instead using
cs_transform oracle command ?

Regards
Eriovaldo
Oracle Spatial - SRID cs_transform

Other related posts:
