使用ST_Transform時報“ORA-20603: Spatial References are not compatible”

接到一使用者的問題，再使用ST_Transform時報“ORA-20603: Spatial References are not compatible”。

檢視ST_Transform時有一個關鍵的地方：

“SRIDs 102 and 105 have to exist in the ST_SPATIAL_REFERENCES view, and both must have the same geographic datum.”

也就是說，在實作轉換的過程中，兩個投影檔案必須要有一樣的橢球體。

我們檢視st_spatial_references表中的SRID資訊

SQL> select srid,sr_name from sde.st_spatial_references order by srid;

SRID SR_NAME

---------- ------------------------------------------------------------

0 GCS_WGS_1984

2 GCS_Abidjan_1987

3 NAD_1927_StatePlane_Alabama_East_FIPS_0101

4 Unknown_CoordSys

5 Xian_1980_3_Degree_GK_CM_102E

6 Unknown_CoordSys

7 Unknown_CoordSys

8 GCS_WGS_1984

9 GCS_WGS_1984

10 Unknown_CoordSys

11 Unknown_CoordSys

SRID SR_NAME

---------- ------------------------------------------------------------

12 Unknown_CoordSys

13 NAD_1983_StatePlane_Ohio_South_FIPS_3402_Feet

14 Pulkovo_1942_GK_Zone_18

15 Xian_1980_3_Degree_GK_Zone_39

16 GCS_WGS_1984

17 Xian_1980_3_Degree_GK_CM_111E

18 Xian_1980_3_Degree_GK_CM_120E

19 Xian_1980_3_Degree_GK_Zone_39

20 NAD_1983_StatePlane_California_II_FIPS_0402_Feet

21 NAD_1983_StatePlane_California_II_FIPS_0402_Feet

22 NTF_Lambert_II

SRID SR_NAME

---------- ------------------------------------------------------------

23 Deutsches_Hauptdreiecksnetz_Transverse_Mercator

24 GCS_Beijing_1954

25 Beijing_1954_GK_Zone_20N

26 Beijing_1954_GK_Zone_20N

27 NAD_1983_StatePlane_North_Carolina_FIPS_3200_Feet

28 Xian_1980_3_Degree_GK_CM_114E

29 Xian_1980_3_Degree_GK_CM_102E

30 Xian_1980_3_Degree_GK_CM_105E

31 Unknown_CoordSys

32 Xian_1980_3_Degree_GK_CM_87E

33 Xian_1980_3_Degree_GK_CM_84E

SRID SR_NAME

---------- ------------------------------------------------------------

34 NAD_1927_StatePlane_Alabama_East_FIPS_0101

35 Africa_Albers_Equal_Area_Conic

36 GCS_Batavia

37 GCS_Krasovsky_1940

38 Beijing_1954_3_Degree_GK_CM_114E

39 Unknown_CoordSys

40 Unknown_CoordSys

41 Suzhou_1954_3_Degree_GK_CM_120E

42 NAD_1983_StatePlane_Vermont_FIPS_4400

43 Xian_1980_3_Degree_GK_CM_111E

44 Lat Long WGS84

SRID SR_NAME

---------- ------------------------------------------------------------

45 Xian_1980_3_Degree_GK_CM_117E

46 Unknown_CoordSys

47 Xian_1980_3_Degree_GK_CM_117E

48 Unknown_CoordSys

49 Unknown_CoordSys

50 Unknown_CoordSys

51 GCS_North_American_1983

52 NAD_1983_Texas_Statewide_Mapping_System

53 Unknown_CoordSys

54 Unknown_CoordSys

55 GCS_Xian_1980

SRID SR_NAME

---------- ------------------------------------------------------------

56 GCS_Beijing_1954

57 Xian_1980_3_Degree_GK_Zone_29

58 NAD_1983_HARN_Adj_MN_Beltrami_North_Meters

59 GCS_Xian_1980

已選擇59行。 

我們進行相關測試

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(1 1)',59), 30) ) from dual ;

SDE.ST_ASTEXT(SDE.ST_TRANSFORM(SDE.ST_POINT('POINT(11)',59),30))

--------------------------------------------------------------------------------

EMPTY

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(1 1)',30), 59) ) from dual ;

SDE.ST_ASTEXT(SDE.ST_TRANSFORM(SDE.ST_POINT('POINT(11)',30),59))

--------------------------------------------------------------------------------

POINT ( 100.51305898 0.00000902)

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(1 1)',30), 56) ) from dual ;

ERROR:

ORA-20603: Spatial References are not compatible.

ORA-06512: 在 "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 788

ORA-06512: 在 "SDE.ST_GEOMETRY_OPERATORS", line 2577

未標明行 

56 GCS_Beijing_1954

30 Xian_1980_3_Degree_GK_CM_105E

橢球體不一緻

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(109.51305898 0.00000902)',59), 28) ) from dual;

SDE.ST_ASTEXT(SDE.ST_TRANSFORM(SDE.ST_POINT('POINT(109.513058980.00000902)',59),

--------------------------------------------------------------------------------

POINT ( 1.00019519 1.00046738) 

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(1 1)',18), 59) ) from dual

SDE.ST_ASTEXT(SDE.ST_TRANSFORM(SDE.ST_POINT('POINT(11)',18),59))

--------------------------------------------------------------------------------

POINT ( 115.51305898 0.00000902)

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(1 1)',59), 59) ) from dual

SDE.ST_ASTEXT(SDE.ST_TRANSFORM(SDE.ST_POINT('POINT(11)',59),59))

--------------------------------------------------------------------------------

POINT ( 1.00000000 1.00000000)

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(1 1)',55), 59) ) from dual

SDE.ST_ASTEXT(SDE.ST_TRANSFORM(SDE.ST_POINT('POINT(11)',55),59))

--------------------------------------------------------------------------------

POINT ( 1.00000000 1.00000000)

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(1 1)',8), 59) ) from dual ;

ERROR:

ORA-20603: Spatial References are not compatible.

ORA-06512: 在 "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 788

ORA-06512: 在 "SDE.ST_GEOMETRY_OPERATORS", line 2577

未標明行

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(1 1)',7), 59) ) from dual ;

ERROR:

ORA-20603: Spatial References are not compatible.

ORA-06512: 在 "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 788

ORA-06512: 在 "SDE.ST_GEOMETRY_OPERATORS", line 2577

未標明行 

 7 Unknown_CoordSys

 8 GCS_WGS_1984

------------------------------

 59 GCS_Xian_1980

橢球體不一緻

凡是出現該錯誤的都是相關的橢球體（datum)不一緻造成的