Google BigQuery error: "Invalid polygon: edge crosses edge"

Question

I am trying to upload the FCC's Study Area Boundaries into Google BigQuery for analysis with other data. I used QGIS to fix the geometries and convert the shapefile to a newline delimited geojson. Shapely and QGIS say that all the polygons in the geojson are valid. However, when I attempt to make a BigQuery table from the geojson, BigQuery has the following error:

Could not convert JSON value to geography: Invalid polygon loop: Edge 52 crosses edge 54; in loop 15 Field: geometry; Value: {"type": "MultiPolygon", "coordinates": [[[[-71.084213, 45.305475], [-71.084695, 45.305694], ...

I found the coordinates for edge 52 and edge 54 in loop 15, and this is what they look like in QGIS: If I remove the coordinate that sticks out, the error changes to be about another set of points that do the same thing.

How can I find all the errors of this type, and, ideally, fix them without manually removing each extraneous coordinate?

Answer 1

BigQuery can now repair such polygons too, see make_valid argument to st_geogfromgeojson.

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

And here is a post with some guidelines:

https://mentin.medium.com/new-options-for-bigquery-gis-geospatial-data-ingestion-9b05eb1abea

Answer 2

This kind of topological error can creep into a dataset depending upon how the data was created. A self intersection can be fixed using the Repair geometry tool in ArcMap.

Here is a polygon with a self intersection

After running the dataset through the Repair tool we see that it has removed this self-intersecting spur.

Warning: always run this sort of bulk correction on a backed up dataset as sometime these can cause more problems than they fix!