google-bigquery - 如何在大查询中使用 ST_CONTAINS 加入地理列

Question

我有一个 BigQuery 表，其中包含地址，包括 Lat/Lng 和其他 BQ 表，其中包含从人口普查 shapefile 导入的工作几何定义。对于地址表中的每一行，我试图查找包含它的几何行。

以下查询是我查找的 INDIVIDUAL lat/lng 工作正常:

SELECT SLDLST FROM `geographies.tl_2018_sldl_*` sldl WHERE ST_CONTAINS(sldl.geom, ST_GEOGPOINT(-95.221080, 38.974500));

但是当我尝试抽象成一个连接时

SELECT 
  address_id,
  SLDLST
FROM `launchpad-239920.address_standardization.temp_delete_geo_match_sample` ssgolden
LEFT JOIN `geographies.tl_2018_sldl_*` sldl ON ST_CONTAINS(sldl.geom, ST_GEOGPOINT(ssgolden.longitude, ssgolden.latitude));

我得到一个错误:“如果连接两侧的字段相等，则不能使用 LEFT OUTER JOIN。”

如何重组我的连接查询以便能够为每个地址提取匹配的地理位置？

Answer 1

以下是 BigQuery 标准 SQL

如果你想在输出中保留不匹配的地址——你可以使用下面的

#standardSQL
WITH matched_addresses AS (
  SELECT 
    address_id,
    SLDLST
  FROM `launchpad-239920.address_standardization.temp_delete_geo_match_sample` ssgolden
  JOIN `geographies.tl_2018_sldl_X` sldl 
  ON ST_CONTAINS(sldl.geom, ST_GEOGPOINT(ssgolden.longitude, ssgolden.latitude)) 
)
SELECT * FROM matched_addresses UNION ALL 
SELECT address_id, NULL 
FROM `launchpad-239920.address_standardization.temp_delete_geo_match_sample`
WHERE NOT address_id IN (SELECT address_id FROM matched_addresses)   

但是如果你只对匹配的感兴趣——使用下面的一个

#standardSQL
WITH matched_addresses AS (
  SELECT 
    address_id,
    SLDLST
  FROM `launchpad-239920.address_standardization.temp_delete_geo_match_sample` ssgolden
  JOIN `geographies.tl_2018_sldl_X` sldl 
  ON ST_CONTAINS(sldl.geom, ST_GEOGPOINT(ssgolden.longitude, ssgolden.latitude)) 
)
SELECT * FROM matched_addresses