Home » Server Options » Spatial » Update-SET Query for multiple rows (11g)
Update-SET Query for multiple rows [message #652685] Thu, 16 June 2016 05:22 Go to next message
moehre
Messages: 43
Registered: June 2016
Member
Hi,

I created a geometry column in Oracle like :
ALTER table building
add (centroid_geom sdo_geometry);
.
After this I populated this column:
update building
set centroid_geom = MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE (NULL, NULL,NULL),NULL,NULL);
commit;

And now I will add my geometries:
update building

set centroid_geom = (SDO_GEOM.SDO_CENTROID(c.geometry, 2003) 
              FROM  building a, Thematic_Surface b, Surface_Geometry c--, user_sdo_geom_metadata m

              WHERE a.ID = b.Building_ID
              AND b.Objectclass_ID = 35
              AND b.Lod2_Multi_Surface_ID = c.Root_ID
              AND c.Geometry IS NOT NULL);

But it is not possible. Of course I get multiple rows as result and this is not allowed for an UPDATE-SET query. But I will that Oracle adds for each row the result automatically.

In PostgreSQL it looks like:
UPDATE building

SET centroid_geom = ST_Centroid(c.geometry)

FROM  Thematic_Surface b, Surface_Geometry c

WHERE building.ID = b.Building_ID
AND b.Objectclass_ID = 35
AND b.Lod2_Multi_Surface_ID = c.Root_ID
AND c.Geometry IS NOT NULL;

