Is It Possible To Issue A Spatial Join With Shapes From Two Tables?

I want to be able to run a query like: select A.* from A join B on match(A.geom,B.wkt) using within; But i get: ERROR: UnhandledServerException: java.lang.IllegalArgument

Solution 1:

If you are using the match predicate Crate leverages the generated Lucene index of the geo_shape to get really fast but not "exact" results (as you already noticed). However, a geospatial match on two relations (join) is not possible with Lucene and that is why Crate is not able to do it. The documentation explains it also here: https://crate.io/docs/reference/en/latest/sql/joins.html#join-conditions

Solution 2:

Your Variable in Table A geom is of type geo_shape whereas B has wkt of type string.

Change them to matching types and it should solve your java.lang.IllegalArgumentException