Update a table with values from coordinates on GEOMETRY in Oracle

Question

I have 2 columns in a table that represent the X and Y coordinates. The values in those columns don't match the actual coordinates and I want to update them with the coordinates values that are selected from the GEOMETRY column. I have been trying the following:

update TABLE1
set X = (SELECT t.X FROM TABLE1 Tb, table(SDO_UTIL.GETVERTICES(Tb.GEOMETRY )) t);

but I am getting the following error:

ORA-01427: single-row subquery returns more than one row

Any suggestions?

Answer 1

The solution depends on the geometry of your layer.

Point Geometries

For points you can access the X and Y co-ordinates directly:

update TABLE1 s1 set X = s1.GEOMETRY.SDO_POINT.x,y=s1.geometry.sdo_point.y

Polygon, Multi-point

SDO_UTIL.GETVERTICES will return all vertices of your geometry, and your previous query would fail if the geometry had more than one vertice, or if t1 had more than one row.

You can use a function to calculate the centroid or label point of the polygon and use that to populate your columns. This is complicated by the way updates with joins work in Oracle.

update table1 t1 set x = 
(select c.centroid.sdo_point.x from 
  (select sdo_geom.sdo_centroid(shape,1) centroid,rowid from table1) c
 where c.rowid=t1.rowid)