1

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.

2
  • 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 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 at 20:50
2

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

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.

1
  • 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). – User1973 Jan 15 at 0:20

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.