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?
Best Answer
The solution depends on the geometry of your layer.
Point Geometries
For points you can access the X and Y co-ordinates directly:
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.