Invalid polygon loop: Edge 0 has duplicate vertex with edge 77 in BigQuery

Question

I am trying to use a ST_GeogFromText and it returns a

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

What does this mean and how to fix it other than using safe?

Here's the troubled polygon

id	polygons
1	POLYGON ((106.90169855907098 -6.133581031583924, 106.9017021065643 -6.133580815556306, 106.90170264419842 -6.1336761739345365, 106.90167406544357 -6.133683743590734, 106.90162801563197 -6.133695942984981, 106.90162546518543 -6.133696860699337, 106.90162311136393 -6.133698203564284, 106.90162102358165 -6.1336999315513125, 106.9016192630876 -6.133701994715365, 106.90161788385632 -6.133704329575195, 106.90161692719347 -6.133706866365521, 106.90161641993565 -6.133709530851762, 106.90161637896385 -6.133712243409904, 106.90161680398326 -6.133714920860319, 106.90162435219705 -6.1337452577094025, 106.90162478364874 -6.133747965877619, 106.90162474639607 -6.133750708259081, 106.9016242410507 -6.133753402575097, 106.9016232817933 -6.133755971923593, 106.90162189815752 -6.133758338443912, 106.90162013055223 -6.133760434182235, 106.90161803114526 -6.133762195663685, 106.90161620026157 -6.133763259101627, 106.90161566208575 -6.133763572035901, 106.90161309188133 -6.133764522369391, 106.90161039721434 -6.133765018363632, 106.90155319475645 -6.133770538708446, 106.90150712196733 -6.133774984292834, 106.9015000341944 -6.133775668575066, 106.90149473890627 -6.133776179227366, 106.90146064281967 -6.13377946929004, 106.90145105459433 -6.133780394812981, 106.90140567531827 -6.133784773733044, 106.90136709485307 -6.133788497423158, 106.90135815860155 -6.133789359148415, 106.90132632657804 -6.133793763199713, 106.90123576384975 -6.133806292383803, 106.90113927459215 -6.133819641850603, 106.90113455973909 -6.1338202941969655, 106.90095056240669 -6.133849896937195, 106.90093433076949 -6.133852507838495, 106.90065112027884 -6.133576391116389, 106.9006578420394 -6.133573874552873, 106.90088631510756 -6.133488341427569, 106.90094771482305 -6.1334747784229835, 106.90099342521992 -6.133464680605348, 106.90101288928126 -6.133460381056623, 106.90118294377297 -6.1333813534116, 106.90119184943609 -6.133377214300388, 106.90120462037157 -6.1333707658622485, 106.90123213338536 -6.133356874669582, 106.90124899718838 -6.133348360212979, 106.90129459942436 -6.133325335397309, 106.90133961915346 -6.133302605587468, 106.9013534928787 -6.133295600803761, 106.90140801302735 -6.133268074265409, 106.90141624294067 -6.133263918563662, 106.90145062968064 -6.133246556387555, 106.90145189838928 -6.133245916274603, 106.90151259989008 -6.133223464407351, 106.90152613291578 -6.133218458974171, 106.90153869162906 -6.133213814134244, 106.90162495078259 -6.133181908268772, 106.90168236143796 -6.133164039390095, 106.90172903438165 -6.133149513255213, 106.90173308333969 -6.133163859294707, 106.90174353962243 -6.133200901750687, 106.90177414555183 -6.133309332068909, 106.90178325982984 -6.133341624428483, 106.90182566677858 -6.133444183859271, 106.9018398493703 -6.133508516219575, 106.90184039744875 -6.133510997034733, 106.90184849591249 -6.13354773864174, 106.9018340617928 -6.133551589988596, 106.90181498084641 -6.133556680230166, 106.90178876857213 -6.133563672784084, 106.90176772114135 -6.133569288078043, 106.90173113496131 -6.133579048866452, 106.9017021065643 -6.133580815556306, 106.90170209721472 -6.133579197183354, 106.90169855907098 -6.133581031583924))
2	POLYGON ((106.58427583500003 -6.216766528999979, 106.58433255400001 -6.216745424000006, 106.58424155699998 -6.216896430000017, 106.58413189499997 -6.21700203199998, 106.58387842799998 -6.216988436999993, 106.58355334999999 -6.217013337000028, 106.58334746899997 -6.216923034999978, 106.58322764299997 -6.216827690000001, 106.582949981 -6.216718462000016, 106.58291187100001 -6.216766428000031, 106.58292154899999 -6.216804680999984, 106.58269672799997 -6.216776642999998, 106.582199198 -6.216696784000027, 106.58194087700001 -6.216659275999977, 106.58180192399999 -6.2165639880000185, 106.581753965 -6.216516280000014, 106.58151006000003 -6.216502654000016, 106.58131365700001 -6.216383612000017, 106.581207833 -6.21617337999998, 106.58114557200001 -6.216140069999989, 106.58105953199997 -6.216149898000029, 106.58057197999999 -6.216208778000012, 106.58035196899998 -6.216190294999986, 106.58022189 -6.216199662999994, 106.580221834 -6.216195249000013, 106.580218559 -6.215939240999997, 106.58020894700002 -6.215774269999997, 106.58022653199998 -6.215579396999995, 106.58026102699999 -6.215450868000007, 106.58029934799997 -6.215272460999988, 106.580341514 -6.215155439999988, 106.58050831000001 -6.215017298000017, 106.58055351299997 -6.214942894999974, 106.58056385999997 -6.214853044999988, 106.580572496 -6.214728931000021, 106.58289524999996 -6.214690985999984, 106.582864867 -6.213657875000023, 106.58286489900001 -6.213657882000006, 106.58291097499999 -6.213668039000034, 106.583338632 -6.213768443999976, 106.58372737699999 -6.213848030000009, 106.58400849999998 -6.2139159750000195, 106.58423281500001 -6.2139751180000085, 106.58439137399999 -6.214031466999988, 106.584535055 -6.21410879500001, 106.58463980399999 -6.214159323999975, 106.58476852299997 -6.2142307159999755, 106.584915039 -6.214257192999978, 106.585101844 -6.2142646089999936, 106.58512213099999 -6.214264231000009, 106.58512796600002 -6.215118643999968, 106.58513549399997 -6.2162208330000235, 106.58513377799999 -6.216645523000009, 106.58512224499997 -6.2167861230000145, 106.58510441200002 -6.216819073999998, 106.58465016899999 -6.2168234270000005, 106.58440210600003 -6.216818188999976, 106.584351265 -6.216806378999983, 106.58433255400001 -6.216745424000006, 106.58437426799996 -6.216703428000011, 106.58437415100002 -6.216664548999976, 106.584335189 -6.216500833000005, 106.58437627699998 -6.216376234999985, 106.58440472299998 -6.21629480199999, 106.58446201599996 -6.216265919000031, 106.58448077000003 -6.216141447999972, 106.584518393 -6.215930786999995, 106.58455239699997 -6.215816999000028, 106.58457993799999 -6.215724840000006, 106.58461724499999 -6.215408905999991, 106.58464980000001 -6.215102557000023, 106.584656654 -6.215007276000004, 106.584663572 -6.214911108000018, 106.584682154 -6.214729216000015, 106.58470546000001 -6.214528168000008, 106.584557103 -6.214485545999994, 106.58441829399997 -6.214438109999993, 106.58418386699996 -6.214395745999984, 106.584121664 -6.214381576999985, 106.58403080000001 -6.2143770640000175, 106.58396885399996 -6.214449026999996, 106.583926378 -6.214635776000026, 106.58388491499998 -6.214759026000026, 106.58366896500002 -6.214701746000004, 106.58361298900002 -6.214971079999993, 106.58358387300001 -6.215228371000023, 106.58357828099996 -6.215356989999978, 106.58362915700002 -6.215380762999982, 106.583631991 -6.215571119000002, 106.583575332 -6.21556993899998, 106.58326926 -6.215554107999992, 106.58312577599997 -6.215542573999979, 106.583106714 -6.215564164999983, 106.58309741599999 -6.215652718999985, 106.58308795300002 -6.215686242999997, 106.58309053699998 -6.215750835999984, 106.58309785199998 -6.215798666000003, 106.583181573 -6.215812770000016, 106.583289152 -6.2158100560000165, 106.58337766099999 -6.215826538999968, 106.583647857 -6.215837691999979, 106.58370229399998 -6.215853080999969, 106.583774447 -6.215996420000005, 106.58384056999999 -6.216121832999992, 106.58390672899998 -6.216259208999989, 106.584017706 -6.2163934600000115, 106.58414678399998 -6.216584480999983, 106.58421892899997 -6.216724828999991, 106.58427583500003 -6.216766528999979))
3	POLYGON ((106.82189393929842 -6.3347604517697045, 106.82191259985103 -6.334743663589071, 106.82189393929842 -6.3347604517697045, 106.82189393929842 -6.3347604517697045))

Answer 1

This means there are duplicate vertices, which is not allowed by OGC model, although often happens in "dirty" data. Typical cases are 8-shaped figure, and spikes, like below:

*--*       *-------*
|  |       |       |
*--*--*    |       |----*
   |  |    *-------*
   *--*

First figure should be described as multi-polygon with two polygons, but some tools describe it as single polygon with a self-intersection. In the second case the "spike" should simply be removed.

BigQuery Geography parsers like ST_GeogFromText have make_valid option that attempts (and usually succeeds) to fix such shapes:

ST_GeogFromText(<wkt_text>, make_valid => TRUE)

See the description for second signature of ST_GeogFromText here:

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

Often with such data, you might also need to add planar => TRUE if the data comes from planar map tools, or oriented => FALSE if the data comes from spherical world tools that don't care about polygon orientation.