Пространственное соединение в BigQuery

У меня есть три таблицы, одна из которых представляет собой полигональный слой. Тот, у которого есть дата начала и идентификатор:

Begin_Date                  ID
2015-12-14 00:00:00.0000000  1
2015-12-10 19:00:00.0000000  2
2015-12-09 19:00:00.0000000  3
2015-12-07 00:00:00.0000000  4
2015-12-06 00:00:00.0000000  5

И таблица со связанными местоположениями для этих записей на основе идентификатора:

DateAndTime                 ID
2015-12-14 00:00:00.0000000  1
2015-12-15 19:00:00.0000000  1
2015-12-16 19:00:00.0000000  1
2015-12-12 00:00:00.0000000  2
2015-12-13 00:00:00.0000000  2

Пока у меня есть запрос, который выбирает первую доступную точку. Далее я хочу сделать пространственное соединение на основе выбора точек с полигональным слоем. Результат покажет идентификатор, первую точку и полигон, в который она попадает.

Желаемый результат:

ID     point                          Area
35     POINT(-109.607635 40.876372)   TEST

Где я застрял:

    SELECT 
    t2.ID,
    ARRAY_AGG(ST_GeogPoint(Longitude, Latitude) ORDER BY t3.DateAndTime ASC LIMIT 1)[ORDINAL(1)] AS point,
    ST_GeogFromText(WKT) geo
    FROM `Table1` as t1, `Table2` t2
    INNER JOIN 
         `Table3` AS t3 
         ON t2.ID = t3.ID AND t3.DateAndTime >= t2.BeginDate
         AND ST_Within(ST_GeogPoint(t3.Longitude, t3.Latitude), ST_GeogFromText(t1.WKT))
    GROUP BY t2.ID;

Я не уверен, что делаю правильно несколько вещей: когда я использую функцию ST_Within, она не использует точки, выбранные в первой части запроса. Я также получаю ошибку: нераспознанное имя t3 в функции ST_within. Нужно ли мне где-то иметь вложенный оператор select?


google-bigquery spatial-query
person James    schedule 05.10.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Не совсем понятно, что такое входные схемы, поэтому это не полный запрос, но поскольку у вас есть запрос, который выбирает первую рабочую точку, просто используйте его как подзапрос:

SELECT 
    ST_GeogFromText(WKT) geo,
    ...
FROM `Table1` as t1
JOIN (
    SELECT ST_GeogPoint(t3.Longitude, t3.Latitude) AS StartPoint, 
           ...
        <rest of the query that selects the first point>
    ) AS t4
ON ST_Within(t4.StartPoint, ST_GeogFromText(t1.WKT))
person Michael Entin    schedule 06.10.2020
comment
Ах! Спасибо, это очень помогло. - person James; 06.10.2020