3

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?

  • add rownum=1 to your query – Mapperz Sep 23 '15 at 14:17
  • No, that would populate all rows with the coordinate in row 1. – Vince Sep 23 '15 at 14:19
  • @ Mapperz as @Vince said, that populated the table with values from the first row only! – GIS_DBA Sep 23 '15 at 14:21
5

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)
  • @LatvianGIS, you're welcome! If you are looking for a great source of info on Oracle spatial then I can recommend Pro Oracle Spatial which I found very useful for me getting started. – kes Sep 24 '15 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.