What I am doing wrong???
Re: Update-SET Query for multiple rows [message #652687 is a reply to message #652685] Thu, 16 June 2016 06:21 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps something like this?

update building a set
  a.centroid_geom = (select sdo_geom.sdo_centroid(c.geometry, 2003)
                     from thematic_surface b,
                          surface_geometry c
                     where b.building_id = a.id
                       and b.objectclass_id = 35
                       and b.lod2_multi_surface_id = c.root_id
                       and c.geometry is not null
                    );
Re: Update-SET Query for multiple rows [message #652689 is a reply to message #652687] Thu, 16 June 2016 06:29 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
No it doesn´t work. It occurs the same error message ORA-01427 " single-row subquery returns more than one row!
Re: Update-SET Query for multiple rows [message #652691 is a reply to message #652689] Thu, 16 June 2016 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you have to closer correlate the source row and the target one.
See what multiple rows are returned in subquery and decide which one you want to keep.

Re: Update-SET Query for multiple rows [message #652693 is a reply to message #652691] Thu, 16 June 2016 06:52 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
The results in the query are correct. But I will add them loop for loop.
With this query I get all the results for the table column at once.
Re: Update-SET Query for multiple rows [message #652695 is a reply to message #652693] Thu, 16 June 2016 07:11 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Do I have to rewrite my code with a loop statement?
Re: Update-SET Query for multiple rows [message #652696 is a reply to message #652695] Thu, 16 June 2016 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No you have to know what you want to do first.

Re: Update-SET Query for multiple rows [message #652698 is a reply to message #652695] Thu, 16 June 2016 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Show us what you have and what you want.

Re: Update-SET Query for multiple rows [message #652700 is a reply to message #652698] Thu, 16 June 2016 08:33 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
I have table building which contains informations about all buildings.
For this table I will add a new geometry column "centroid_geom" which contains the centroid of the building_polygon.
Each building (e.g. building_id =1...) consists of multiple polygons. The geometry of these polygons is in table "Surface_Geometry"!


If I execute this SELECT-Query:
SELECT sdo_geom.sdo_centroid(c.geometry, 2003) 
     FROM   building a,
     Thematic_Surface b, 
     Surface_Geometry c

     WHERE b.Building_ID = a.ID
     AND b.Lod2_Multi_Surface_ID = c.Root_ID
     AND b.Objectclass_ID = 35  
     AND c.Geometry IS NOT NULL;

I get a table with centroid of my ground_polygons of all buildings. It is already in the right order...
Now I will add the value for each row to the column centroid_geom in table building!

Do you know what I mean?
Re: Update-SET Query for multiple rows [message #652702 is a reply to message #652700] Thu, 16 June 2016 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does this query return one row per a.id?
The error message seems this is not the case.

Re: Update-SET Query for multiple rows [message #652703 is a reply to message #652702] Thu, 16 June 2016 11:58 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
No it returns a column. But I can add a.ID and then I guess I can do a GROUP By expression to get a row.
For example :
SELECT a.ID, sdo_geom.sdo_centroid(c.geometry, 2003) geom
                
                FROM building a,
                Thematic_Surface b,
                Surface_Geometry c
                
                WHERE b.Building_ID = a.ID
                AND b.Lod2_Multi_Surface_ID = c.Root_ID
                AND b.OBJECTCLASS_ID = 35
                AND c.Geometry IS NOT NULL;

Result = column(ID) column(GEOM)

If I use the GROUB BY a.ID expression it fails "not a GROUP BY expression"
I think with the help of the GROUP BY expression I have for each a.ID ohne geometry value!
Re: Update-SET Query for multiple rows [message #652704 is a reply to message #652703] Thu, 16 June 2016 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
No it returns a column.


OK a column but how many rows?

Quote:
I guess I can do a GROUP By expression to get a row.


No you must know why you have several rows for one a.id.
Most surely this is because a condition is missing.

As we have neither your model nor your data we can't help more than giving general rules.

Re: Update-SET Query for multiple rows [message #652712 is a reply to message #652685] Thu, 16 June 2016 15:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please see the example below. If you do not already have an entry in your user_sdo_geom_metadata view, you will need to figure out the appropriate values for your data. Alternatively, instead of passing diminfo from your user_sdo_geom_metadata, you can pass a tolerance, like 0.005, but passing an sdo_gtype like 2003 as if it was a tolerance may produce incorrect results. Regardless of which parameter you pass, you will still need an appropriate entry in your user_sdo_geom_metadata view, if you do not already have one. If this does not work for you, then please post a copy and paste of a run from SQL*Plus of everything you did as I have done below, including some sample data for the tables and the user_sdo_geom_metadata view.

-- create tables and insert data:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE building
  2    (id  NUMBER)
  3  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO building (id) VALUES (1)
  3  INTO building (id) VALUES (2)
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE thematic_surface
  2    (building_id	       NUMBER,
  3  	objectclass_id	       NUMBER,
  4  	lod2_multi_surface_id  NUMBER)
  5  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO thematic_surface (building_id, objectclass_id, lod2_multi_surface_id) VALUES (1, 35, 10)
  3  INTO thematic_surface (building_id, objectclass_id, lod2_multi_surface_id) VALUES (2, 35, 20)
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE surface_geometry
  2    (root_id   NUMBER,
  3  	geometry  SDO_GEOMETRY)
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO surface_geometry (root_id, geometry) VALUES
  3    (10,
  4  	SDO_GEOMETRY
  5  	  (2003, NULL, NULL,
  6  	   SDO_ELEM_INFO_ARRAY(1,1003,3),
  7  	   SDO_ORDINATE_ARRAY(1,1, 5,7)))
  8  INTO surface_geometry (root_id, geometry) VALUES
  9    (20,
 10  	SDO_GEOMETRY
 11  	  (2003, NULL, NULL,
 12  	   SDO_ELEM_INFO_ARRAY(1,1003,1),
 13  	   SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)))
 14  SELECT * FROM DUAL
 15  /

2 rows created.

-- insert into user_sdo_geom_metadata view:
SCOTT@orcl_12.1.0.2.0> INSERT INTO USER_SDO_GEOM_METADATA (table_name, column_name, diminfo, srid) VALUES
  2    ('SURFACE_GEOMETRY', 'GEOMETRY',
  3  	SDO_DIM_ARRAY
  4  	  (SDO_DIM_ELEMENT('X', 0, 20, 0.005),
  5  	   SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
  6  	  ),
  7  	NULL)
  8  /

1 row created.

-- display of starting data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
         1
         2

2 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM thematic_surface
  2  /

BUILDING_ID OBJECTCLASS_ID LOD2_MULTI_SURFACE_ID
----------- -------------- ---------------------
          1             35                    10
          2             35                    20

2 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM surface_geometry
  2  /

   ROOT_ID
----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
        10
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(1, 1, 5, 7))

        20
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1))


2 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM USER_SDO_GEOM_METADATA WHERE table_name = 'SURFACE_GEOMETRY'
  2  /

TABLE_NAME
--------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
--------------------------------------------------------------------------------
      SRID
----------
SURFACE_GEOMETRY
GEOMETRY
SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 0, 20, .005), SDO_DIM_ELEMENT('Y', 0, 20, .00
5))



1 row selected.

-- add centroid_geom column:
SCOTT@orcl_12.1.0.2.0> ALTER TABLE building ADD (centroid_geom SDO_GEOMETRY)
  2  /

Table altered.

-- update centroid_geom column:
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.centroid_geom =
  3  	    (SELECT SDO_GEOM.SDO_CENTROID(c.geometry, m.DIMINFO)
  4  	     FROM   Thematic_Surface b, Surface_Geometry c, USER_SDO_GEOM_METADATA m
  5  	     WHERE  a.ID = b.Building_ID
  6  	     AND    b.Objectclass_ID = 35
  7  	     AND    b.Lod2_Multi_Surface_ID = c.Root_ID
  8  	     AND    c.Geometry IS NOT NULL)
  9  /

2 rows updated.

-- results:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(3, 4, NULL), NULL, NULL)

         2
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(6.45454545, 3.75757576, NULL), NULL, NUL
L)


2 rows selected.

-- rollback:
SCOTT@orcl_12.1.0.2.0> ROLLBACK
  2  /

Rollback complete.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1


         2



2 rows selected.

-- alternative update and results using tolerance of 0.005 instead of m.diminfo
-- (this still requires that there be an appropriate entry in the user_sdo_geom_metadata table):
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.centroid_geom =
  3  	    (SELECT SDO_GEOM.SDO_CENTROID(c.geometry, 0.005)
  4  	     FROM   Thematic_Surface b, Surface_Geometry c, USER_SDO_GEOM_METADATA m
  5  	     WHERE  a.ID = b.Building_ID
  6  	     AND    b.Objectclass_ID = 35
  7  	     AND    b.Lod2_Multi_Surface_ID = c.Root_ID
  8  	     AND    c.Geometry IS NOT NULL)
  9  /

2 rows updated.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(3, 4, NULL), NULL, NULL)

         2
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(6.45454545, 3.75757576, NULL), NULL, NUL
L)


2 rows selected.

[Updated on: Thu, 16 June 2016 15:29]

Report message to a moderator

Re: Update-SET Query for multiple rows [message #652823 is a reply to message #652712] Mon, 20 June 2016 07:36 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Ok I am still working on this example.
I already found a mistake regarding to one result.

If I am doing a SELECT query to get one row:
SELECT count(SDO_GEOM.SDO_CENTROID(c.geometry, 0.005))
FROM  building a, Thematic_Surface b, Surface_Geometry c, user_sdo_geom_metadata m

WHERE a.ID = b.Building_ID
AND b.Objectclass_ID = 35
AND b.Lod2_Multi_Surface_ID = c.Root_ID
AND c.Geometry IS NOT NULL
AND a.ID = 1;

Result:
COUNT(SDO_GEOM.SDO_CENTROID(C.GEOMETRY,0.005))
--------------------------------------------------------------------------------
116

If I am doing a SELECT query without calculating the centroid:
SELECT count(c.geometry)
FROM  building a, Thematic_Surface b, Surface_Geometry c, user_sdo_geom_metadata m

WHERE a.ID = b.Building_ID
AND b.Objectclass_ID = 35
AND b.Lod2_Multi_Surface_ID = c.Root_ID
AND c.Geometry IS NOT NULL
AND a.ID = 1;

Result:
COUNT((C.GEOMETRY)
--------------------------------------------------------------------------------
1

So does it occurs because of the spacial datatype?
If I am using m.DIMINFO instead of 0.005 it says: "layer dimensionality does not match geometry dimensions"

My original geometry column ( Table Surface_Geometry, column: GEOMETRY) looks like:
MDSYS.SDO_GEOMETRY(3003,31467,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(3481803.008,5384185.685,625.799,34818 03.083,5384185.819,625.799,3481803.083,5384185.819,639.956,3481803.008,5384185.685,639.956,3481803.008,5384185.685,625.799))

At the moment I have this query:
UPDATE building a


SET a.centroid_geom = (SELECT SDO_GEOM.SDO_CENTROID(c.geometry, 0.005)

FROM  Thematic_Surface b, Surface_Geometry c

WHERE  b.Building_ID = a.ID
AND b.Objectclass_ID = 35
AND b.Lod2_Multi_Surface_ID = c.Root_ID
AND c.Geometry IS NOT NULL);
AND a.ID = 1)

As a result I get one row and for my column centroid_geom the first row is updated.
Nevertheless in Table building (a) I have multiple id´s. So I do not want only to update a.ID =1. I will update from 1 till the end!!!
How can I do this?

[Updated on: Mon, 20 June 2016 09:45]

Report message to a moderator

Re: Update-SET Query for multiple rows [message #652836 is a reply to message #652823] Mon, 20 June 2016 14:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The reason for the multiple rows is that I forgot to add the filter conditions for the user_sdo_geom_metadata view and you presumable have entries for other tables in that view. I have added those in the revised demonstration below.

The "layer dimensionality does not match geometry dimensions" occurs because the entry in your user_sdo_geom_metadata view is not appropriate for your table data. I have included a revised insert statement below that inserts appropriate data into that view by selecting the minimum - 1 and maximum + 1 coordinate values from your data.

To update all of the rows, you need to remove the "and a.id = 1" that you added.

Since you only provided one geometry, I have used that for all 3 rows of data. You can see that it updates all 3 rows. The results will be the same, since the data is the same.

Please see the revised demonstration below.


-- create tables and insert data:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE building
  2    (id  NUMBER)
  3  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO building (id) VALUES (1)
  3  INTO building (id) VALUES (2)
  4  INTO building (id) VALUES (3)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE thematic_surface
  2    (building_id	       NUMBER,
  3  	objectclass_id	       NUMBER,
  4  	lod2_multi_surface_id  NUMBER)
  5  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO thematic_surface (building_id, objectclass_id, lod2_multi_surface_id) VALUES (1, 35, 10)
  3  INTO thematic_surface (building_id, objectclass_id, lod2_multi_surface_id) VALUES (2, 35, 20)
  4  INTO thematic_surface (building_id, objectclass_id, lod2_multi_surface_id) VALUES (3, 35, 30)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE surface_geometry
  2    (root_id   NUMBER,
  3  	geometry  SDO_GEOMETRY)
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO surface_geometry (root_id, geometry) VALUES
  3  	(10,
  4  	MDSYS.SDO_GEOMETRY
  5  	  (3003,31467,NULL,
  6  	   MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
  7  	   MDSYS.SDO_ORDINATE_ARRAY
  8  	     (3481803.008,5384185.685,625.799,
  9  	      3481803.083,5384185.819,625.799,
 10  	      3481803.083,5384185.819,639.956,
 11  	      3481803.008,5384185.685,639.956,
 12  	      3481803.008,5384185.685,625.799)))
 13  INTO surface_geometry (root_id, geometry) VALUES
 14    (20,
 15  	MDSYS.SDO_GEOMETRY
 16  	  (3003,31467,NULL,
 17  	   MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
 18  	   MDSYS.SDO_ORDINATE_ARRAY
 19  	     (3481803.008,5384185.685,625.799,
 20  	      3481803.083,5384185.819,625.799,
 21  	      3481803.083,5384185.819,639.956,
 22  	      3481803.008,5384185.685,639.956,
 23  	      3481803.008,5384185.685,625.799)))
 24  INTO surface_geometry (root_id, geometry) VALUES
 25    (30,
 26  	MDSYS.SDO_GEOMETRY
 27  	  (3003,31467,NULL,
 28  	   MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
 29  	   MDSYS.SDO_ORDINATE_ARRAY
 30  	     (3481803.008,5384185.685,625.799,
 31  	      3481803.083,5384185.819,625.799,
 32  	      3481803.083,5384185.819,639.956,
 33  	      3481803.008,5384185.685,639.956,
 34  	      3481803.008,5384185.685,625.799)))
 35  SELECT * FROM DUAL
 36  /

3 rows created.


-- insert into user_sdo_geom_metadata values appropriate for your data:
SCOTT@orcl_12.1.0.2.0> INSERT INTO USER_SDO_GEOM_METADATA (table_name, column_name,diminfo,srid) VALUES
  2    ('SURFACE_GEOMETRY', 'GEOMETRY',
  3  	(SELECT MDSYS.SDO_DIM_ARRAY
  4  		  (MDSYS.SDO_DIM_ELEMENT('X', minx, maxx, 0.05),
  5  		   MDSYS.SDO_DIM_ELEMENT('Y', miny, maxy, 0.05),
  6  		   MDSYS.SDO_DIM_ELEMENT('Z', minz, maxz, 0.05)) as diminfo
  7  	 FROM	(SELECT TRUNC( MIN( v.x ) - 1,0) as minx,
  8  			ROUND( MAX( v.x ) + 1,0) as maxx,
  9  			TRUNC( MIN( v.y ) - 1,0) as miny,
 10  			ROUND( MAX( v.y ) + 1,0) as maxy,
 11  			ROUND( MIN( v.z ) - 1,0) as minz,
 12  			ROUND( MAX( v.z ) + 1,0) as maxz
 13  		 FROM	(SELECT SDO_AGGR_MBR(a.geometry) as mbr
 14  			 FROM	surface_geometry a) b,
 15  			TABLE(mdsys.sdo_util.getvertices(b.mbr)) v)),
 16  	3003)
 17  /

1 row created.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

-- display starting data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
         1
         2
         3

3 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM thematic_surface
  2  /

BUILDING_ID OBJECTCLASS_ID LOD2_MULTI_SURFACE_ID
----------- -------------- ---------------------
          1             35                    10
          2             35                    20
          3             35                    30

3 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM surface_geometry
  2  /

   ROOT_ID
----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
        10
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3481803.01, 5384185.69, 625.799, 3481803.08, 5384185.82, 625.799, 3481803.08
, 5384185.82, 639.956, 3481803.01, 5384185.69, 639.956, 3481803.01, 5384185.69,
625.799))

        20
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3481803.01, 5384185.69, 625.799, 3481803.08, 5384185.82, 625.799, 3481803.08
, 5384185.82, 639.956, 3481803.01, 5384185.69, 639.956, 3481803.01, 5384185.69,
625.799))

        30
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3481803.01, 5384185.69, 625.799, 3481803.08, 5384185.82, 625.799, 3481803.08
, 5384185.82, 639.956, 3481803.01, 5384185.69, 639.956, 3481803.01, 5384185.69,
625.799))


3 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM USER_SDO_GEOM_METADATA WHERE table_name = 'SURFACE_GEOMETRY'
  2  /

TABLE_NAME
--------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
--------------------------------------------------------------------------------
      SRID
----------
SURFACE_GEOMETRY
GEOMETRY
SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 3481802, 3481804, .05), SDO_DIM_ELEMENT('Y',
5384184, 5384187, .05), SDO_DIM_ELEMENT('Z', 625, 641, .05))
      3003


1 row selected.

-- add centroid_geom column:
SCOTT@orcl_12.1.0.2.0> ALTER TABLE building ADD (centroid_geom SDO_GEOMETRY)
  2  /

Table altered.

-- update centroid_geom column using user_sdo_geom_metadata.diminfo:
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.centroid_geom =
  3  	    (SELECT SDO_GEOM.SDO_CENTROID(c.geometry, m.DIMINFO)
  4  	     FROM   Thematic_Surface b, Surface_Geometry c, USER_SDO_GEOM_METADATA m
  5  	     WHERE  a.ID = b.Building_ID
  6  	     AND    b.Objectclass_ID = 35
  7  	     AND    b.Lod2_Multi_Surface_ID = c.Root_ID
  8  	     AND    c.Geometry IS NOT NULL
  9  	     AND    m.table_name = 'SURFACE_GEOMETRY'
 10  	     AND    m.column_name = 'GEOMETRY')
 11  /

3 rows updated.

-- results:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3481803.05, 5384185.75, 632.8775), NULL
, NULL)

         2
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3481803.05, 5384185.75, 632.8775), NULL
, NULL)

         3
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3481803.05, 5384185.75, 632.8775), NULL
, NULL)


3 rows selected.

-- rollback:
SCOTT@orcl_12.1.0.2.0> ROLLBACK
  2  /

Rollback complete.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1


         2


         3



3 rows selected.

-- update centroid_geom column using tolerance of 0.005:
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.centroid_geom =
  3  	    (SELECT SDO_GEOM.SDO_CENTROID(c.geometry, 0.005)
  4  	     FROM   Thematic_Surface b, Surface_Geometry c, USER_SDO_GEOM_METADATA m
  5  	     WHERE  a.ID = b.Building_ID
  6  	     AND    b.Objectclass_ID = 35
  7  	     AND    b.Lod2_Multi_Surface_ID = c.Root_ID
  8  	     AND    c.Geometry IS NOT NULL)
  9  /

3 rows updated.

-- results:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3481803.05, 5384185.75, 632.8775), NULL
, NULL)

         2
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3481803.05, 5384185.75, 632.8775), NULL
, NULL)

         3
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3481803.05, 5384185.75, 632.8775), NULL
, NULL)


