时间戳

首页 > 解决方案 > 如何在 BigQuery 中从 GeoJSON 中提取单独的值

sql - 如何在 BigQuery 中从 GeoJSON 中提取单独的值

问题描述

我有一个用于多点几何的 GeoJSON 字符串。我想将这些点中的每一个提取到 BigQuery 中的单个点几何表中

我已经能够为其中一个点实现点几何。我想以自动化的方式为所有其他人做这件事。我已经尝试将字符串转换为数组，但它仍然是一个大小为 1 的数组，整个内容为单个字符串。

这对我有用，我能够提取一个点并将其转换为几何图形

WITH temp_table as (select '{ "type": "MultiPoint", "coordinates": [ [ 20, 10 ], [ 30, 5 ], [ 90, 50 ], [ 40, 80 ] ]  }' as string) 

select ST_GEOGPOINT(CAST(JSON_EXTRACT(string, '$.coordinates[0][0]') as FLOAT64), CAST(JSON_EXTRACT(string, '$.coordinates[0][1]') as FLOAT64))  from temp_table

这导致POINT(20 10)

我可以为这些点中的每一个编写手动查询并执行UNION ALL但不会每次都扩展或工作。我想实现这一点，以便它能够以自动化的方式完成。出于架构目的，我们不能在 Python 等语言中进行字符串操作。

标签： sqlgoogle-bigquerygisgeojson

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
SELECT 
  ARRAY(
    SELECT ST_GEOGPOINT(
      CAST(SPLIT(pair)[OFFSET(0)] AS FLOAT64), CAST(SPLIT(pair)[SAFE_OFFSET(1)] AS FLOAT64)) 
    FROM UNNEST(REGEXP_EXTRACT_ALL(JSON_EXTRACT(STRING, '$.coordinates'), r'\[(\d+,\d+)\]')) pair
  ) points
FROM `project.dataset.temp_table`

您可以使用您问题中的示例数据进行测试，使用上面的示例数据，如下例所示 

#standardSQL
WITH `project.dataset.temp_table` AS (
  SELECT '{ "type": "MultiPoint", "coordinates": [ [ 20, 10 ], [ 30, 5 ], [ 90, 50 ], [ 40, 80 ] ]  }' AS STRING
) 
SELECT 
  ARRAY(
    SELECT ST_GEOGPOINT(
      CAST(SPLIT(pair)[OFFSET(0)] AS FLOAT64), CAST(SPLIT(pair)[SAFE_OFFSET(1)] AS FLOAT64)) 
    FROM UNNEST(REGEXP_EXTRACT_ALL(JSON_EXTRACT(STRING, '$.coordinates'), r'\[(\d+,\d+)\]')) pair
  ) points
FROM `project.dataset.temp_table`

结果 

Row points   
1   POINT(20 10)     
    POINT(30 5)  
    POINT(90 50)     
    POINT(40 80)

注意：在上述版本中 - 为每个相应的原始行生成点数组。显然，您可以将其调整为扁平，如下例所示 

#standardSQL
WITH `project.dataset.temp_table` AS (
  SELECT '{ "type": "MultiPoint", "coordinates": [ [ 20, 10 ], [ 30, 5 ], [ 90, 50 ], [ 40, 80 ] ]  }' AS STRING
) 
SELECT 
  ST_GEOGPOINT(
      CAST(SPLIT(pair)[OFFSET(0)] AS FLOAT64), CAST(SPLIT(pair)[SAFE_OFFSET(1)] AS FLOAT64)
  ) points
FROM `project.dataset.temp_table`, UNNEST(REGEXP_EXTRACT_ALL(JSON_EXTRACT(STRING, '$.coordinates'), r'\[(\d+,\d+)\]')) pair

结果 

Row points   
1   POINT(20 10)     
2   POINT(30 5)  
3   POINT(90 50)     
4   POINT(40 80)

推荐阅读