Hi,
I need some help with calculation the Centroid of multiple polygons.
I am using Oracle 11g Locator!
Table Building:
| ID | ... | CENTROID_GEOM |
|---|
| 1 | ... | null |
| 2 | ... | null |
| 3 | ... | null |
Table Thematic_Surface: (35 = ground polygon)
| BUILDING_ID | LOD2S2_MULTI_SURFACE_ID | OBJECTCLASS_ID |
|---|
| 1 | 9 | 33 |
| 1 | 11 | 34 |
| 1 | 13 | 34 |
| 1 | 15 | 34 |
| 1 | 17 | 34 |
| 1 | 19 | 35 |
| 1 | 20 | 35 |
Table Surface_Geometry:
| ROOT_ID | GEOMETRY |
|---|
| 19 | ... |
| 20 | ... |
My query looks like:
update building
set centroid_geom = (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);
It works only for buildings with one ground polygon!!!
The problem is as you can see in table building sometimes (not always) I have multiple ground surfaces.
Because of that I get the error message: " single-row subquery returns more than one row".
In spatial I guess you can use the SDO_AGGR_CENTROID function instead of SDO_GEOM.SDO_CENTROID!
But I am not allowed to use this function because I only have Locator extension.
So is there an alternative how to solve this in locator?