ST_GEOMETRY points: get attribute of intersecting polygon

Question

I have an SDE.ST_GEOEMTRY point feature class.

I want to create an SQL query from the points. The query would perform a spatial join/intersection with an SDE.ST_GEOMETRY polygon feature class. An attribute would be returned from the polygon that the points intersect.

• The polygons don't overlap, so a point would never intersect multiple polygons.
• The query would be output to a static table on a schedule. So performance is not a major concern.

Is there a way to do this with SDE.ST_GEOMETRY functions? I've looked at the function list, but I don't see any spatial operations that return an attribute.

Answer 1

I think I figured it out. I just hadn't done a spatial join with SQL until now.

I ended up doing the spatial join on a line FC instead of a point FC (but I convert the lines to midpoints in the subquery).

--Spatial join between line midpoints and and a polygon FC. 
--Returns the zone ID from the intersecting polygon.

select 
    ba.ba_id,
    z.zone
from 
    (
    select
        ba_id,
        --Get the line's midpoint to avoid the line overlapping multiple polygons 
        --More info here: https://stackoverflow.com/a/65723767/10936066
        sde.st_pointonsurface(sde.st_buffer(shape, 0.05)) as midpoint 
    from
        .barrier --line FC
    ) ba
left join 
    zones z --polygon FC
    on sde.st_intersects(ba.midpoint, z.shape) = 1

---

Source: What's the proper way to do a spatial join in Oracle?

Note: The datatype in that post is SDO_GEOMETRY, not SDE.ST_GEOMETRY.