Import PostGIS into BigQuery GIS

google-big-querypostgispostgresqlshapelywell-known-binary

I have to import PostGIS data into BigQuery GIS.
In order to do this, I dumped the PostGIS data in CSV and import it into BigQuery.
This is working for all the columns but not the geometric one.

If I understood well, this is because BigQuery GIS is not supporting EWKB. In the CSV dump, a data point is encoded like:

01010000206A080000C88758C0E72B1441EE517105E8185A41

As an example, of what I think is the EWKB problem, when I'm using the python package shapely for converting a Hex WKB geometry:

from shapely import wkb
print(wkb.loads("01010000206A080000C88758C0E72B1441EE517105E8185A41", hex=True))

this will give the right location

POINT (330489.9378377167 6841248.085041506)

But with BigQuery GIS using:

SELECT ST_GEOGFROM("01010000206A080000C88758C0E72B1441EE517105E8185A41") AS WKT_format

I have the following error:

ST_GeogFrom failed: Unknown object type 0x20000001; during WKB parsing

What could be a reliable pipeline for importing PostGIS data into BigQuery GIS?

Best Answer

There are two problems that would prevent loading this data directly to BigQuery.

  1. this is EWKB, extended WKB with SRID (this is what 0x20000001 is). BigQuery does not understand this extension.
  2. BigQuery only supports WGS 84 (also known SRID EPSG:4326) - or to put it simply, POINT(longitude latitude) while the values POINT (330489.9378377167 6841248.085041506) are clearly not longitude and latitude.

What you can do to get this into format BigQuery understands is project it to SRID 4326, and export to some text format, GeoJson being safer choice:

ST_AsGeoJson( ST_Transform( geog_column, 4326 ))
Related Question