CODEWITHSUNDEEP
sqlgoogle-bigquerygeospatial
Alex
Alex

Reputation: 1537

Invalid POLYGON bigQuery

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?

Upvotes: 6

Views: 4942

Answers (2)

Michael Entin
Michael Entin

Reputation: 7744

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 identifies the invalid polygons, and attempts to fix them:

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

Upvotes: 4

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

Use prefix SAFE:

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

null     bad

Upvotes: 10

Related Questions