Here is some extra input which might be helpful in case geometry support will be added.

The solution by @brews worked for a few rows in my case, I needed to manually specify the schema to get it to parse the geometry column as GEOGRAPHY. This is what I used:

import geopandas as gpd
from google.cloud import bigquery

client = bigquery.Client()

table_id = "dataset.tablename"

df = gpd.read_file("my_file")

#determine schema
type_dict = {
    'b' : 'BOOLEAN',
    'i' : 'INTEGER',
    'f' : 'FLOAT',
    'O' : 'STRING',
    'S' : 'STRING',
    'U' : 'STRING'
}
schema = [{'name' : col_name, 'type' : "GEOGRAPHY" if col_name == "geometry" else type_dict.get(col_type.kind, 'STRING')} for (col_name, col_type) in df.dtypes.iteritems()]

#https://cloud.google.com/bigquery/docs/pandas-gbq-migration#loading_a_pandas_dataframe_to_a_table
job_config = bigquery.LoadJobConfig(schema=schema)
job = client.load_table_from_dataframe(
    df.to_wkt(), #same output as github issue solution
    table_id, 
    job_config=job_config
)
job.result()

This worked for a small dataset but for a larger set I quickly started running into all kinds of errors where bq would not accept the polygons:

Invalid polygon loop: Edge 462 has duplicate vertex with edge 554
Invalid nesting: loop 1 should not contain loop 0

I found a potential solution using geojson instead of wkt: https://stackoverflow.com/questions/62233152/uploading-to-bigquery-gis-invalid-nesting-loop-1-should-not-contain-loop-0

After quite some experimentation I found a way to create a df that seemed acceptable to bq similar to the solution above:

df_json = pd.DataFrame({
    col: (df[col] if col != 'geometry' else df[col].map(lambda x: json.dumps(shapely.geometry.mapping(x))))
    for col in df
})

This led to another similar error as before:

Invalid geography value for column 'geometry', error: Polygon's first loop must be shell. It is nested in loop 2

I then found that bq has an option to fix this type of data using make_valid=>true: https://gis.stackexchange.com/a/376870
Now I have a fully working solution, it is relatively simple but it requires overwriting the table after creation:

import geopandas as gpd
from google.cloud import bigquery

client = bigquery.Client()

table_id = "dataset.tablename"
df = gpd.read_file("my_file")

df.to_wkt().to_gbq(table_id,if_exists="replace")

cols = ",".join("st_geogfromtext(geometry, make_valid => TRUE) as geometry" if col == "geometry" else col for col in df)

query = f"CREATE OR REPLACE TABLE {table_id} AS SELECT {cols} FROM {table_id}"
# print(query)
query_job = client.query(query)

I thought I'd share this solution in case others have similar issues.

It would be nice if make_valid=True could somehow be set in df.to_gbq or in bigquery.LoadJobConfig, but I have no idea whether the architecture allows this.

EDIT: I learned that shapely also has make_valid, this still didn't work with to_wkt because of the issue mentioned before but it does work with the geojson solution. This solution worked for all the data I tested as well:

import geopandas as gpd
import json
import shapely
from shapely.validation import make_valid
from google.cloud import bigquery

client = bigquery.Client()

table_id = "dataset.tablename"

df = gpd.read_file("my_file")

#determine schema
type_dict = {
    'b' : 'BOOLEAN',
    'i' : 'INTEGER',
    'f' : 'FLOAT',
    'O' : 'STRING',
    'S' : 'STRING',
    'U' : 'STRING'
}
schema = [{'name' : col_name, 'type' : "GEOGRAPHY" if col_name == "geometry" else type_dict.get(col_type.kind, 'STRING')} for (col_name, col_type) in df.dtypes.iteritems()]

df_json = pd.DataFrame({
    col: (df[col] if col != 'geometry' else df[col].map(lambda x: ujson.dumps(shapely.geometry.mapping(make_valid(x)))))
    for col in df
})

job_config = bigquery.LoadJobConfig(schema=schema)
job = client.load_table_from_dataframe(
    df_json,
    table_id, 
    job_config=job_config
)
job.result()

Geometry column #14489

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions