Enable left outer/outer joins in SpatialJoinQuery

[steven1u]

As for now, the SpatialJoinQuery is an inner join between 2 spatial RDDs. But sometimes, you may need to keep all rows from the left RDD, without having to do another join to get back the rows that didn't match. In this case, enabling left outer/outer spatial joins would be really convenient.

In the meanwhile, a possible hack would be to include the world bounding box (-180,-90,180,90) in the right RDD so that each row from the left RDD at least matches the world bbox and thus will be included in the output. But this implies more indexing and computation.

Thanks 🙂

[jiayuasu]

@steven1u I agree. Outer joins are useful in some cases. It is not that hard to implement. Will do.

[steven1u]

@jiayuasu Thank you ! 🙂

[mispecto]

Just out of curiosity, what's the problem with the following query:

SELECT
  table_to_enrich.*,
  polygons.area_name AS area_name
FROM
  table_to_enrich
LEFT OUTER JOIN
  polygons
ON
  ST_Within(
    ST_Point(
      CAST(table_to_enrich.longitude AS Decimal(24,20)),
      CAST(table_to_enrich.latitude AS Decimal(24,20))
    ),
    polygons.area_polygon)

Where schema of table_of_enrich is:

latitude:Double	longitude:Double	col1	col2	col3	...

and schema of polygons is:

area_name:String	area_polygon:Geometry

It seems to provide correct results.

[mispecto]

Ah, I see, this query doesn't use spatial join.

[steven1u]

@jiayuasu Do you know if you could handle this please?

[yortuc]

@spektom this approach yields tremendous performance degradation, any ideas?

Instead, I do the spatial join and join to the original dataframe again. Even this approach looks much faster.