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.
POINT(longitude latitude)
while the valuesPOINT (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: