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  |
 |
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 #652712 is a reply to message #652685] |
Thu, 16 June 2016 15:11  |
 |
Barbara Boehmer
Messages: 9100
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 |
|
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 |
|
Barbara Boehmer
Messages: 9100
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 |
|
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
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)
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 |
|
Barbara Boehmer
Messages: 9100
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 |
|
moehre
Messages: 43
Registered: June 2016
|
Member |
|
|
@Barbara thanks for helping me so much 
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 #652953 is a reply to message #652909] |
Wed, 22 June 2016 14:20 |
|
Barbara Boehmer
Messages: 9100
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 |
|
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 |
|
Barbara Boehmer
Messages: 9100
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 #652981 is a reply to message #652978] |
Thu, 23 June 2016 04:59 |
|
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 
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 |
|
Barbara Boehmer
Messages: 9100
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.
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 11:51:08 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
