SDO_CS.Transform Query is Returning Inaccurate Tranformation Results for SRID 3785

(Doc ID 1533978.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

SDO_CS.Transform(geometry,3785) where the srid for geometry is 8307 returns inaccurate results, e.g. -

select sdo_cs.transform(geometry,3785) geometry, LOC_MSTR_ID, RPA_TYP_DESC, OPRTNL_STTS_CD, ADDRESS, ADDR_LINE1, ZIP_CODE, CITY, COUNTY, STATE, COUNTRY, COUNT_RPA_UNIQ_ID FROM ( SELECT GEOMETRY, LOC_MSTR_ID, RPA_TYP_DESC, OPRTNL_STTS_CD, ADDRESS, ADDR_LINE1,ZIP_CODE, CITY, COUNTY, STATE, COUNTRY, COUNT_RPA_UNIQ_ID FROM RPDW_BUILDINGS_MV WHERE (RPA_TYP_DESC != 'ALL' OR RPA_TYP_DESC IS NULL) AND STATE = 'DC' AND (CITY != 'nocity' OR CITY IS NULL) AND (ZIP_CODE != 'nozipcode' OR ZIP_CODE IS NULL) )
where loc_mstr_id = '4500';

--RESULT---

MDSYS.SDO_GEOMETRY(2001,3785,MDSYS.SDO_POINT_TYPE(-8581000.09387,4713017.77382055,NULL),NULL,NULL) 4500 Building ACT 3801 Nebraska Ave NW, Washington, DC 20016 3801 NEBRASKA AVE NW 20016 WASHINGTON CITY OF WASHINGTON DC UNITED STATES 28

the Y coordinate in the result is inaccurate by almost 30 miles.

It returns correct results in 11.2.0.1.0.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms