search

gabfl / bigquery_fdw

BigQuery Foreign Data Wrapper for PostgreSQL
MIT License
90 stars 15 forks source link

GEOMETRY type #30

Open bkanuka opened 2 years ago

bkanuka commented 2 years ago

Both BigQuery and Postgres (with postgis extension) support a GEOMETRY data type. Will bigquery_fdw work with this?

gabfl commented 2 years ago

Hi @bkanuka , if they both have the data type and they work the same way, it's easy to add and I am happy to do it but would you be able to test the pull request on your hand?

bkanuka commented 2 years ago

Absolutely I would test. They are the same type and are 90% the same. There's some implementation details of geometric functions that differ, but reading/writing wise should be identical

gabfl commented 2 years ago

@bkanuka I can see the data type on PostGIS here: https://postgis.net/docs/reference.html#PostGIS_Types

However on BQ I checked https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types and https://cloud.google.com/bigquery/docs/geospatial-data and I do not see GEOMETRY, only GEOGRAPHY, are they interchangeable in this instance? Or am-I missing something?

bkanuka commented 2 years ago

So sorry on my phone opening tickets late at night... You're right. It's GEOGRAPHY for both.

gabfl commented 2 years ago

@bkanuka

Ok Sounds good! I created this PR: https://github.com/gabfl/bigquery_fdw/issues/30

I mapped both PostGIS types geometry and geography to BigQuery's GEOGRAPHY data type.

Can you try it and let me know?

The steps should be:

  1. Clone the repo and checkout the branch PostGIS-datatypes-support
  2. Follow the steps here: https://github.com/gabfl/bigquery_fdw/blob/main/README.md#installation-on-debianubuntu
  3. Instead of pip3 install bigquery-fdw, run pip3 install .

Give me some feedback on the results and I will push the new version to Pypi

gabfl commented 2 years ago

@bkanuka did you get a chance to review this?