public static PlanMatchPattern spatialJoin(String expectedFilter, PlanMatchPattern left, PlanMatchPattern right) { return spatialJoin(expectedFilter, Optional.empty(), left, right); }
@Test public void testSpatialJoinIntersects() { // broadcast assertPlan("SELECT b.name, a.name " + "FROM (VALUES ('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))', 'a')) AS a (wkt, name), (VALUES ('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))', 'a')) AS b (wkt, name) " + "WHERE ST_Intersects(ST_GeometryFromText(a.wkt), ST_GeometryFromText(b.wkt))", anyTree( spatialJoin("st_intersects(geometry_a, geometry_b)", project(ImmutableMap.of("geometry_a", expression("ST_GeometryFromText(cast(wkt_a as varchar))")), anyTree(values(ImmutableMap.of("wkt_a", 0)))), anyTree(project(ImmutableMap.of("geometry_b", expression("ST_GeometryFromText(cast(wkt_b as varchar))")), anyTree(values(ImmutableMap.of("wkt_b", 0)))))))); // distributed assertDistributedPlan("SELECT b.name, a.name " + "FROM (VALUES ('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))', 'a')) AS a (wkt, name), (VALUES ('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))', 'a')) AS b (wkt, name) " + "WHERE ST_Intersects(ST_GeometryFromText(a.wkt), ST_GeometryFromText(b.wkt))", withSpatialPartitioning("default.kdb_tree"), anyTree( spatialJoin("st_intersects(geometry_a, geometry_b)", Optional.of(KDB_TREE_JSON), anyTree(unnest( project(ImmutableMap.of("partitions", expression(format("spatial_partitions(cast('%s' as kdbtree), geometry_a)", KDB_TREE_JSON))), project(ImmutableMap.of("geometry_a", expression("ST_GeometryFromText(cast(wkt_a as varchar))")), anyTree(values(ImmutableMap.of("wkt_a", 0))))))), anyTree( project(ImmutableMap.of("partitions", expression(format("spatial_partitions(cast('%s' as kdbtree), geometry_b)", KDB_TREE_JSON))), project(ImmutableMap.of("geometry_b", expression("ST_GeometryFromText(cast(wkt_b as varchar))")), anyTree(values(ImmutableMap.of("wkt_b", 0))))))))); }
@Test public void testPushDownBothArguments() { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression("ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))"), p.join(INNER, p.values(p.symbol("wkt", VARCHAR)), p.values(p.symbol("lat"), p.symbol("lng"))))) .matches( spatialJoin("ST_Contains(st_geometryfromtext, st_point)", project(ImmutableMap.of("st_geometryfromtext", expression("ST_GeometryFromText(wkt)")), values(ImmutableMap.of("wkt", 0))), project(ImmutableMap.of("st_point", expression("ST_Point(lng, lat)")), values(ImmutableMap.of("lat", 0, "lng", 1))))); }
@Test public void testPushDownOppositeOrder() { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression("ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))"), p.join(INNER, p.values(p.symbol("lat"), p.symbol("lng")), p.values(p.symbol("wkt", VARCHAR))))) .matches( spatialJoin("ST_Contains(st_geometryfromtext, st_point)", project(ImmutableMap.of("st_point", expression("ST_Point(lng, lat)")), values(ImmutableMap.of("lat", 0, "lng", 1))), project(ImmutableMap.of("st_geometryfromtext", expression("ST_GeometryFromText(wkt)")), values(ImmutableMap.of("wkt", 0))))); }
withSpatialPartitioning("kdb_tree"), anyTree( spatialJoin("st_contains(g1, g3)", Optional.of(KDB_TREE_JSON), anyTree(unnest(exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.REPARTITION, project(ImmutableMap.of("p1", expression(format("spatial_partitions(cast('%s' as kdbtree), g1)", KDB_TREE_JSON))), withSpatialPartitioning("kdb_tree"), anyTree( spatialJoin("st_contains(g1, g2)", Optional.of(KDB_TREE_JSON), anyTree(unnest( project(ImmutableMap.of("p1", expression(format("spatial_partitions(cast('%s' as kdbtree), g1)", KDB_TREE_JSON))),
"WHERE ST_Distance(ST_Point(a.lng, a.lat), ST_Point(b.lng, b.lat)) <= 3.1", anyTree( spatialJoin("st_distance(st_point_a, st_point_b) <= radius", project(ImmutableMap.of("st_point_a", expression("ST_Point(cast(a_lng as double), cast(a_lat as double))")), anyTree(values(ImmutableMap.of("a_lng", 0, "a_lat", 1)))), anyTree(project(ImmutableMap.of("st_point_b", expression("ST_Point(cast(b_lng as double), cast(b_lat as double))"), "radius", expression("3.1e0")), anyTree(values(ImmutableMap.of("b_lng", 0, "b_lat", 1)))))))); "WHERE ST_Distance(ST_Point(a.lng, a.lat), ST_Point(b.lng, b.lat)) <= 300 / (111321 * cos(radians(b.lat)))", anyTree( spatialJoin("st_distance(st_point_a, st_point_b) <= radius", project(ImmutableMap.of("st_point_a", expression("ST_Point(cast(a_lng as double), cast(a_lat as double))")), anyTree(values(ImmutableMap.of("a_lng", 0, "a_lat", 1)))), anyTree(project(ImmutableMap.of("st_point_b", expression("ST_Point(cast(b_lng as double), cast(b_lat as double))"), "radius", expression("3e2 / (111.321e3 * cos(radians(cast(b_lat as double))))")), anyTree(values(ImmutableMap.of("b_lng", 0, "b_lat", 1)))))))); withSpatialPartitioning("memory.default.kdb_tree"), anyTree( spatialJoin("st_distance(st_point_a, st_point_b) <= radius", Optional.of(KDB_TREE_JSON), anyTree( unnest(
"WHERE ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))", anyTree( spatialJoin("st_contains(st_geometryfromtext, st_point)", project(ImmutableMap.of("st_point", expression("ST_Point(lng, lat)")), anyTree(values(ImmutableMap.of("lng", 0, "lat", 1)))), anyTree(project(ImmutableMap.of("st_geometryfromtext", expression("ST_GeometryFromText(cast(wkt as varchar))")), anyTree(values(ImmutableMap.of("wkt", 0)))))))); "WHERE ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))", anyTree( spatialJoin("st_contains(st_geometryfromtext, st_point)", project(ImmutableMap.of("st_point", expression("ST_Point(lng, lat)"), "length", expression("length(name)")), anyTree(values(ImmutableMap.of("lng", 0, "lat", 1, "name", 2)))), withSpatialPartitioning("kdb_tree"), anyTree( spatialJoin("st_contains(st_geometryfromtext, st_point)", Optional.of(KDB_TREE_JSON), anyTree(unnest( project(ImmutableMap.of("partitions", expression(format("spatial_partitions(cast('%s' as kdbtree), st_point)", KDB_TREE_JSON))),
"WHERE ST_Within(ST_GeometryFromText(wkt), ST_Point(lng, lat))", anyTree( spatialJoin("st_within(st_geometryfromtext, st_point)", project(ImmutableMap.of("st_point", expression("ST_Point(lng, lat)")), anyTree(values(ImmutableMap.of("lng", 0, "lat", 1)))), anyTree(project(ImmutableMap.of("st_geometryfromtext", expression("ST_GeometryFromText(cast(wkt as varchar))")), anyTree(values(ImmutableMap.of("wkt", 0)))))))); "WHERE ST_Within(ST_GeometryFromText(wkt), ST_Point(lng, lat))", anyTree( spatialJoin("st_within(st_geometryfromtext, st_point)", project(ImmutableMap.of("st_point", expression("ST_Point(lng, lat)"), "length", expression("length(name)")), anyTree(values(ImmutableMap.of("lng", 0, "lat", 1, "name", 2)))), withSpatialPartitioning("kdb_tree"), anyTree( spatialJoin("st_within(st_geometryfromtext, st_point)", Optional.of(KDB_TREE_JSON), anyTree(unnest( project(ImmutableMap.of("partitions", expression(format("spatial_partitions(cast('%s' as kdbtree), st_point)", KDB_TREE_JSON))),
private void testSimpleDistanceQuery(String filter, String newFilter) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("a", GEOMETRY), p.symbol("name_a")), p.values(p.symbol("b", GEOMETRY), p.symbol("name_b"), p.symbol("r"))))) .matches( spatialJoin(newFilter, values(ImmutableMap.of("a", 0, "name_a", 1)), values(ImmutableMap.of("b", 0, "name_b", 1, "r", 2)))); }
private void testRadiusExpressionInDistanceQuery(String filter, String newFilter, String radiusExpression) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("a", GEOMETRY), p.symbol("name_a")), p.values(p.symbol("b", GEOMETRY), p.symbol("name_b"), p.symbol("r"))))) .matches( spatialJoin(newFilter, values(ImmutableMap.of("a", 0, "name_a", 1)), project(ImmutableMap.of("radius", expression(radiusExpression)), values(ImmutableMap.of("b", 0, "name_b", 1, "r", 2))))); }
private void testPointExpressionsInDistanceQuery(String filter, String newFilter, String radiusExpression) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("lat_a"), p.symbol("lng_a"), p.symbol("name_a")), p.values(p.symbol("lat_b"), p.symbol("lng_b"), p.symbol("name_b"))))) .matches( spatialJoin(newFilter, project(ImmutableMap.of("point_a", expression("ST_Point(lng_a, lat_a)")), values(ImmutableMap.of("lat_a", 0, "lng_a", 1, "name_a", 2))), project(ImmutableMap.of("point_b", expression("ST_Point(lng_b, lat_b)")), project(ImmutableMap.of("radius", expression(radiusExpression)), values(ImmutableMap.of("lat_b", 0, "lng_b", 1, "name_b", 2)))))); }
private void testPointAndRadiusExpressionsInDistanceQuery(String filter, String newFilter, String radiusExpression) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("lat_a"), p.symbol("lng_a"), p.symbol("name_a")), p.values(p.symbol("lat_b"), p.symbol("lng_b"), p.symbol("name_b"))))) .matches( spatialJoin(newFilter, project(ImmutableMap.of("point_a", expression("ST_Point(lng_a, lat_a)")), values(ImmutableMap.of("lat_a", 0, "lng_a", 1, "name_a", 2))), project(ImmutableMap.of("point_b", expression("ST_Point(lng_b, lat_b)")), project(ImmutableMap.of("radius", expression(radiusExpression)), values(ImmutableMap.of("lat_b", 0, "lng_b", 1, "name_b", 2)))))); }
@Test public void testPushDownAnd() { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression("name_1 != name_2 AND ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))"), p.join(INNER, p.values(p.symbol("wkt", VARCHAR), p.symbol("name_1")), p.values(p.symbol("lat"), p.symbol("lng"), p.symbol("name_2"))))) .matches( spatialJoin("name_1 != name_2 AND ST_Contains(st_geometryfromtext, st_point)", project(ImmutableMap.of("st_geometryfromtext", expression("ST_GeometryFromText(wkt)")), values(ImmutableMap.of("wkt", 0, "name_1", 1))), project(ImmutableMap.of("st_point", expression("ST_Point(lng, lat)")), values(ImmutableMap.of("lat", 0, "lng", 1, "name_2", 2))))); // Multiple spatial functions - only the first one is being processed assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression("ST_Contains(ST_GeometryFromText(wkt1), geometry1) AND ST_Contains(ST_GeometryFromText(wkt2), geometry2)"), p.join(INNER, p.values(p.symbol("wkt1", VARCHAR), p.symbol("wkt2", VARCHAR)), p.values(p.symbol("geometry1"), p.symbol("geometry2"))))) .matches( spatialJoin("ST_Contains(st_geometryfromtext, geometry1) AND ST_Contains(ST_GeometryFromText(wkt2), geometry2)", project(ImmutableMap.of("st_geometryfromtext", expression("ST_GeometryFromText(wkt1)")), values(ImmutableMap.of("wkt1", 0, "wkt2", 1))), values(ImmutableMap.of("geometry1", 0, "geometry2", 1)))); }
@Test public void testPushDownFirstArgument() { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression("ST_Contains(ST_GeometryFromText(wkt), point)"), p.join(INNER, p.values(p.symbol("wkt", VARCHAR)), p.values(p.symbol("point", GEOMETRY))))) .matches( spatialJoin("ST_Contains(st_geometryfromtext, point)", project(ImmutableMap.of("st_geometryfromtext", expression("ST_GeometryFromText(wkt)")), values(ImmutableMap.of("wkt", 0))), values(ImmutableMap.of("point", 0)))); assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression("ST_Contains(ST_GeometryFromText(wkt), ST_Point(0, 0))"), p.join(INNER, p.values(p.symbol("wkt", VARCHAR)), p.values()))) .doesNotFire(); }
p.values(p.symbol("b"))))) .matches( spatialJoin("ST_Contains(a, b)", values(ImmutableMap.of("a", 0)), values(ImmutableMap.of("b", 0)))); p.values(p.symbol("b"), p.symbol("name_2"))))) .matches( spatialJoin("name_1 != name_2 AND ST_Contains(a, b)", values(ImmutableMap.of("a", 0, "name_1", 1)), values(ImmutableMap.of("b", 0, "name_2", 1)))); p.values(p.symbol("b1"), p.symbol("b2"))))) .matches( spatialJoin("ST_Contains(a1, b1) AND ST_Contains(a2, b2)", values(ImmutableMap.of("a1", 0, "a2", 1)), values(ImmutableMap.of("b1", 0, "b2", 1))));
@Test public void testPushDownSecondArgument() { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression("ST_Contains(polygon, ST_Point(lng, lat))"), p.join(INNER, p.values(p.symbol("polygon", GEOMETRY)), p.values(p.symbol("lat"), p.symbol("lng"))))) .matches( spatialJoin("ST_Contains(polygon, st_point)", values(ImmutableMap.of("polygon", 0)), project(ImmutableMap.of("st_point", expression("ST_Point(lng, lat)")), values(ImmutableMap.of("lat", 0, "lng", 1))))); assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression("ST_Contains(ST_GeometryFromText('POLYGON ...'), ST_Point(lng, lat))"), p.join(INNER, p.values(), p.values(p.symbol("lat"), p.symbol("lng"))))) .doesNotFire(); }
@Test public void testPushDownBothArguments() { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression("ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))"), p.join(INNER, p.values(p.symbol("wkt", VARCHAR)), p.values(p.symbol("lat"), p.symbol("lng"))))) .matches( spatialJoin("ST_Contains(st_geometryfromtext, st_point)", project(ImmutableMap.of("st_geometryfromtext", expression("ST_GeometryFromText(wkt)")), values(ImmutableMap.of("wkt", 0))), project(ImmutableMap.of("st_point", expression("ST_Point(lng, lat)")), values(ImmutableMap.of("lat", 0, "lng", 1))))); }
private void testPointExpressionsInDistanceQuery(String filter, String newFilter, String radiusExpression) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("lat_a"), p.symbol("lng_a"), p.symbol("name_a")), p.values(p.symbol("lat_b"), p.symbol("lng_b"), p.symbol("name_b"))))) .matches( spatialJoin(newFilter, project(ImmutableMap.of("point_a", expression("ST_Point(lng_a, lat_a)")), values(ImmutableMap.of("lat_a", 0, "lng_a", 1, "name_a", 2))), project(ImmutableMap.of("point_b", expression("ST_Point(lng_b, lat_b)")), project(ImmutableMap.of("radius", expression(radiusExpression)), values(ImmutableMap.of("lat_b", 0, "lng_b", 1, "name_b", 2)))))); }
private void testRadiusExpressionInDistanceQuery(String filter, String newFilter, String radiusExpression) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("a"), p.symbol("name_a")), p.values(p.symbol("b"), p.symbol("name_b"), p.symbol("r"))))) .matches( spatialJoin(newFilter, values(ImmutableMap.of("a", 0, "name_a", 1)), project(ImmutableMap.of("radius", expression(radiusExpression)), values(ImmutableMap.of("b", 0, "name_b", 1, "r", 2))))); }
private void testSimpleDistanceQuery(String filter, String newFilter) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("a"), p.symbol("name_a")), p.values(p.symbol("b"), p.symbol("name_b"), p.symbol("r"))))) .matches( spatialJoin(newFilter, values(ImmutableMap.of("a", 0, "name_a", 1)), values(ImmutableMap.of("b", 0, "name_b", 1, "r", 2)))); }