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.

  • By what do you mean "don't see any spatial operations that return an attribute"? SQL queries haven't really changed since 3.0 days. Theme-on-theme SQL query is unchanged, and still has the requirement to share a SpatialReference between the tables.
    – Vince
    Jan 14, 2021 at 19:42
  • It's not clear why you have to take the ST_PointOnSurface of a buffer of a point (which really ought to snap back to the start, but really slowly), but yes, this is just a simple ST_Intersects join.
    – Vince
    Jan 14, 2021 at 20:50

1 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.

        --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 
        .barrier --line FC
    ) ba
left join 
    zones z --polygon FC
    on sde.st_intersects(ba.midpoint, z.shape) = 1

enter image description here

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.

  • Upon further testing, the way I calculated the line midpoints doesn't seem to be a good approach. The midpoint doesn't get placed at the correct position along the line (especially curved lines). So, I think a true midpoint calculation would be better (example: a linear referencing solution).
    – User1974
    Jan 15, 2021 at 0:20

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.