如何在Big查询中使用ST_CONTAINS加入地理列 [英] How to JOIN in geography columns using ST_CONTAINS in Big query

问题描述

我有一个BigQuery表，其地址包括纬度/经度和其他BQ表，这些表具有从人口普查shapefile导入的有效geom定义.对于地址表中的每一行，我正在尝试查找包含它的geom行.

I have one BigQuery table with addresses including Lat/Lng and other BQ tables with working geom definitions imported from Census shapefiles. For each row in addresses table I am attempting to lookup which geom row contains it.

以下查询是我查找的lat/lng个人作品正常吗?

The following query were I look up an INDIVIDUAL lat/lng works fine:

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

但是当我尝试将抽象抽象为连接时

But when I attempt abstract into a join like

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."

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

How do I restructure my join query to be able to extract the matching geography for each address?

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

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

If you want to preserve not matching addresses in output - you can use below

#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)   

但如果您只对匹配项感兴趣，请在-1以下使用

but if you interested in only matched - use below one

#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  

这篇关于如何在Big查询中使用ST_CONTAINS加入地理列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！