Bigquery load_table_from_uri fails to insert MULTIPOLYGONS as a WKT format

Question

I am trying to INSERT multipolygon into a BigQuery table from a CSV file. My MULTIPOLYGON is properly formed. All multipolygon with a single polygon are inserted properly, however, if I have 2 or more polygons, I get an error or it will insert the wrong data.

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("wkt","GEOGRAPHY"),
    ],
    skip_leading_rows=1,
    source_format=bigquery.SourceFormat.CSV,
)

• Single polygons get properly inserted as:

POLYGON((150.811386496322 -34.0507092490729, ..., 150.811386496322 -34.0507092490729))

• Polygons with a hole in it trigger an error on insert.

• Multi polygons (>1) get inserted with dummy the wrong data:

GEOMETRYCOLLECTION(POLYGON((0 0, 120 0, -120 0, 0 0)), POLYGON((0 0, -120 0, 120 0, 0 0)))

Here is an example of a multipolygon which gets inserted with the wrong values (0 0, -120 0)

MULTIPOLYGON (((151.067511780297 -33.8736012563862,151.06750693348 -33.8736130507474,151.067498972144 -33.8736230114564,151.06748853587 -33.8736303383104,151.067476463067 -33.8736344427,151.066714390325 -33.8737774629975,151.066699539667 -33.8737777552223,151.06668540326 -33.8737731956766,151.066673522066 -33.8737642813808,151.066665191212 -33.8737519840512,151.066637690803 -33.8736919562765,151.06662450589 -33.8736632575946,151.066624466539 -33.8736631717053,151.066581815924 -33.8735698208198,151.066537401935 -33.8734724100764,151.066519548355 -33.8734378362003,151.066260439719 -33.8729359250265,151.065950193456 -33.8724076772925,151.065693863282 -33.8720056126331,151.065363561159 -33.8715351701334,151.065357665437 -33.8715234389004,151.065355407756 -33.8715105050594,151.065356980401 -33.8714974701771,151.06536224943 -33.871485444426,151.065370766085 -33.8714754520312,151.067728687185 -33.8694135663641,151.067739203239 -33.8694066994585,151.067751207773 -33.8694030071765,151.067763765198 -33.8694027772808,151.067775896835 -33.8694060276887,151.067921659061 -33.869467968343,151.068204534276 -33.8695881569186,151.068217207821 -33.869596277536,151.068226493378 -33.869608124122,151.068231351529 -33.8696223705791,151.068231238457 -33.86963742217,151.068167916904 -33.8699943319892,151.06756634424 -33.8733851275447,151.067565662982 -33.8733882983199,151.067554063592 -33.8734341670045,151.067526426187 -33.8735433450672,151.067511780297 -33.8736012563862)),((151.065984484039 -33.8708005085248,151.065984250366 -33.8708007124659,151.065313144091 -33.8713823671865,151.065300539717 -33.8713900800624,151.065286116288 -33.8713932930712,151.065271429886 -33.8713916595753,151.065258064962 -33.8713853558055,151.065247463398 -33.8713750618477,151.064938714608 -33.8709561460191,151.064537900649 -33.8704473058267,151.064420683008 -33.8703157851074,151.064414873762 -33.8703075782436,151.064213480028 -33.8699424036349,151.064209290449 -33.8699318300411,151.064103103145 -33.8695168515608,151.064102575414 -33.8695145364137,151.064068951526 -33.8693467038201,151.064068091018 -33.8693390677137,151.064062577727 -33.86913268626,151.06405490864 -33.868945245542,151.064054895537 -33.8689448911037,151.064053792968 -33.868911500279,151.064054349671 -33.8689028073705,151.064088932705 -33.8686897514989,151.064089570773 -33.8686865567586,151.064137196398 -33.8684861759544,151.064137935618 -33.8684834493359,151.064200764153 -33.8682775377562,151.064207552367 -33.8682640120636,151.064299720007 -33.8681386956262,151.06431097421 -33.868127939713,151.064325220389 -33.8681216633171,151.064487602883 -33.8680817377512,151.06449904954 -33.8680804421131,151.064510449969 -33.8680820958446,151.064767215822 -33.8681538316248,151.064911062192 -33.8681878109421,151.064918334802 -33.8681901979428,151.065199605647 -33.8683098846126,151.066280329664 -33.8687697187294,151.066523844749 -33.8688733332061,151.067572155252 -33.8693193628947,151.067584832416 -33.8693274968816,151.067594114234 -33.8693393594304,151.067598960291 -33.8693536208452,151.067598827382 -33.8693686825369,151.067593730406 -33.8693828562115,151.067584240692 -33.8693945531148,151.065984484039 -33.8708005085248)),((151.064139262095 -33.8702042610377,151.064356305788 -33.8705264634254,151.064362095341 -33.870538706732,151.06436397889 -33.8705521182836,151.064361785744 -33.8705654826986,151.064355714652 -33.8705775888668,151.064346315786 -33.8705873397031,151.064334440893 -33.870593851567,151.064321166097 -33.8705965343403,151.064307694388 -33.8705951449049,151.064295246599 -33.8705898091741,151.064284950773 -33.8705810106826,151.063623166444 -33.8698150539413,151.06327669026 -33.8694164678548,151.063275715175 -33.8694153071883,151.062845686172 -33.8688855628747,151.062838401715 -33.868872765647,151.062835639636 -33.8688583017861,151.06283769584 -33.8688437208267,151.062844350044 -33.8688305848483,151.063152289641 -33.868411133342,151.063608590276 -33.8677899466688,151.063619614711 -33.8677793344152,151.063633558441 -33.8677730312046,151.063648808294 -33.8677717662661,151.063663599988 -33.8677756859425,151.063676028272 -33.8677813848528,151.063864706391 -33.8678678492516,151.063875776069 -33.8678750549079,151.063973053178 -33.8679611270418,151.063981335342 -33.8679708866324,151.063986533731 -33.8679825836659,151.06398822753 -33.8679952712538,151.063987089104 -33.8681087910938,151.063984380725 -33.868123720098,151.063864842506 -33.8684523154361,151.063863309222 -33.8684643734845,151.063843866539 -33.8686222627058,151.063816072566 -33.8689077000221,151.063853123834 -33.8693472451656,151.063891250773 -33.8695116527481,151.063891663257 -33.8695136326396,151.063936422059 -33.8697555886164,151.063997962006 -33.8699397503223,151.064008425407 -33.8699699761639,151.064139262095 -33.8702042610377)),((151.062936399162 -33.8683083170979,151.062936369386 -33.8683083582036,151.062708576731 -33.8686224470914,151.062698722464 -33.8686323426073,151.062686308459 -33.8686387395068,151.062672530912 -33.8686410213949,151.062658717403 -33.8686389683925,151.062646198982 -33.8686327783237,151.062636181902 -33.8686230476535,151.061669416682 -33.8673355333084,151.061666167402 -33.8673305511417,151.061351038693 -33.8667694866827,151.061346190284 -33.8667564837426,151.0613455355 -33.8667426217522,151.061349136645 -33.8667292196898,151.061356651067 -33.8667175527713,151.061367363763 -33.8667087311124,151.061380255412 -33.8667035941003,151.061394099365 -33.8667026305255,151.06140757836 -33.8667059320727,151.061454657182 -33.8667255631884,151.061454835143 -33.8667256378438,151.061832376435 -33.8668849710616,151.063088835908 -33.8674149737923,151.06309252724 -33.8674167325491,151.0633090701 -33.8675322603206,151.06331526944 -33.8675362526144,151.06339615143 -33.8675982743905,151.063405701185 -33.8676082753808,151.063411762352 -33.8676207043748,151.063413762296 -33.8676343871325,151.063411512071 -33.8676480309635,151.063405224269 -33.8676603468549,151.062936399162 -33.8683083170979)))

