How to create Polygon from Geo points in google bigquery

Question

I have a table in bigquery which contain 11 geo points with their ids. I want to create a polygon from these points. I am using below query to create a polygon. but getting error ST_MakePolygon failed: Invalid polygon loop: Edge 2 has duplicate vertex with edge 7, what is the correct way to create a polygon?

Query:

Select ST_MAKEPOLYGON(ST_MAKELINE(ARRAY_AGG(ST_GEOGFROMTEXT(wkt_geom)))) as polygons from bd.wari_lockdown_area

Table bd.wari_lockdown_area:

wari_lockdown_area

Answer 1

Below is for BigQuery Standard SQL

You can use ST_CONVEXHULL - as in below example

#standardSQL 
SELECT 
    ST_CONVEXHULL(ST_UNION_AGG(ST_GEOGFROMTEXT(wkt_geom))) AS polygons  
FROM 
  `bd.wari_lockdown_area`

If to apply to sample data in your question - result is