Query Layer (Oracle) using SDO_BUFFER does not show(draw) geometry

Question

Hello everyone,
I am facing some issues with the Query Layer and would appreciate it if someone could assist me in resolving this problem. I am working with an Oracle 19c database that includes the Spatial Data Module, and it contains a view displaying real-time mobile positions. My goal is to represent a security area on the map as a circle with a radius of 500 meters.
To achieve this, I attempted to add a query layer using the following SQL query:
```sql
SELECT ID, SDO_BUFFER(geom, 0.5, 0.5, 'unit=km arc_tolerance=0.5') FROM vw_position_mobile

, but it does not draw on the map. Any clue about that?

MrRock78 · Answer

Thank you for your response. I tried changing the field name, but it didn't work. The example you gave might be effective because it uses the st_geometry library, but I am using sdo_geometr

MrRock78 · Answer

Yes, it works, but it displays a point instead of a polygon.

AlfredBaldenweck · Answer

Take this with a grain of salt because I've never used Oracle and the documentation  isn't super backing me up here.

You can return spatial sql at least with Microsoft SQL Server.

Idk about Oracle specifically, but for SQL Server I got a similar thing to work by manually casting the result of my buffer to geometry (or geography, depending on what you're doing) and also assigning it as the SHAPE field

//returns a 1 mile buffer for each feature

select OBJECTID,
ADM_UNIT_CD,
ADMU_NAME,
geography:: STPolyFromText(SHAPE.STAsText(), 4269).STBuffer(1609.344) AS shape
from gdb.FeatureClass

I'd start by at least assigning the buffer as the SHAPE field and see if that helps?

SELECT 
ID, 
SDO_BUFFER(geom, 0.5, 0.5, 'unit=km arc_tolerance=0.5') As shape 
FROM vw_position_mobile

The documentation that I linked above appears to not care about whether you are doing that, but Arc is a little finicky, so I'd try it just in case.