If I use the below SQL (using the st_geogfromtext function) to insert this WKT, then it gets inserted properly:

INSERT INTO `my_table` VALUES (st_geogfromtext('MULTIPOLYGON (((151.067511780297 -33.8736012563862,151.06750693348 -33.8736130507474,151.067498972144 -33.8736230114564,151.06748853587 -33.8736303383104,151.067476463067 -33.8736344427,151.066714390325 -33.8737774629975,151.066699539667 -33.8737777552223,151.06668540326 -33.8737731956766,151.066673522066 -33.8737642813808,151.066665191212 -33.8737519840512,151.066637690803 -33.8736919562765,151.06662450589 -33.8736632575946,151.066624466539 -33.8736631717053,151.066581815924 -33.8735698208198,151.066537401935 -33.8734724100764,151.066519548355 -33.8734378362003,151.066260439719 -33.8729359250265,151.065950193456 -33.8724076772925,151.065693863282 -33.8720056126331,151.065363561159 -33.8715351701334,151.065357665437 -33.8715234389004,151.065355407756 -33.8715105050594,151.065356980401 -33.8714974701771,151.06536224943 -33.871485444426,151.065370766085 -33.8714754520312,151.067728687185 -33.8694135663641,151.067739203239 -33.8694066994585,151.067751207773 -33.8694030071765,151.067763765198 -33.8694027772808,151.067775896835 -33.8694060276887,151.067921659061 -33.869467968343,151.068204534276 -33.8695881569186,151.068217207821 -33.869596277536,151.068226493378 -33.869608124122,151.068231351529 -33.8696223705791,151.068231238457 -33.86963742217,151.068167916904 -33.8699943319892,151.06756634424 -33.8733851275447,151.067565662982 -33.8733882983199,151.067554063592 -33.8734341670045,151.067526426187 -33.8735433450672,151.067511780297 -33.8736012563862)),((151.065984484039 -33.8708005085248,151.065984250366 -33.8708007124659,151.065313144091 -33.8713823671865,151.065300539717 -33.8713900800624,151.065286116288 -33.8713932930712,151.065271429886 -33.8713916595753,151.065258064962 -33.8713853558055,151.065247463398 -33.8713750618477,151.064938714608 -33.8709561460191,151.064537900649 -33.8704473058267,151.064420683008 -33.8703157851074,151.064414873762 -33.8703075782436,151.064213480028 -33.8699424036349,151.064209290449 -33.8699318300411,151.064103103145 -33.8695168515608,151.064102575414 -33.8695145364137,151.064068951526 -33.8693467038201,151.064068091018 -33.8693390677137,151.064062577727 -33.86913268626,151.06405490864 -33.868945245542,151.064054895537 -33.8689448911037,151.064053792968 -33.868911500279,151.064054349671 -33.8689028073705,151.064088932705 -33.8686897514989,151.064089570773 -33.8686865567586,151.064137196398 -33.8684861759544,151.064137935618 -33.8684834493359,151.064200764153 -33.8682775377562,151.064207552367 -33.8682640120636,151.064299720007 -33.8681386956262,151.06431097421 -33.868127939713,151.064325220389 -33.8681216633171,151.064487602883 -33.8680817377512,151.06449904954 -33.8680804421131,151.064510449969 -33.8680820958446,151.064767215822 -33.8681538316248,151.064911062192 -33.8681878109421,151.064918334802 -33.8681901979428,151.065199605647 -33.8683098846126,151.066280329664 -33.8687697187294,151.066523844749 -33.8688733332061,151.067572155252 -33.8693193628947,151.067584832416 -33.8693274968816,151.067594114234 -33.8693393594304,151.067598960291 -33.8693536208452,151.067598827382 -33.8693686825369,151.067593730406 -33.8693828562115,151.067584240692 -33.8693945531148,151.065984484039 -33.8708005085248)),((151.064139262095 -33.8702042610377,151.064356305788 -33.8705264634254,151.064362095341 -33.870538706732,151.06436397889 -33.8705521182836,151.064361785744 -33.8705654826986,151.064355714652 -33.8705775888668,151.064346315786 -33.8705873397031,151.064334440893 -33.870593851567,151.064321166097 -33.8705965343403,151.064307694388 -33.8705951449049,151.064295246599 -33.8705898091741,151.064284950773 -33.8705810106826,151.063623166444 -33.8698150539413,151.06327669026 -33.8694164678548,151.063275715175 -33.8694153071883,151.062845686172 -33.8688855628747,151.062838401715 -33.868872765647,151.062835639636 -33.8688583017861,151.06283769584 -33.8688437208267,151.062844350044 -33.8688305848483,151.063152289641 -33.868411133342,151.063608590276 -33.8677899466688,151.063619614711 -33.8677793344152,151.063633558441 -33.8677730312046,151.063648808294 -33.8677717662661,151.063663599988 -33.8677756859425,151.063676028272 -33.8677813848528,151.063864706391 -33.8678678492516,151.063875776069 -33.8678750549079,151.063973053178 -33.8679611270418,151.063981335342 -33.8679708866324,151.063986533731 -33.8679825836659,151.06398822753 -33.8679952712538,151.063987089104 -33.8681087910938,151.063984380725 -33.868123720098,151.063864842506 -33.8684523154361,151.063863309222 -33.8684643734845,151.063843866539 -33.8686222627058,151.063816072566 -33.8689077000221,151.063853123834 -33.8693472451656,151.063891250773 -33.8695116527481,151.063891663257 -33.8695136326396,151.063936422059 -33.8697555886164,151.063997962006 -33.8699397503223,151.064008425407 -33.8699699761639,151.064139262095 -33.8702042610377)),((151.062936399162 -33.8683083170979,151.062936369386 -33.8683083582036,151.062708576731 -33.8686224470914,151.062698722464 -33.8686323426073,151.062686308459 -33.8686387395068,151.062672530912 -33.8686410213949,151.062658717403 -33.8686389683925,151.062646198982 -33.8686327783237,151.062636181902 -33.8686230476535,151.061669416682 -33.8673355333084,151.061666167402 -33.8673305511417,151.061351038693 -33.8667694866827,151.061346190284 -33.8667564837426,151.0613455355 -33.8667426217522,151.061349136645 -33.8667292196898,151.061356651067 -33.8667175527713,151.061367363763 -33.8667087311124,151.061380255412 -33.8667035941003,151.061394099365 -33.8667026305255,151.06140757836 -33.8667059320727,151.061454657182 -33.8667255631884,151.061454835143 -33.8667256378438,151.061832376435 -33.8668849710616,151.063088835908 -33.8674149737923,151.06309252724 -33.8674167325491,151.0633090701 -33.8675322603206,151.06331526944 -33.8675362526144,151.06339615143 -33.8675982743905,151.063405701185 -33.8676082753808,151.063411762352 -33.8676207043748,151.063413762296 -33.8676343871325,151.063411512071 -33.8676480309635,151.063405224269 -33.8676603468549,151.062936399162 -33.8683083170979)))'));

Question is: why is this happening? is there a big in the LoadJobConfig function? How can I efficiently insert the data?

Answer 1

The issue is probably polygon orientation.

See BigQuery doc for details of what this means: https://cloud.google.com/bigquery/docs/gis-data#polygon_orientation

The polygons should be small, but were loaded with incorrect orientation, and thus became inverted - they are now complimentary to what was the intended shape, and are occupying most of the Earth. You can check it with ST_Area call. When two such polygons are combined in geometry collection, the result is full globe (this dummy data with 0s and 120s).

ST_GEOGFROMTEXT has different defaults - it defaults to unoriented polygons (smaller of the two possible interpretations), unless you pass oriented => TRUE argument. Since you don't pass oriented parameter to ST_GEOGFROMTEXT, this function "fixes" the polygon by ignoring the orientation.

Unfortunately, there is currently no option to make load job treat the WKT strings as unoriented polygons. Your options are either load as string and use ST_GeogFromText, or switch to GeoJson format (it is OK to put GeoJson geometry strings inside CSV files).