Switch coordinates (X with Y) in SDO_ORDINATE_ARRAY

I have a spatial data in my Oracle 11g table (a column with SDO_GEOMETRY as object type). My data is proprietary and I cannot show you them, but I will use some random data, it doesn't matter in this case. The table and its data has been created by external application and I cannot change the way they are written to the database.

The problem is that coordinates in SDO_ORDINATE_ARRAY are switched - all X values are where Y values should be and vice versa. Take a look at this example:

This is what I get:

SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)

And this is what I need (switching X and Y values in SDO_ORDINATE_ARRAY):

SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(1,5, 1,8, 6,8, 7,5, 1,5)

What I am looking for is a SELECT statement that returns a column with SDO_GEOMETRY from the example above, but with switched X and Y values in SDO_ORDINATE_ARRAY. I don't need to update the table because I want to send correct data to another external application.

I will be very thankful for pointing me any directions.

Solution

The following function will do what you want:

create or replace function swap_ordinates (g_in sdo_geometry)
return sdo_geometry
is
  g_out sdo_geometry;
  i integer;
begin
  -- Check input geometry: we only work on 2D shapes
  if substr(g_in.sdo_gtype,1,1) <> 2 then
    raise_application_error (-20001,'Geometry must be 2D');
  end if;
  -- Initialize output geometry
  g_out := g_in;
  -- Swap ordinates in sdo_point
  if g_in.sdo_point is not null then
    g_out.sdo_point.x := g_in.sdo_point.y;
    g_out.sdo_point.y := g_in.sdo_point.x;
  end if;
  -- Copy ordinates, swapping X and Y
  if g_in.sdo_ordinates is not null then
    for i in 1..g_in.sdo_ordinates.count/2 loop
      g_out.sdo_ordinates ((i-1)*2+1) := g_in.sdo_ordinates ((i-1)*2+2); -- Y -> X
      g_out.sdo_ordinates ((i-1)*2+2) := g_in.sdo_ordinates ((i-1)*2+1); -- X -> Y
    end loop;
  end if;
  -- Return fixed geometry
  return g_out;
end;
/
show errors

It takes a geometry object as input and returns a new one with the X and Y ordinates swapped. It also works for points (by swapping the X and Y in the SDO_TYPE property).

Note that it only works for plain 2D geometries. If the geometry is 3D or LRS (or both), then it fails with an exception.

Generalizing the function to work with 3D or more is left as an exercise to the reader.