How to insert points coming from sub-query as an object of SDO_GEOMETRY in Oracle spatial database?

Question

Say, I have a table named buildings which could be created by the following query:

create table buildings(
  building_id number primary key,
  building_name varchar2(32),
  shape sdo_geometry
);

I can insert a rectangle into it by the following query:

insert into buildings values(
  4, -- index
  'Reading Room', -- building_name
  sdo_geometry(
    2003, --SDO_GTYPE: dltt - 2(2D)0(linear referencing)03(polygon)
    8307, --SDO_SRID: coordinate system
    null, --SDO_POINT: it is for point inserting, if the next two field = null, then it could not be null.
    sdo_elem_info_array( --SDO_ELEM_INFO:
    1, --SDO_STARTING_OFFSET: indicates from which index of the next param of SDO_GEOMETRY would be considered, starts from 1.
    1003, --SDO_ETYPE: 1(exterior, interior  - 2)003(this digits usually comes from SDO_GTYPE)
    3),  --SDO_INTERPRETATION: 1 - simple polygon, 2 - polygon connecting arcs, 3 - rectangle, 4 - circle etc.
    sdo_ordinate_array(
      24.916312, 91.832393,
      24.916392, 91.832678
    ) --SDO_ORDINATES: co-ordinates of the geometry
                              -- two corner points of the main diagonal
  )
);

Here, two geodetic points came from real data as an object of sdo_ordinate_array. The following two points are inserted directly in the above query:

1. 24.916312, 91.832393
2. 24.916392, 91.832678

Now, I want to insert these two points coming from two different sub-query.

Sub-queries would be like the following:

SELECT 180+SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.X, 
  180-SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.Y
  FROM buildings c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
  AND c.building_name = 'IICT';

So, the result of the query would be like:

         X          Y
---------- ----------
24.9181097 91.83097409 

How can I convert this result to comma separated value, like: 24.9181097, 91.83097409?

So that I could replace the following code:

sdo_ordinate_array(
    24.916312, 91.832393,
    24.916392, 91.832678
) --SDO_ORDINATES: co-ordinates of the geometry

with:

sdo_ordinate_array(
    (/*sub-query*/),
    (/*another-subquery*/)
) --SDO_ORDINATES: co-ordinates of the geometry

I had google it and explored several blogs but had no luck.

N.B.:

The title seemed inappropriate, but the straightforward versions of the sub-queries return the object of SDO_GEOMETRY. If you explored on oracle spatial queries, then it is clear to you that I just retrieve the value of X and Y from the returned object.

Answer 1

I found out a way to insert. There could exist efficient way but this one works fine.

sdo_ordinate_array(

    --this sub-query returns the Longitude of the first point
    (SELECT SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.X X
    FROM buildings c, user_sdo_geom_metadata m 
    WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
    AND c.building_name = 'IICT'), 

    --this sub-query returns the Latitude of the first point
    (SELECT SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.Y Y
    FROM buildings c, user_sdo_geom_metadata m 
    WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
    AND c.building_name = 'IICT'), 

    --this sub-query returns the Longitude of the second point
    (SELECT SDO_GEOM.SDO_POINTONSURFACE(c.shape, m.diminfo).SDO_POINT.X X 
    FROM buildings c, user_sdo_geom_metadata m 
    WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
    AND c.building_name = 'IICT'), 

    --this sub-query returns the Latitude of the second point
    (SELECT SDO_GEOM.SDO_POINTONSURFACE(c.shape, m.diminfo).SDO_POINT.Y Y 
    FROM buildings c, user_sdo_geom_metadata m 
    WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
    AND c.building_name = 'IICT') 

) --SDO_ORDINATES: co-ordinates of the geomentry

sdo_ordinate_array takes only points, not object of sdo_geometry. So, I have to get the Longitude and Latitude separately.

So, the complete insert query would be like the following:

insert into buildings values(
  4, -- index
  'Reading Room', -- building_name
   sdo_geometry(
    2003, --SDO_GTYPE: dltt - 2(2D)0(linear referencing)03(polygon)
    8307, --SDO_SRID: coordinate system
    null, --SDO_POINT: it is for point inserting, if the next two field = null, then it could not be null.
    sdo_elem_info_array( --SDO_ELEM_INFO:
    1, --SDO_STARTING_OFFSET: indicates from which index of the next param of SDO_GEOMETRY would be considered, starts from 1.
    1003, --SDO_ETYPE: 1(exterior, interior  - 2)003(this digits usually comes from SDO_GTYPE)
    3),  --SDO_INTERPRETATION: 1 - simple polygon, 2 - polygon connecting arcs, 3 - rectangle, 4 - circle etc.

    sdo_ordinate_array(

        --this sub-query returns the Longitude of the first point
        (SELECT SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.X X
        FROM buildings c, user_sdo_geom_metadata m 
        WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
        AND c.building_name = 'IICT'), 

        --this sub-query returns the Latitude of the first point
        (SELECT SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.Y Y
        FROM buildings c, user_sdo_geom_metadata m 
        WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
        AND c.building_name = 'IICT'), 

        --this sub-query returns the Longitude of the second point
        (SELECT SDO_GEOM.SDO_POINTONSURFACE(c.shape, m.diminfo).SDO_POINT.X X 
        FROM buildings c, user_sdo_geom_metadata m 
        WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
        AND c.building_name = 'IICT'), 

        --this sub-query returns the Latitude of the second point
        (SELECT SDO_GEOM.SDO_POINTONSURFACE(c.shape, m.diminfo).SDO_POINT.Y Y 
        FROM buildings c, user_sdo_geom_metadata m 
        WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
        AND c.building_name = 'IICT') 

    ) --SDO_ORDINATES: co-ordinates of the geomentry
 )
);