[SEDONA-260] More intuitive configuration of partition and index-build side of spatial joins in Sedona SQL

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.4.0
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Kristin Cowalcijk
• Created: 12/Mar/23 04:20
• Updated: 13/Mar/23 21:33
• Resolved: 13/Mar/23 21:33

Description

Apache Sedona allows configuring the dominant side of spatial partitioning and the side on which Sedona builds spatial indices (See Apache Sedona - parameters). However, the way Apache Sedona defines the left and right sides of the join is quite counterintuitive.

For queries such as SELECT * FROM A JOIN B ON ST_(left, right), or df_a.join(df_b, expr("ST_(left, right)")), the left-side relation and right-side relation were completely determined by the join condition: ST_*(left, right). The relation left references to is the left-side relation, and the relation right references to is the right-side relation.

For example, the left side relation of the following query is `df_msb`, even though it appears to be on the right side of the join:

SELECT * FROM df_pickup JOIN df_msb ON ST_Contains(df_msb.geom, df_pickup.pickup)

If we replace the join condition with ST_Within(df_pickup.pickup, df_msb.geom), df_pickup becomes the left-side relation.

A more intuitive way is to always treat df_pickup as the left-side relation, regardless of how the join condition is written.
 
 

Issue Links

• links to: GitHub Pull Request #795