Oracle Spatial transform

We have a table in an Oracle Spatial database containing polygons (lying in Austria) in Lambert projection (SRID=1000047), which we want to transform into WGS84 (SRID=8307). After performing

create table Table2 as (
  select ..., SDO_CS.TRANSFORM(geometry, 8307) as geometry, ...
from Table1)

the polygons in Table2 all lie practically on a single point north of Paris. What are we doing wrong?

Edit: As an example, the point [11.26555560027597,53.87928275206266] gets transformed to [2.3069645331040123,49.293822884973984]. I get the same results using SRID=4326 instead of 8307.

The sqlplus query select * from cs_srs where srid=100047 returns

PROJCS["LAMBERT",GEOGCS["GCS_MGI",DATUM["D_MGI",SPHEROID["Bessel_1841",6377397.1‌55,299.1528128]], PRIMEM["Greenwich",0],UNIT["Decimal Degree",0.017453292519943295]],PROJECTION["Lambert Conformal Conic"], PARAMETER["False_Easting",400000],PARAMETER["False_Northing",400000], PARAMETER["Central_Meridian",13.33333333333333],PARAMETER["Standard_Parallel_1",‌46], PARAMETER["Standard_Parallel_2",49],PARAMETER["Scale_Factor",1], PARAMETER["Latitude_Of_Origin",47.5],UNIT["Meter",1]]

Solution

Your source SRID (1000047) is not a standard EPSG code, neither is it any coordinate system that ships with any version of Oracle: it looks like one you defined yourself. Can you show us the definition (select * from cs_srs where srid=1000047) ?

Looking at your input (11.26555560027597,53.87928275206266) - that does not look like any projection. It looks to me like some geodetic coordinates, possibly on a different datum than WGS84.

The coordinate system definition you use is that of the standard SRID 31287:

PROJCS[
  "MGI / Austria Lambert",
  GEOGCS["MGI",
    DATUM["Militar-Geographische Institut",
      SPHEROID[
        "Bessel 1841",
        6377397.155,
        299.1528128,
        AUTHORITY["EPSG", "7004"]],
      TOWGS84[577.326, 90.129, 463.919, 5.137, 1.474, 5.297, 2.4232],
      AUTHORITY["EPSG", "6312"]],
    PRIMEM["Greenwich", 0.000000, AUTHORITY["EPSG","8901"]],
    UNIT["degree (supplier to define representation)", 0.0174532925199433, AUTHORITY["EPSG", "9122"]],
    AXIS["Lat", NORTH],
    AXIS["Long", EAST],
    AUTHORITY["EPSG", "4312"]],
  PROJECTION ["Lambert Conformal Conic"],
  PARAMETER ["Latitude_Of_Origin", 47.5],
  PARAMETER ["Central_Meridian", 13.3333333333333333],
  PARAMETER ["Standard_Parallel_1", 49.0],
  PARAMETER ["Standard_Parallel_2", 46.0],
  PARAMETER ["False_Easting", 400000.0],
  PARAMETER ["False_Northing", 400000.0],
  UNIT["metre", 1.0, AUTHORITY["EPSG", "9001"]],
  AXIS["X", NORTH],
  AXIS["Y", EAST],
  AUTHORITY["EPSG", "31287"]]

The main difference with yours is that yours is missing the datum shift parameters. The other difference is that the standard parallels are reversed: parallel 1 is 49 and parallel 2 is 46 vs. 46 and 49 in your definition.

Here is what I get when I transform the geometry you posted as example (encoded using 31287):

select sdo_cs.transform (
  sdo_geometry(2003,31287,null,sdo_elem_info_array(1, 1003, 1),sdo_ordinate_array(607205.274999979, 528729.87700098, 607052.849999979, 528254.154000983, 607080.702999979, 528224.753000982, 607098.889999979, 528220.193000982, 607113.807999979, 528225.393000979, 607272.533999979, 528720.85100098, 607269.772999979, 528724.96700098, 607205.274999979, 528729.87700098)), 
  4326
) 
from dual;

SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(16.1442004, 48.62389, 16.1419009, 48.6196637, 16.1422641, 48.6193904, 16.1425084, 48.6193435, 16.1427132, 48.6193854, 16.1451079, 48.623787, 16.1450725, 48.6238249, 16.1442004, 48.62389))

The result looks fine to me. Can you verify that it is ?

What exact version of Oracle do you run? SRID 31287 exists since 10gR2.

The simplest seems to me to use srid 31287 for your data instead of the custom 100047.