是否可以发布来自两个表的形状的空间连接? [英] 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;

但是我得到了

ERROR:  UnhandledServerException: java.lang.IllegalArgumentException: queryTerm must be a literal

示例架构:

create table if not exists A (
  id integer,
  geom geo_shape
);

create table if not exists B (
  id integer,
  wkt string index off
);

尝试使用wkt string的原因是由于文档的使用WKT文字.此外，由于我们的实际实现是可能要加入的可变几何形状，并且对象中不存在geo_shape，因此我们希望WKT可以与加入一起使用.

The reason for trying wkt string is due to the documentation's use of WKT literals. Additionally, since our actual implementation is a variable number of geometries that we might join against and geo_shape can't exist in an object, we were hoping to WKT would work with a join.

更新1 (每个扩大了Jacob的答案)，尝试使用以下模式将geo_shape加入geo_shape:

Update 1 (per Augmented Jacob's answer) Trying geo_shape join geo_shape with this schema:

create table if not exists B (
  id integer,
  wkt geo_shape
);

上面的查询产生了另一个错误:

And the above query produces a different error:

SQLParseException: Couldn't create executionContexts from NodeOperations
... original-error: Can't handle Symbol io.crate.analyze.symbol.MatchPredicate@6666c921 

而且，虽然错误不是理想的，但我不希望它继续起作用，因为

And, while the error isn't ideal, I wouldn't expect it to work anyway since the docs state:

注意

一个MATCH谓词不能合并联接的两个关系的列.

One MATCH predicate cannot combine columns of both relations of a join.

更新2 通过geo_shape连接geo_shape，虽然，至少在我们的2.4B行中，该性能使其几乎无法使用.

Update 2 With geo_shape join geo_shape, match doesn't work but within works, though, being "exact" queries, the performance makes it mostly unusable, at least across our 2.4B rows.

select A.*
  from A
  join B
    on within(B.wkt,A.geom);

推荐答案

如果您使用的是match谓词，则板条箱会利用生成的geo_shape的Lucene索引来获得真正的快速结果，而不是精确的"结果(当您已经注意到).但是，Lucene无法在两个关系(连接)上进行地理空间匹配，这就是为什么Crate无法做到这一点.该文档还在此处进行了说明:
https://crate.io/docs/reference/zh_CN/latest/sql/joins.html#join-conditions

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

这篇关于是否可以发布来自两个表的形状的空间连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！