Oracle Spatial - SRID cs_transform

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Mon, 7 Mar 2011 11:44:19 -0300
Message-ID: <AANLkTi=ZFXdNinNAHgfsSQDCudxofAZsZwNnLWnOA4Ep_at_mail.gmail.com>

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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 07 2011 - 08:44:19 CST