I have an Oracle Spatial table (in 12c) with a polygon geometry type. I added a text column to the table that I would like to populate with the well-known text of the polygon's centroid. In concept this is really simple but I'm having trouble using the SDO_CENTROID function properly. This is my SQL statement so far:

update parcel
set centroid_wkt = sdo_util.to_wktgeometry(sdo_geom.sdo_centroid(shape,  usgm.diminfo))
from user_sdo_geom_metadata usgm;

When I execute this it tells me SQL Error: ORA-00933: SQL command not properly ended. Can anyone tell what I'm doing wrong here? My guess is that the UPDATE... SET... FROM... syntax doesn't work in Oracle, but I'm not sure how else to select the diminfo array.

  • 1
    For what do you need anything from sdo_geom_metadata? – user30184 Aug 20 '15 at 16:17
  • It's a required argument, isn't it? docs.oracle.com/cd/B19306_01/appdev.102/b14255/… – Rob Aug 20 '15 at 18:34
  • You are right, I did not remember how tricky it is with Oracle. The update query will be even more tricky because you will need to make a sub-query and use alias for it. I may come back to this but only after a week or so, sorry. – user30184 Aug 20 '15 at 22:21
  • It is NOT a required argument. It is just there to get the tolerance for the operation. But you can just pass that directly. – Albert Godfrind Aug 21 '15 at 10:10

You do not need to pass the metadata. Just pass your tolerance setting explicitly, like this:

update parcel
set centroid_wkt = sdo_util.to_wktgeometry(sdo_geom.sdo_centroid(shape,0.05);

Assuming your data is in geodetic coordinates, or is in a projection that uses meters as base unit, then 0.05 means 5cm.


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.