How to convert a string type with a POLYGON to a Geography type- BigQuery

Question

I'm trying to convert a STRING data type to a GEOGRAPHY repeated type in order to join two tables in Bigquery. I'm not very familiar with Bigquery so any help will be greatly appreciated.

My 1st table is known as DealerInfo with the following data types:

1. DealerId - STRING
2. Geographies - GEOGRAPHY

My 2nd table is known as Cities:

1. City_name- STRING
2. Geometry - STRING

The Geometry string looks something like this:

POLYGON ((-28.855180740356388 -20.470634460449162,-28.855382919311467
          -20.470634460449162,-28.855499267578125 -20.470554351806527,
          -28.855585098266545 -20.470428466796818,-28.855623245239258
          -20.470342636108398,-28.855686187744141 -20.470207214355469,
          -28.855756759643498 -20.470096588134766,-28.855855941772404
          -20.470016479492188,-28.856058120727482 -20.469953536987305,
          -28.856224060058594 -20.469875335693303,-28.856462478637695
          -20.469699859619141,-28.856561660766602 -20.46949577331543,
          -28.856527328491154 -20.469383239746037,-28.856527328491154
          -20.469097137451115,-28.856479644775334 -20.468908309936523,
          -28.856412887573185 -20.468816757202148,-28.856327056884766
          -20.468719482421818,-28.856176376342773 -20.468656539916935,
          -28.855871200561467 -20.46859169006342,-28.855756759643498
          -20.46859169006342,-28.855636596679574 -20.468561172485295,
          -28.855499267578125 -20.468547821044808,-28.855382919311467
          -20.468450546264648,-28.855401992797852 -20.468360900878793,
          -28.855417251586914 -20.468278884887638,-28.8554668426513…

Whereas the Geographies variable looks exactly the same but it's a GEOGRAPHY data type so I'm unable to join the tables.

Any advice/code will be greatly appreciated!

Here is the code I used to try and convert the STRING to GEOGRAPHY in order to join the two tables:

SELECT 
    ST_MAKEPOLYGON(geometry) AS NEW_GEO
FROM `DWH.CASE1.BrazilCitiesGeographies` 
LIMIT 1000 

Answer 1

To convert a string type with a POLYGON to a BigQuery Geography type you should use ST_GEOGFROMTEXT() function as in below example

SELECT 
    ST_GEOGFROMTEXT(geometry) AS NEW_GEO
FROM `DWH.CASE1.BrazilCitiesGeographies` 
LIMIT 1000