4

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?

vvvvv
  • 25,404
  • 19
  • 49
  • 81
user2308155
  • 45
  • 1
  • 1
  • 4

1 Answers1

3

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
Jordan Tigani
  • 26,089
  • 4
  • 60
  • 63
  • Jordan, any news when this release is expected? – N.N. Dec 05 '13 at 10:00
  • is there something unique about syntax? I am still receiving "Error: ON clause must be AND of = comparisons of one field name from each table, with all field names prefixed with table name" – N.N. Dec 08 '13 at 06:59
  • With a CROSS JOIN you don't use ON -- just use a WHERE restriction. – Jordan Tigani Dec 09 '13 at 20:46
  • @JordanTigani That's good that BigQuery now supports CROSS JOIN! Just wanted to mention that it's not yet in the syntax documentation: https://developers.google.com/bigquery/query-reference#joins – YABADABADOU Jan 30 '14 at 15:37
  • Thanks, we'll be updating the docs soon to add some new query syntax features and functions that are as-of-yet undocumented. – Jordan Tigani Feb 07 '14 at 17:26