2

I've once found a setting in Oracle SQL Developer, allowing to display the geometry column as coordinates, instead of the generic geometry object name (e.g. MDSYS.SDO_GEOMETRY).

I cannot find it anymore, where is this setting located?

Note: I've also posted this question on dba.stackexchange.com

2 Answers 2

5

You can double-click one of those objects. That bring up a little pencil icon on the side. Clicking that brings up an "edit" window that shows the object content.

That works fine for points. Hard to read when you deal with complex lines or polygons.

Note that for points, you can get the X and Y values easily like this:

select id, p.location.sdo_point.x as longitude, p.location.sdo_point.y as latitude from us_cities p;

which returns:

    ID  LONGITUDE   LATITUDE
------ ---------- ----------
     1 -73.943849    40.6698
     2  -118.4112  34.112101
     3 -87.684965   41.83705

EDIT

There is actually a setting that helps. Check Preferences then Database/Advanced. You will see a tick box marked Display Struct Value in Grid.

When ticked, then the content of objects is shown inside the result grid. Not the full content though: only what fits the grid column. For point geometries, you should be able to see it all. For more complex shapes, you will need to click the object and the "edit" pencil to see everything.

And then also mousing over the object brings up a pop-up that shows the content of the object (not all of it though).

5
  • Thank you, but it's not this I'm looking for. By changing this setting, the coordinates were immediately visible in the table for all records. This also applied to the USER_SDO_GEOM_METADATA table.
    – GISGe
    Mar 29, 2018 at 6:10
  • I am not aware of any other way to see object contents in sql developer. It is notoriously weak in anything that is not plain relational data. Mar 30, 2018 at 8:44
  • 1
    I actually decided to explore all the available preferences. I found the following in "Database/Advanced": a tick box marked "Display Struct Value in Grid". When ticked, then the content of objects is shown inside the result grid. Maybe that is what you meant ? You still need to double-click and edit to see the full content though. Mar 30, 2018 at 8:56
  • Yes! It's what I was looking for. Thanks! I don't need to double-click and edit, everything is in there but of course for lines and polygons you can't see all coordinates at once, the column (or even the screen) is not broad enough. I like using this option when viewing the USER_SDO_GEOM_METADATA table mostly. Could you please update your answer, then I will mark it as accepted.
    – GISGe
    Mar 30, 2018 at 12:13
  • 1
    Just edited my answer. I did not know about this setting, and I find it useful too. Mar 31, 2018 at 14:17
0

I find useful viewing geometry as WKT, which contains all coodinates - use this SQL:

select sdo_util.to_wktgeometry(shape) shape_wkt, shape, id from TABLE where id = 123;

Since WKT is "copy&paste friendly", you can then use it with e.g. online WKT viewer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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