CODEWITHSUNDEEP
google-bigquerygeojson
sopana
sopana

Reputation: 375

How to separate records with NULL values from a geoJSON file?

I've a geoJSON file as below -

{"EFTA_FLAG": "F", "OTHR_FLAG": "F", "EU_FLAG": "T", "COAS_FLAG": "F", "NUTS_BN_ID": 108, "CC_FLAG": "F", "LEVL_CODE": 3, "FID": 108, "geometry": "{\"type\": \"LineString\", \"coordinates\": [[24.28752, 35.17601], [24.28513, 35.19078], [24.29281, 35.20274], [24.2949, 35.21629], [24.30594, 35.23965], [24.28578, 35.23943], [24.27087, 35.24888], [24.27403, 35.25674], [24.28072, 35.26354], [24.29232, 35.27115], [24.30861, 35.27614], [24.31421, 35.28196], [24.32534, 35.28467], [24.33098, 35.29074], [24.3305, 35.31241], [24.32346, 35.32514], [24.31741, 35.35377]]}"}
{"EFTA_FLAG": "F", "OTHR_FLAG": "F", "EU_FLAG": "T", "COAS_FLAG": "F", "NUTS_BN_ID": 298, "CC_FLAG": "F", "LEVL_CODE": 3, "FID": 298, "geometry": "{\"type\": \"LineString\", \"coordinates\": [[-6.34556, 36.79877], [-6.34397, 36.79998], [-6.34244, 36.80314], [-6.34244, 36.8053], [-6.3436, 36.81252], [-6.34495, 36.81596], [-6.35188, 36.8253], [-6.35296, 36.82927], [-6.35331, 36.83487], [-6.35398, 36.83836], [-6.35362, 36.85142], [-6.35296, 36.85741], [-6.34889, 36.87373], [-6.34594, 36.88804], [-6.34333, 36.89191], [-6.33989, 36.89527], [-6.33823, 36.89626], [-6.33107, 36.89884]]}"}
{"EFTA_FLAG": "F", "OTHR_FLAG": "F", "EU_FLAG": "T", "COAS_FLAG": "F", "NUTS_BN_ID": 355, "CC_FLAG": "F", "LEVL_CODE": 3, "FID": 355, "geometry": "null"}

While loading it into BigQuery using below syntax -

bq load --source_format NEWLINE_DELIMITED_JSON dataset.table_name data.json geometry:GEOGRAPHY,EU_FLAG,CC_FLAG,OTHR_CNTR_FLAG,LEVL_CODE:int64,FID:int64,EFTA_FLAG,COAS_FLAG,NUTS_BN_ID:int64

it throws error -

Failure details: - Error while reading data, error message: JSON processing encountered too many errors, giving up. Rows: 467; errors: 1; max bad: 0; error percent: 0 - query: Could not convert JSON value to geography: Unexpected 'null' at position 0. Field: geometry; Value: null

I need to load the records with NULL in geometry too. How can I separate the NULL and not NULL records? And then how can load the NULL records too in BQ table?

Upvotes: 1

Views: 472

Answers (1)

Michael Entin
Michael Entin

Reputation: 7744

In JSON the NULL values should be encoded without quotes, just "geometry": null.

For this JSON file, I would load it to a temporary table, using STRING type for geometry field. Then convert it to GEOMETRY type using a query like

CREATE TABLE final_table AS
SELECT 
  * EXCEPT(geometry), 
  IF(geometry <> 'null', ST_GeogFromGeoJson(geometry), NULL) AS geometry
FROM temp_table

Upvotes: 0

Related Questions