inverted_join_geospatial

statement ok
CREATE TABLE ltable(
  lk int primary key,
  geom1 geometry,
  geom2 geometry
)

statement ok
INSERT INTO ltable VALUES
  (1, 'POINT(3.0 3.0)', 'POINT(3.0 3.0)'),
  (2, 'POINT(4.5 4.5)', 'POINT(3.0 3.0)'),
  (3, 'POINT(1.5 1.5)', 'POINT(3.0 3.0)'),
  (4, NULL, 'POINT(3.0 3.0)'),
  (5, 'POINT(1.5 1.5)', NULL),
  (6, NULL, NULL)

statement ok
CREATE TABLE rtable(
  rk int primary key,
  geom geometry,
  INVERTED INDEX geom_index(geom)
)

statement ok
INSERT INTO rtable VALUES
  (11, 'POINT(1.0 1.0)'),
  (12, 'LINESTRING(1.0 1.0, 2.0 2.0)'),
  (13, 'POINT(3.0 3.0)'),
  (14, 'LINESTRING(4.0 4.0, 5.0 5.0)'),
  (15, 'LINESTRING(40.0 40.0, 41.0 41.0)'),
  (16, 'POLYGON((1.0 1.0, 5.0 1.0, 5.0 5.0, 1.0 5.0, 1.0 1.0))')

query II
SELECT lk, rk FROM ltable JOIN rtable@geom_index ON ST_Intersects(ltable.geom1, rtable.geom) ORDER BY (lk, rk)
----
1  13
1  16
2  14
2  16
3  12
3  16
5  12
5  16

query II
SELECT lk, rk FROM ltable JOIN rtable@geom_index ON ST_DWithin(ltable.geom1, rtable.geom, 2) ORDER BY (lk, rk)
----
1  12
1  13
1  14
1  16
2  14
2  16
3  11
3  12
3  16
5  11
5  12
5  16

query II
SELECT lk, rk FROM ltable JOIN rtable@geom_index
ON ST_Intersects(rtable.geom, ltable.geom1) OR ST_DWithin(ltable.geom1, rtable.geom, 2) ORDER BY (lk, rk)
----
1  12
1  13
1  14
1  16
2  14
2  16
3  11
3  12
3  16
5  11
5  12
5  16

query II
SELECT lk, rk FROM ltable JOIN rtable@geom_index
ON ST_Intersects(ltable.geom1, rtable.geom) AND ST_DWithin(rtable.geom, ltable.geom1, 2) ORDER BY (lk, rk)
----
1  13
1  16
2  14
2  16
3  12
3  16
5  12
5  16

query II
SELECT lk, rk FROM ltable JOIN rtable@geom_index
ON ST_Intersects(ltable.geom1, rtable.geom) AND ST_DWithin(rtable.geom, ltable.geom2, 2) ORDER BY (lk, rk)
----
1  13
1  16
2  14
2  16
3  12
3  16

query II
SELECT lk, rk FROM ltable JOIN rtable@geom_index
ON ST_Intersects(ltable.geom1, rtable.geom) OR ST_DWithin(rtable.geom, ltable.geom2, 2) ORDER BY (lk, rk)
----
1  12
1  13
1  14
1  16
2  12
2  13
2  14
2  16
3  12
3  13
3  14
3  16
4  12
4  13
4  14
4  16
5  12
5  16

# Run the same two queries with the primary index to verify that we get the
# same results.
query II
SELECT lk, rk FROM ltable JOIN rtable@primary
ON ST_Intersects(ltable.geom1, rtable.geom) AND ST_DWithin(rtable.geom, ltable.geom2, 2) ORDER BY (lk, rk)
----
1  13
1  16
2  14
2  16
3  12
3  16

query II
SELECT lk, rk FROM ltable JOIN rtable@primary
ON ST_Intersects(ltable.geom1, rtable.geom) OR ST_DWithin(rtable.geom, ltable.geom2, 2) ORDER BY (lk, rk)
----
1  12
1  13
1  14
1  16
2  12
2  13
2  14
2  16
3  12
3  13
3  14
3  16
4  12
4  13
4  14
4  16
5  12
5  16

query II
SELECT ltable.lk, rtable.rk FROM ltable JOIN rtable@geom_index
ON ST_Intersects(ltable.geom1, rtable.geom) AND ST_Covers(ltable.geom2, rtable.geom)
AND (ST_DFullyWithin(rtable.geom, ltable.geom1, 100) OR ST_Intersects('POINT(1.0 1.0)', rtable.geom))
----
1  13

# The following queries perform semi- and anti-joins, and don't currently use
# an inverted join.
query I
SELECT lk FROM ltable WHERE EXISTS (SELECT * FROM rtable WHERE ST_Intersects(ltable.geom2, rtable.geom))
ORDER BY lk
----
1
2
3
4

query I
SELECT rk FROM rtable WHERE EXISTS (SELECT * FROM ltable WHERE ST_Intersects(ltable.geom2, rtable.geom))
ORDER BY rk
----
13
16

query I
SELECT lk FROM ltable WHERE NOT EXISTS (SELECT * FROM rtable WHERE ST_Intersects(ltable.geom2, rtable.geom))
ORDER BY lk
----
5
6

query I
SELECT rk FROM rtable WHERE NOT EXISTS (SELECT * FROM ltable WHERE ST_Intersects(ltable.geom2, rtable.geom))
ORDER BY rk
----
11
12
14
15

# Bounding box operations.
query II
SELECT lk, rk FROM ltable JOIN rtable@geom_index ON ltable.geom1 ~ rtable.geom
ORDER BY lk, rk
----
1  13

query II
SELECT lk, rk FROM ltable JOIN rtable@geom_index ON rtable.geom ~ ltable.geom1
ORDER BY lk, rk
----
1  13
1  16
2  14
2  16
3  12
3  16
5  12
5  16

query II
SELECT lk, rk FROM ltable JOIN rtable@geom_index ON rtable.geom && ltable.geom1
ORDER BY lk, rk
----
1  13
1  16
2  14
2  16
3  12
3  16
5  12
5  16