DataGrip

JetBrains DataGrip can connect to the Splitgraph engine and use it as a data source to query checked out Splitgraph tables directly or through layered querying.

Add PostgreSQL data source

Add PostgreSQL data source

Configure PostgreSQL database

Use a JDBC connection string to add the Splitgraph engine to DataGrip:

jdbc:postgresql://localhost:5432/splitgraph?user=sgr&password=[password]

Configure PostgreSQL database

Missing schemas

By default, DataGrip only shows the "public" schema in the selector. Splitgraph checks data images out into schemas with the same name as the repository.

Splitgraph schemas missing

To solve this, right click on the database, then go to Tools, Manage Shown Schemas and check "All Schemas".

Go to Database -> Database Tools -> Manage Shown Schemas

Check All Schemas

Your checked-out Splitgraph datasets will appear in the schema selector.

The checked-out datasets in the schema selector

Making geospatial queries

DataGrip has support for plotting geospatial data using PostGIS.

Make sure you have upgraded your engine to be PostGIS-enabled:

$ sgr engine upgrade --image splitgraph/engine:stable-postgis
$ sgr sql "CREATE EXTENSION IF NOT EXISTS postgis"

Clone a dataset and check it out:

$ sgr clone splitgraph/london_wards
$ sgr checkout --layered splitgraph/london_wards:latest

A good sample query is just plotting the geometry column in this dataset. This column is in the British National Grid projection and we need to convert it to the standard WGS 84 projection in order for DataGrip to plot it with an OpenStreetMap overlay:

SELECT name, gss_code, ST_Transform(ST_SetSRID(geom, 27700), 4326) FROM "splitgraph/london_wards".city_merged_2018

Toggle geo viewer by pressing gear in bottom right hand side of window.

Toggle geo viewer by pressing gear in bottom right hand side of window

Use mouse/keyboard to select one or multiple rows containing polygons to plot (select all with Cmd+A / Ctrl+A).

Select one row

Select multiple rows