Блог

У меня есть строка 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.

Ниже приведен стандартный SQL BigQuery

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