How to export entire BigQuery table to GeoJSON-CodePudding

I have a BigQuery table:

where the geometry column is of type GEOGRAPHY:

I would like to export this table to GeoJSON format. I know how to export a table to simple JSON (see

Problem with this is that it applies to individual rows, not entire column, nor table.

Expected output:

{
"type": "FeatureCollection",
"features": [
{ "type": "Feature", "properties": { "description": "aaa" }, "geometry": { "type": "Point", "coordinates": [ 60.0, 10.0 ] } },
{ "type": "Feature", "properties": { "description": "bbb" }, "geometry": { "type": "Point", "coordinates": [ 65.0, 15.0 ] } }
]
}

How can I get this GeoJSON string or file from the BigQuery table?

CodePudding user response：

Following query will generate the output you are expecting.

WITH geodata AS (
  SELECT ST_GEOGPOINT(60., 10.) AS geometry, 'aaa' AS description
   UNION ALL
  SELECT ST_GEOGPOINT(65., 15.) AS geometry, 'bbb' AS description
),
ndjson AS (
  SELECT STRUCT(
           "Feature" AS type, 
           STRUCT(description) AS properties,
           STRUCT(
             "Point" AS type, 
             [ST_X(geometry),ST_Y(geometry)] AS coordinates
           ) AS geometry
         ) json
    FROM geodata
)
SELECT TO_JSON_STRING(STRUCT(
         "FeatureCollection" AS type,
         ARRAY_AGG(json) AS features
       )) 
  FROM ndjson;

output:

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {
        "description": "aaa"
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          59.99999999999999,
          10
        ]
      }
    },
    {
      "type": "Feature",
      "properties": {
        "description": "bbb"
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          65,
          14.999999999999998
        ]
      }
    }
  ]
}

But If you have a huge number of GEO_POINTs, you'd better export each GEO_POINT as NDJSON(Newlined-Delimter JSON), instead of aggregating all the rows into one single JSON string.