How to cross join in Big Query using intervals?

Question

How can I join two tables using intervals in Google Big Query?

I have two table:

Table CarsGPS:
ID | Car | Latitude | Longitude
1  | 1   | -22.123  | -43.123
2  | 1   | -22.234  | -43.234
3  | 2   | -22.567  | -43.567
4  | 2   | -22.678  | -43.678
...

Table Areas:
ID | LatitudeMin | LatitudeMax | LongitudeMin | LongitudeMax
1  | -22.124     | -22.120     | -43.124      | -43.120
2  | -22.128     | -22.124     | -43.128      | -43.124
...

I'd like to cross join these tables to check in which areas each car has passed by using Google Big Query.

In a regular SQL server I would make:

SELECT A.ID, C.Car 
FROM Cars C, Areas A
WHERE C.Latitude BETWEEN A.LatitudeMin AND A.LatitudeMax AND
      C.Longitude BETWEEN A.LongitudeMin AND A.LongitudeMax

But Google Big Query only allows me to do joins (even JOIN EACH) using exact matches among joined tables. And the "FROM X, Y" means UNION, not JOINS.

So, this is not an option:

SELECT A.ID, C.Car 
FROM Cars C
JOIN EACH
  Areas A
  ON C.Latitude BETWEEN A.LatitudeMin AND A.LatitudeMax AND
     C.Longitude BETWEEN A.LongitudeMin AND A.LongitudeMax

Then, how can I run something similar to it to identify which cars passed inside each area?

Answer 1

BigQuery now supports CROSS JOIN. Your query would look like:

SELECT A.ID, C.Car 
FROM Cars C 
CROSS JOIN Areas A
WHERE C.Latitude BETWEEN A.LatitudeMin AND A.LatitudeMax AND
  C.Longitude BETWEEN A.LongitudeMin AND A.LongitudeMax