One Minute Demo

This demo will show you how to query Splitgraph's PostgreSQL endpoint with clients like psql, pgcli, pgAdmin or DBeaver.

Connecting to Splitgraph

Go to splitgraph.com/connect to get your Splitgraph credentials. You can signup via OAuth using your GitHub/GitLab/Google account, or via email/password. Then you'll need to click a button to get your credential, with a SQL username and password.

If you're using psql or pgcli, you can paste the connection string directly:

$ psql postgresql://[API_KEY]:[API_SECRET]@data.splitgraph.com:5432/ddn

You're now ready to run queries against Splitgraph data.

Querying open government data

This sample query runs a JOIN between two datasets:

SELECT
    cambridge_cases.date AS date,
    chicago_cases.cases_total AS chicago_daily_cases,
    cambridge_cases.new_positive_cases AS cambridge_daily_cases
FROM
    "cityofchicago/covid19-daily-cases-deaths-and-hospitalizations-naz8-j4nc".covid19_daily_cases_deaths_and_hospitalizations chicago_cases
FULL OUTER JOIN
    "cambridgema-gov/covid19-case-count-by-date-axxk-jvk8".covid19_case_count_by_date cambridge_cases
ON
    date_trunc('day', chicago_cases.lab_report_date) = cambridge_cases.date::timestamp
ORDER BY date ASC;

This will join the data between two distinct Socrata open government data portals (Chicago, IL and Cambridge, MA).

Querying geospatial data

If your client supports PostGIS (like DBeaver or pgAdmin), it can plot geospatial data from Splitgraph repositories.

For example, you can query the London ward boundary data image as follows:

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