Invalid POLYGON bigQuery

Question

I have a column with POLYGON strings, and before processing the geo data I need to convert them with ST_GEOGFROMTEXT. However, I probably have some rows with invalid polygons and I get the following error

Error: ST_GeogFromText failed: Invalid polygon loop: Edge 0 has duplicate vertex with edge 4025

This is my query

SELECT st_geogfromtext(string_field_1)
FROM t

Is there a way to deal with incorrect polygons, or at least to identify which row returns the problem?

Answer 1

Use prefix SAFE:

SELECT SAFE.st_geogfromtext(x), x
FROM (SELECT "bad" x)

null     bad

Answer 2

There is now a way to fix this problem in most cases, by using make_valid parameter:

https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_geogfromtext_signature2

Here is a query that find the invalid polygons, and attempt to fix it:

SELECT 
  st_geogfromtext(string_field_1, make_valid => TRUE) as geog,
  SAFE.st_geogfromtext(string_field_1) IS NULL as geog_needed_fix
FROM t