3 rows selected.
Re: Update-SET Query for multiple rows [message #652865 is a reply to message #652836] Tue, 21 June 2016 04:03 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
It works with the filter conditions thanks for that!
But if I remove "a.ID= 1" the error occurs: "single-row subquery returns more than one row"
I do not want to do it seperate...I will update all of my a.ID´s


Building(a):
ID      ...       CENTROID_GEOM
---     ----      --------------
 1       ...          null        -> Building 1
 2       ...          null        -> Building 2
 3       ...          null        -> Building 3
1) BUILDING.ID = THEMATIC_SURFACE.BUILDING_ID
Thematic_Surface:
BUILDING_ID   LOD2_Multi_Surface_ID   OBJECTCLASS_ID
-----------   ---------------------   --------------
    1                     9                    33
    1                     11                   34
    1                     13                   34
    1                     15                   34
    1                     17                   34
    1                     19                   35  35== GroundPolygon (which I need)

2)THEMATIC_SURFACE.OBJECTCLASS_ID = 35
3) THEMATIC_SURFACE.LOD2_MULTI_SURFACE_ID = SURFACE_GEOMETRY.ROOT_ID
4) SURFACE_GEOMETRY.GEOMETRY IS NOT NULL
Surface_Geometry:
ROOT_ID     GEOMETRY
-------     --------
   19        ....    -> compute centroid out of this polygon and set it to building table (a.id=1)

I found propably a mistake. So sometimes I have for one building more than one groundpolygon! like this:
BUILDING_ID   LOD2_Multi_Surface_ID   OBJECTCLASS_ID
-----------   ---------------------   --------------
    1                     9                    33
    1                     11                   34
    1                     13                   34
    1                     15                   34
    1                     17                   34
    1                     19                   35  35== GroundPolygon (which I need)
    1                     20                   35  35== GroundPolygon (which I need)

I guess this will be produce errors?
So for case I have multiple groundpolygons can I append both together for one building and calculate the centroid of this.


[Updated on: Tue, 21 June 2016 04:29]

Report message to a moderator

Re: Update-SET Query for multiple rows [message #652893 is a reply to message #652865] Tue, 21 June 2016 17:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Having more than one polygon per building will produce the "single-row subquery returns more than one row".
You can aggregate the polygons and get the center of that. Please see the revised demonstration below.
Since I used sdoaggrtype, I had to use a tolerance, not diminfo.


-- create tables and insert data:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE building
  2    (id  NUMBER)
  3  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO building (id) VALUES (1)
  3  SELECT * FROM DUAL
  4  /

1 row created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE thematic_surface
  2    (building_id	       NUMBER,
  3  	objectclass_id	       NUMBER,
  4  	lod2_multi_surface_id  NUMBER)
  5  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO thematic_surface (building_id, objectclass_id, lod2_multi_surface_id) VALUES (1, 35, 19)
  3  INTO thematic_surface (building_id, objectclass_id, lod2_multi_surface_id) VALUES (1, 35, 20)
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE surface_geometry
  2    (root_id   NUMBER,
  3  	geometry  SDO_GEOMETRY)
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO surface_geometry (root_id, geometry) VALUES
  3  	(19,
  4  	MDSYS.SDO_GEOMETRY
  5  	  (3003,31467,NULL,
  6  	   MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
  7  	   MDSYS.SDO_ORDINATE_ARRAY
  8  	     (3481803.008,5384185.685,625.799,
  9  	      3481803.083,5384185.819,625.799,
 10  	      3481803.083,5384185.819,639.956,
 11  	      3481803.008,5384185.685,639.956,
 12  	      3481803.008,5384185.685,625.799)))
 13  INTO surface_geometry (root_id, geometry) VALUES
 14  	(20,
 15  	MDSYS.SDO_GEOMETRY
 16  	  (3003,31467,NULL,
 17  	   MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
 18  	   MDSYS.SDO_ORDINATE_ARRAY
 19  	     (3481803.008,5384185.685,625.799,
 20  	      3481803.083,5384185.819,625.799,
 21  	      3481803.083,5384185.819,639.956,
 22  	      3481803.008,5384185.685,639.956,
 23  	      3481803.008,5384185.685,625.799)))
 24  SELECT * FROM DUAL
 25  /

2 rows created.

SCOTT@orcl_12.1.0.2.0> ALTER TABLE building ADD (centroid_geom SDO_GEOMETRY)
  2  /

Table altered.

-- display starting data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1



1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM thematic_surface
  2  /

BUILDING_ID OBJECTCLASS_ID LOD2_MULTI_SURFACE_ID
----------- -------------- ---------------------
          1             35                    19
          1             35                    20

2 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM surface_geometry
  2  /

   ROOT_ID
----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
        19
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3481803.01, 5384185.69, 625.799, 3481803.08, 5384185.82, 625.799, 3481803.08
, 5384185.82, 639.956, 3481803.01, 5384185.69, 639.956, 3481803.01, 5384185.69,
625.799))

        20
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3481803.01, 5384185.69, 625.799, 3481803.08, 5384185.82, 625.799, 3481803.08
, 5384185.82, 639.956, 3481803.01, 5384185.69, 639.956, 3481803.01, 5384185.69,
625.799))


2 rows selected.


