As I continued my experimentation with BigQuery, I found myself wanting to more easily use it with my regular GIS tool set. BigQuery has a lot of powerful analytic capability, but the SQL console is intimidating for the casual user and the GeoViewer tool is fairly limited. As I began digging deeper in my previous posts, I cam to the conclusion that a Koop provider was what I needed.
If you’re not familiar with Koop, it’s an open-source project sponsored by Esri that makes it possible to make other data sources compatible with the GeoServices REST Specification and, therefore, consumable by Esri clients. Because it fully implements that spec, data can also be output as GeoJSON, making it a convenient one-stop to address Esri and non-Esri tools. Koop is built with a plug-in architecture that makes it fairly simple to wire up any data source. For me, it was the perfect way to get my BigQuery GIS data into either ArcGIS Pro (see the header image) or QGIS (image below). I could have also chosen one of the many WFS implementations available, but Koop’s simplicity tipped the scales for me.
I’m in the process of tidying up the readme, but using the Koop provider requires the same preliminary steps described here. It’s build to use a service account to authenticate to BigQuery. The service account is typically bound to a single Google Cloud project, but the environment variable still needs to be set, even if it’s set to an empty string.
process.env.GOOGLE_CLOUD_PROJECT = config.gcloud.projectYou’ll also need to set the appropriate Google Cloud region in the config file, since queries needs to be executed in the same region as the data. Once the configuration steps have been completed, you can serve data by running Koop and accessing via URL similar to this:
https://localhost:8080/bigquery/rest/services/bq_dataset.bq_table/FeatureServer/0/queryThe URL above will produce GeoServices output of the BigQuery table, using the first spatial column it encounters. To return GeoJSON, simple append the f=geojson parameter like so:
http://localhost:8080/bigquery/rest/services/sni_system.geo_sample/FeatureServer/0/query?f=geojsonCode for the BigQuery provider can be found here: https://github.com/geobabbler/koop-bigquery-provider
