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:

24.916312, 91.832393
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.

Albert Godfrind · Answer 1 · 2015-12-16T10:55:45.663

0

The SDO_CENTROID() function returns an SDO_GEOMETRY object, which you can then use for inserting into your results table.

Assuming this is your result table

create table building_centroids (
  building_id number primary key,
  centroid sdo_geometry
);

Then the following will fill it with the centroids of all your buildings

insert into building_centroids (building_id, centroid)
select building_id, sdo_geom.sdo_centroid(shape, 0.05)
from buildings;

I don't understand why you need to change the coordinates (180+x, 180-y). That makes no sense.

Anyway, you say that the above is not what you want. I am going to keep on guessing and assume that you want to build a rectangle where the two corners are computed as centroids in two of your buildings. That needs a bit of PL/SQL, like this:

First define a function that builds a rectangle from two input points

create or replace function rectangle_from_points (
  point_1 sdo_geometry,
  point_2 sdo_geometry
) 
return sdo_geometry
as 
  rectangle sdo_geometry;
begin
  -- Initialize resulting rectangle
  rectangle := sdo_geometry (2003, point_1.sdo_srid, null,
     sdo_elem_info_array (1,1003,3),
     sdo_ordinate_array()
  );
  -- Fill it with the two point points
  rectangle.sdo_ordinates.extend(4);
  rectangle.sdo_ordinates(1) := point_1.sdo_point.x;
  rectangle.sdo_ordinates(2) := point_1.sdo_point.y;
  rectangle.sdo_ordinates(3) := point_2.sdo_point.x;
  rectangle.sdo_ordinates(4) := point_2.sdo_point.y;
  -- Return it
  return rectangle;
end;
/

Now, use it like this:

select rectangle_from_points (
  (
    select sdo_geom.sdo_centroid(shape, 0.05)
    from buildings
    building_id = 42
  ),
  (
    select sdo_geom.sdo_centroid(shape, 0.05)
    from buildings
    building_id = 564
  )
)
from dual;

If this is not what you expect, then please rephrase the question (and also clarify the actual business problem you are trying to solve)

edited Dec 16 '15 at 10:55

answered Dec 14 '15 at 20:53

Albert Godfrind

1,978
1
12
20

It seems you have tried to make another table with centroids of all buildings, but that was not my need and I did not ask for it. But you are right. It make no sense to add or subtract x or y from something. I did it because of some issue, but I recovered from it. – Enamul Hassan Dec 15 '15 at 00:59
I don't understand. In your question you ask about how to insert the results of a subquery so that is exactly what I showed. But then again you can do whatever you want with the result of the function: insert it into some table, update some table with it, save it in a variable, use it in another query, use it in another function call... You need to rephrase your question and clarify what it is that you want to achieve. – Albert Godfrind Dec 16 '15 at 05:47
If you did not understand, then you should ask question in comment to clarify first, I think. However, Consider this query: `SELECT SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT FROM buildings c, user_sdo_geom_metadata m WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' AND c.building_name = 'IICT';` which returns an object of `SDO_GEOMETRY`. I want to add it in the last codeblocks in my question where comments like `/* sub-query */`. But it does not work. My question is how to make it work? – Enamul Hassan Dec 16 '15 at 08:39
Still not clear. Do you mean that you want to construct a line by appending the point you got from the centroid function to an existing line geometry - a bit like constructing a GPS track from individual points ? Or is it that you want to construct a shape (a rectangle ?) using two centroids as corners ? This needs some PL/SQL. See a complement in my response. Then again, it always helps to explain the original business problem you need to solve in addition to the way you have been trying to solve it. – Albert Godfrind Dec 16 '15 at 10:42
in the insert query in my question, it is clear that I am trying to add a rectangle. The corners of the rectangle would come from sub-queries. The sub-queries would return a point as an object of `SDO_GEOMETRY`. But `SDO_ORDINATE_ARRAY` function takes only points. So, I asked in the question, how to solve the issue? – Enamul Hassan Dec 16 '15 at 16:24

Enamul Hassan · Accepted Answer · 2015-12-16T16:16:50.790

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
 )
);

You cannot use the SDO_ORDINATE_ARRAY type the way you do. It is designed for use inside an SDO_GEOMETRY type only. You cannot do anything with this type: in particular, you can't create any spatial index on it, you cannot query it, you cannot do any processing with it. You also can't view it. It would make more sense to just store four numeric columns instead. — Albert Godfrind, Dec 16 '15 at 14:07
Again, I am not sure what you want to achieve. It can make sense to have a table of buildings that contains three geometry columns: one for the building footprint, one for the centroid, and one for a "point on surface". Filling the centroid and point_on_surface is achieved by a simple update statement. — Albert Godfrind, Dec 16 '15 at 14:15
@AlbertGodfrind Oh, sorry, I missed some code while copy paste. Thanks. — Enamul Hassan, Dec 16 '15 at 16:17
Ok, so that is a bit cleared. What you are looking for is a way to build a rectangle, given two points (both being SDO_GEOMETRY objects). While the code you use will probably work, it is not really very efficient, since it involves calling the functions that generate the points multiple times. I suggest using the simple rectangle_from_points() function I posted. Should be simpler and more efficient. — Albert Godfrind, Dec 17 '15 at 09:43
Another point: geometric rectangles are purely artificial constructs. They will never appear in the real world. The only purpose of that shape is to be used as a rectangle for fetching tables for viewing (zoom and pan operation). You may also see them as MBR (minimum bounding rectangles) returned by the SDO_MBR() function. But you will never see real life objects (like buildings) modeled that way. — Albert Godfrind, Dec 17 '15 at 09:47
@AlbertGodfrind I know it, but I need it. Anyway, thanks. :) — Enamul Hassan, Dec 17 '15 at 09:53

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

N.B.:

2 Answers2