-- update centroid_geom column using tolerance of 0.005:
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.centroid_geom =
  3  	    (SELECT SDO_AGGR_CENTROID (SDOAGGRTYPE (c.geometry, 0.005))
  4  	     FROM   Thematic_Surface b, Surface_Geometry c
  5  	     WHERE  a.ID = b.Building_ID
  6  	     AND    b.Objectclass_ID = 35
  7  	     AND    b.Lod2_Multi_Surface_ID = c.Root_ID
  8  	     AND    c.Geometry IS NOT NULL)
  9  /

1 row updated.

-- results:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3481803.05, 5384185.75, 632.8775), NULL
, NULL)


1 row selected.

updated by bb to remove unnecessary user_sdo_geom_metadata

[Updated on: Wed, 22 June 2016 13:13]

Report message to a moderator

Re: Update-SET Query for multiple rows [message #652909 is a reply to message #652893] Wed, 22 June 2016 03:17 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
@Barbara thanks for helping me so much Wink

So I tried the function SDO_AGGR_CENTROID but unfortunately it is not possible bedcause I only have Locator extension for Oracle.
And this won´t be changed in the near future!!!

error: Operation not supported in Oracle Locator

So please can you give me an adcvice how to solve this in Oracle Locator???
Is there a similar function in Locator?

[Updated on: Wed, 22 June 2016 03:17]

Report message to a moderator

Re: Update-SET Query for multiple rows [message #652913 is a reply to message #652909] Wed, 22 June 2016 03:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I don't know of a substitute method in locator and don't have it to test with. You might try the OTN spatial forum:

https://community.oracle.com/community/database/oracle-database-options/spatial/content

[Updated on: Wed, 22 June 2016 03:53]

Report message to a moderator

Re: Update-SET Query for multiple rows [message #652953 is a reply to message #652909] Wed, 22 June 2016 14:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Since I have spatial, not just locator, I can't test what you can use. However, the following is fairly simplistic. It uses the SDO_GEOM.SDO_CENTROID function that we already know that you can use to get centers for the individual polygons. Then it selects the x, y, and z coordinates and averages them for the building to create a center for all of the polygons for that building. I don't know what algorithm SDO_AGGR_CENTROID uses, so I don't know if the results would be the same for multiple polygons, but it would definitely be the same for buildings with only one polygon. If you could supply the actual 2 values of table_surface.geometry for root_id's 19 and 20, then I could run both and compare the results.

-- test data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1



1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM thematic_surface
  2  /

BUILDING_ID OBJECTCLASS_ID LOD2_MULTI_SURFACE_ID
----------- -------------- ---------------------
          1             35                    19
          1             35                    20

2 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM surface_geometry
  2  /

   ROOT_ID
----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
        19
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3481803.01, 5384185.69, 625.799, 3481803.08, 5384185.82, 625.799, 3481803.08
, 5384185.82, 639.956, 3481803.01, 5384185.69, 639.956, 3481803.01, 5384185.69,
625.799))

        20
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3481803.01, 5384185.69, 625.799, 3481803.08, 5384185.82, 625.799, 3481803.08
, 5384185.82, 639.956, 3481803.01, 5384185.69, 639.956, 3481803.01, 5384185.69,
625.799))


2 rows selected.

-- update that should run with locator:
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.centroid_geom =
  3  	    (SELECT centroid_geom
  4  	     FROM   (SELECT building_id,
  5  			    SDO_GEOMETRY
  6  			      (MAX (t1.sg.sdo_gtype), MAX (t1.sg.sdo_srid),
  7  			       SDO_POINT_TYPE (AVG(t1.sg.sdo_point.x), AVG(t1.sg.sdo_point.y), AVG(t1.sg.sdo_point.z)),
  8  			       NULL, NULL) centroid_geom
  9  		     FROM   (SELECT b.building_ID, SDO_GEOM.SDO_CENTROID (c.geometry, 0.005) sg
 10  			     FROM   Thematic_Surface b, Surface_Geometry c
 11  			     WHERE  b.Objectclass_ID = 35
 12  			     AND    b.Lod2_Multi_Surface_ID = c.Root_ID
 13  			     AND    c.Geometry IS NOT NULL) t1
 14  		     GROUP  BY building_id) t2
 15  	     WHERE  a.id = t2.building_id)
 16  /

1 row updated.

-- results:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3481803.05, 5384185.75, 632.8775), NULL
, NULL)


