使用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)不一致造成的