1 row selected.
Re: Update-SET Query for multiple rows [message #652975 is a reply to message #652953] Thu, 23 June 2016 03:50 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
So it works now!!!
But can you explain a little bit more about this code.

first part:
Quote:
FROM (SELECT building_id,
5 SDO_GEOMETRY
6 (MAX (t1.sg.sdo_gtype), MAX (t1.sg.sdo_srid),
7 SDO_POINT_TYPE (AVG(t1.sg.sdo_point.x), AVG(t1.sg.sdo_point.y), AVG(t1.sg.sdo_point.z)),
8 NULL, NULL) centroid_geom

You creating the geometry (average x,y,z) for each polygon and set this to the column centroid_geom.

In the next part:
Quote:
FROM (SELECT b.building_ID, SDO_GEOM.SDO_CENTROID (c.geometry, 0.005) sg
10 FROM Thematic_Surface b, Surface_Geometry c
11 WHERE b.Objectclass_ID = 35
12 AND b.Lod2_Multi_Surface_ID = c.Root_ID
13 AND c.Geometry IS NOT NULL) t1


you calculate the Centroid by using the normal function (SDO_GEOM.SDO_Centroid) of the polygon. It is possible that we have more than one polygon. Because of that the first part is calculating the average of each building.
Am I right?

Why you are using Group By?


Values for root id 19 +20:

Root_ID 19 = MDSYS.SDO_GEOMETRY(3003,31467,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(3482967.35,5376954.8,521.988,3482965. 61,5376958.37,521.988,3482966.11,5376958.61,521.988,3482963.9,5376963.24,521.988,3482968.123,5376965.275,521.988,3482973.5,5376954.25 ,521.988,3482970.35,5376952.78,521.988,3482967.35,5376954.8,521.988))

Root_ID 20 = MDSYS.SDO_GEOMETRY(3003,31467,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(3482973.73,5376953.75,521.847,3482973 .5,5376954.25,521.847,3482968.123,5376965.275,521.847,3482973.01,5376967.63,521.847,3482975.48,5376962.49,521.847,3482976.79,5376963. 12,521.847,3482979.9,5376956.67,521.847,3482977.21,5376955.37,521.847,3482976.84,5376954.15,521.847,3482975,5376953.32,521.847,348297 3.73,5376953.75,521.847))


I have an idea for another option:

Create new column in table building and calculate the minimal bounding rectangle (SDO_AGGR_MBR(c.GEOMETRY) -> This function is working in locator) of each building. Then calculate the centroid by using SDO_GEOM.SDO_CENTROID()! Is this also be possible?
If I trying this one I get this error message: "SQL-Fehler: ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: 54535".
Does it mean that my 3D-geometry is not valid? Anyway is this option realizeable?
So as a result I get the Centroid of the MBR instead of only GroundSurfaces.
This is sufficient for my workflow but better would it be to calculate the centroid of only ground polygons...

[Updated on: Thu, 23 June 2016 04:16]

Report message to a moderator

Re: Update-SET Query for multiple rows [message #652978 is a reply to message #652975] Thu, 23 June 2016 04:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
When analyzing a query, bear in mind that it does not process top to bottom, but from the inside out. So, if you want to see what something is doing, then run the innermost query, then the one around it that uses the previous one as a sub-query and continue in this manner until the outermost. Please see the revised demonstration below with comments. Also, the results of the two different methods are very close.

-- test data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1



1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM thematic_surface
  2  /

BUILDING_ID OBJECTCLASS_ID LOD2_MULTI_SURFACE_ID
----------- -------------- ---------------------
          1             35                    19
          1             35                    20

2 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM surface_geometry
  2  /

   ROOT_ID
----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
        19
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3482967.35, 5376954.8, 521.988, 3482965.61, 5376958.37, 521.988, 3482966.11,
 5376958.61, 521.988, 3482963.9, 5376963.24, 521.988, 3482968.12, 5376965.28, 52
1.988, 3482973.5, 5376954.25, 521.988, 3482970.35, 5376952.78, 521.988, 3482967.
35, 5376954.8, 521.988))

        20
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3482973.73, 5376953.75, 521.847, 3482973.5, 5376954.25, 521.847, 3482968.12,
 5376965.28, 521.847, 3482973.01, 5376967.63, 521.847, 3482975.48, 5376962.49, 5
21.847, 3482976.79, 5376963.12, 521.847, 3482979.9, 5376956.67, 521.847, 3482977
.21, 5376955.37, 521.847, 3482976.84, 5376954.15, 521.847, 3482975, 5376953.32,
521.847, 3482973.73, 5376953.75, 521.847))


2 rows selected.

-- update that runs on full spatial:
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.centroid_geom =
  3  	    (SELECT SDO_AGGR_CENTROID (SDOAGGRTYPE (c.geometry, 0.005))
  4  	     FROM   Thematic_Surface b, Surface_Geometry c
  5  	     WHERE  a.ID = b.Building_ID
  6  	     AND    b.Objectclass_ID = 35
  7  	     AND    b.Lod2_Multi_Surface_ID = c.Root_ID
  8  	     AND    c.Geometry IS NOT NULL)
  9  /

1 row updated.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3482972, 5376958.43, 521.905767), NULL,
 NULL)


1 row selected.

-- rollback:
SCOTT@orcl_12.1.0.2.0> ROLLBACK
  2  /

Rollback complete.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1



1 row selected.

-- innermost query to be used in update gets centroid for each individual polygon in surface geometry
-- along with the building_id that will be needed later:
SCOTT@orcl_12.1.0.2.0> SELECT b.building_ID, SDO_GEOM.SDO_CENTROID (c.geometry, 0.005) sg
  2  			     FROM   Thematic_Surface b, Surface_Geometry c
  3  			     WHERE  b.Objectclass_ID = 35
  4  			     AND    b.Lod2_Multi_Surface_ID = c.Root_ID
  5  			     AND    c.Geometry IS NOT NULL
  6  /

BUILDING_ID
-----------
SG(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
          1
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3482967.85, 5376958.19, 521.988), NULL,
 NULL)

          1
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3482974.96, 5376958.6, 521.847), NULL,
NULL)


2 rows selected.

-- The next level of query that uses the previous one as a sub-query,
-- gets the x, y, and z coordinates of all centroids for the polygons for each building and averages them.
-- It is necessary to have the building_id and group by in order to use the aggregate AVG (average) function
-- and return a centroid for each building and to have the building_id to use in the join for the update.
SCOTT@orcl_12.1.0.2.0> SELECT building_id,
  2  			    SDO_GEOMETRY
  3  			      (MAX (t1.sg.sdo_gtype), MAX (t1.sg.sdo_srid),
  4  			       SDO_POINT_TYPE (AVG(t1.sg.sdo_point.x), AVG(t1.sg.sdo_point.y), AVG(t1.sg.sdo_point.z)),
  5  			       NULL, NULL) centroid_geom
  6  		     FROM   (SELECT b.building_ID, SDO_GEOM.SDO_CENTROID (c.geometry, 0.005) sg
  7  			     FROM   Thematic_Surface b, Surface_Geometry c
  8  			     WHERE  b.Objectclass_ID = 35
  9  			     AND    b.Lod2_Multi_Surface_ID = c.Root_ID
 10  			     AND    c.Geometry IS NOT NULL) t1
 11  		     GROUP  BY building_id
 12  /

BUILDING_ID
-----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
          1
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3482971.4, 5376958.4, 521.9175), NULL,
NULL)


1 row selected.

-- update that should run with locator by using the above query and updating the centroid_geom
-- for the matching building_id, producing results very close to the full spatial method.
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.centroid_geom =
  3  	    (SELECT centroid_geom
  4  	     FROM   (SELECT building_id,
  5  			    SDO_GEOMETRY
  6  			      (MAX (t1.sg.sdo_gtype), MAX (t1.sg.sdo_srid),
  7  			       SDO_POINT_TYPE (AVG(t1.sg.sdo_point.x), AVG(t1.sg.sdo_point.y), AVG(t1.sg.sdo_point.z)),
  8  			       NULL, NULL) centroid_geom
  9  		     FROM   (SELECT b.building_ID, SDO_GEOM.SDO_CENTROID (c.geometry, 0.005) sg
 10  			     FROM   Thematic_Surface b, Surface_Geometry c
 11  			     WHERE  b.Objectclass_ID = 35
 12  			     AND    b.Lod2_Multi_Surface_ID = c.Root_ID
 13  			     AND    c.Geometry IS NOT NULL) t1
 14  		     GROUP  BY building_id) t2
 15  	     WHERE  a.id = t2.building_id)
 16  /

1 row updated.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINA
--------------------------------------------------------------------------------
         1
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3482971.4, 5376958.4, 521.9175), NULL,
NULL)


1 row selected.
Re: Update-SET Query for multiple rows [message #652980 is a reply to message #652975] Thu, 23 June 2016 04:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
moehre wrote on Thu, 23 June 2016 01:50

I have an idea for another option:

Create new column in table building and calculate the minimal bounding rectangle (SDO_AGGR_MBR(c.GEOMETRY) -> This function is working in locator) of each building. Then calculate the centroid by using SDO_GEOM.SDO_CENTROID()! Is this also be possible?
If I trying this one I get this error message: "SQL-Fehler: ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: 54535".
Does it mean that my 3D-geometry is not valid? Anyway is this option realizeable?
So as a result I get the Centroid of the MBR instead of only GroundSurfaces.
This is sufficient for my workflow but better would it be to calculate the centroid of only ground polygons...


I get the same error when I try to use sdo_geom.sdo_centroid with sdo_aggr_mbr. It looks like a possible bug to me.

As you noted, if it did work, it would return different data. The method that I provided is closer.

Re: Update-SET Query for multiple rows [message #652981 is a reply to message #652978] Thu, 23 June 2016 04:59 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
oh wow this is really close. So at the end I have differences of some centimeters...I think this is good enough for me. It takes a long time but this is not relevant I have to calculate it onetimes only or if I add buildings to my database!

Thanks a lot for your help Smile

Can I create a function of that SQL statement?
Then it would be inside my db for later calculations.

[Updated on: Thu, 23 June 2016 05:05]

Report message to a moderator

Re: Update-SET Query for multiple rows [message #653196 is a reply to message #652981] Tue, 28 June 2016 18:00 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
moehre wrote on Thu, 23 June 2016 02:59
...
Can I create a function of that SQL statement?
Then it would be inside my db for later calculations.


Apparently, you edited your post and added this question after I had already read it, so I missed the added question and just happened to stumble across it now. Yes, you can create a function, as demonstrated below.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION get_centroid
  2    (p_id IN building.id%TYPE)
  3    RETURN SDO_GEOMETRY
  4  AS
  5    v_centroid_geom	SDO_GEOMETRY;
  6  BEGIN
  7    SELECT centroid_geom
  8    INTO   v_centroid_geom
  9    FROM   (SELECT building_id,
 10  		      SDO_GEOMETRY
 11  			(MAX (t1.sg.sdo_gtype), MAX (t1.sg.sdo_srid),
 12  			 SDO_POINT_TYPE (AVG(t1.sg.sdo_point.x), AVG(t1.sg.sdo_point.y), AVG(t1.sg.sdo_point.z)),
 13  			 NULL, NULL) centroid_geom
 14  	       FROM   (SELECT b.building_ID, SDO_GEOM.SDO_CENTROID (c.geometry, 0.005) sg
 15  		       FROM   Thematic_Surface b, Surface_Geometry c
 16  		       WHERE  b.Objectclass_ID = 35
 17  		       AND    b.Lod2_Multi_Surface_ID = c.Root_ID
 18  		       AND    c.Geometry IS NOT NULL) t1
 19  	       GROUP  BY building_id) t2
 20    WHERE  t2.building_id = p_id;
 21    RETURN v_centroid_geom;
 22  END get_centroid;
 23  /

Function created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.centroid_geom = get_centroid (a.id)
  3  /

3 rows updated.
Previous Topic: Import Shape in Oracle Locator
Next Topic: UPDATE-SET Command does not work
Goto Forum:
  


Current Time: Thu Mar 28 14:00:30 CDT 2024