Query the Data Delivery Network
Query the DDNThe easiest way to query any data on Splitgraph is via the "Data Delivery Network" (DDN). The DDN is a single endpoint that speaks the PostgreSQL wire protocol. Any Splitgraph user can connect to it at data.splitgraph.com:5432 and query any version of over 40,000 datasets that are hosted or proxied by Splitgraph.
For example, you can query the violence_reduction_victims_of_homicides_and table in this repository, by referencing it like:
"cityofchicago/violence-reduction-victims-of-homicides-and-gumc-mgzr:latest"."violence_reduction_victims_of_homicides_and"or in a full query, like:
SELECT
":id", -- Socrata column ID
"updated", -- Records the most recent date the source table was updated.
"sex", -- Sex of the victims. Victimization demographic data shown here are captured by CPD and limited to information included in the police report, which may not often be supported by individual self-identification including for sex/gender. In particular, CPD has historically recorded a victim’s sex rather than gender although has added a field for collecting gender as of January 2021.
"victimization_iucr_cd", -- Based on the Illinois Uniform Crime Reporting code. This is directly linked to the Primary Type and Description. See the list of IUCR codes at https://data.cityofchicago.org/d/c7ck-438e.
"incident_iucr_secondary", -- Text description of the IUCR code that describes the sub category of the primary category. The column represents the incident as whole by conveying the most serious victimization according to IUCR hierarchy guidelines.
":@computed_region_d3ds_rm58",
"victimization_iucr_secondary", -- IUCR Secondary adds more description to the Primary category.
"race", -- Crime classification as outlined in the FBI's Uniform Crime Reporting (UCR). See the Chicago Police Department listing of these classifications at http://gis.chicagopolice.org/clearmap_crime_sums/crime_type.
"month", -- Month when the victimization occurred.
"block", -- The partially redacted address where the victimization occurred, placing it on the same block as the actual address.
"gunshot_injury_i", -- Indicator field describing whether or not a victim was injured by gunfire. Non-fatal shooting data is not available before 2010 so all non-homicide victimizations will be recorded as “UNKNOWN.”
"victimization_fbi_cd", -- Crime classification as outlined in the FBI's Uniform Crime Reporting (UCR). See the Chicago Police Department listing of these classifications at http://gis.chicagopolice.org/clearmap_crime_sums/crime_type.
"street_outreach_organization", -- Street outreach organization refers to organizations that actively work in “the streets” to engage individuals who are at immediate or high risk of being either victims or perpetrators of violence. Each organization has specific geographic boundaries where they focus their activities. This indicates which organization(s) is (are) currently active in the location where the victimization occurred, using the boundaries in place as of 11/1/2021, and may contain more than one organization as several outreach organizations’ boundaries overlap.
"location", -- A slightly altered location of the victimization in a form that allows for mapping and other geographic analysis on this data portal.
"district", -- The current CPD district where the victimization occurred, using the boundaries in place as of 11/1/2021. Please refer to the City of Chicago’s Data Portal for a map of districts.
"beat", -- The CPD beat where the victimization occurred, using the boundaries in place as of 11/1/2021. A beat is the smallest CPD geographic area. Each beat has a dedicated police beat car. Three to five beats make up a police sector, and three sectors make up a police district. The Chicago Police Department has 22 police districts. Please refer to the City of Chicago’s Data Portal for a map of beats.
"latitude", -- The latitude of the victimization location. In order to preserve anonymity, the given coordinates are not the actual location of the crime. To produce slightly altered coordinates, a circle roughly the size of an average city block was drawn around the original point location, and a new location was picked randomly from a spot around the circumference of that circle.
"incident_fbi_descr", -- FBI Description connects a text description of the category to FBI Code, but only pertains to the most serious victimization in the incident.
":@computed_region_vrxf_vc4k",
"incident_iucr_cd", -- The IUCR code that represents the most serious victimization experienced in this incident according to IUCR hierarchy guidelines. When we were not able to establish whether a homicide victimization was in the first or second degree, we set this field to “01XX.” When we were not able to establish whether a non-fatal shooting victimization was an aggravated battery, robbery, or criminal sexual assault, we set this field equal to “UNK.”
"location_description", -- Describes the location where a crime occurred, such as alley, sidewalk, etc.
"incident_primary", -- Text description of the IUCR Code that describes the major crime category it falls into. INCIDENT_PRIMARY represents the incident as a whole by conveying the most serious victimization according to IUCR hierarchy guidelines. In rare instances when we were not able to establish whether a non-fatal shooting victimization was an aggravated battery, robbery, or criminal sexual assault, we set this field equal to “NON-FATAL.”
":@computed_region_rpca_8um6",
":@computed_region_43wa_7qmu",
"community_area", -- The community area where the victimization occurred. Chicago has 77 community areas. See the full list of Chicago’s community areas at https://data.cityofchicago.org/d/cauq-8yn6.
"zip_code", -- ZIP code where the victimization occurred, using the boundaries in place as of 11/1/2021.
"state_senate_district", -- Illinois State Senate Legislative Districts (SLDs) where the victimization occurred, using the boundaries in place as of 11/1/2021, represented by members elected to the Illinois State Senate.
"incident_fbi_cd", -- Crime classification as outlined in the FBI's Uniform Crime Reporting (UCR), but only pertains to the most serious victimization in the incident. See the Chicago Police Department listing of these classifications at http://gis.chicagopolice.org/clearmap_crime_sums/crime_type.
"area", -- The CPD area where the victimization occurred, using the boundaries in place as of 11/1/2021. There are 5 CPD areas. Each area includes 3-4 police districts.
"homicide_victim_mi", -- (For homicide victims only) Victim's middle initial.
"hour", -- Hour of the day when the victimization occurred.
"longitude", -- The longitude of the victimization location. This has been slightly altered to preserve anonymity. (see details under LATITUDE).
"victimization_fbi_descr", -- FBI Description connects a text description of the category to FBI Code.
":@computed_region_d9mm_jgwp",
"case_number", -- The Chicago Police Department RD Number (Records Division Number), which is a unique ID assigned to each incident. Due to an incident sometimes involving multiple victimizations, this number is repeated in this dataset for some incidents.
"date", -- Date when the victimization occurred. This is sometimes a best estimate.
"day_of_week", -- Day of the week when the victimization occurred. Sunday=1
"ward", -- The City Council district in which the victimization occurred, using the boundaries in place as of 11/1/2021. Please refer to the city of Chicago’s Data Portal for a map of the 50 wards.
"state_house_district", -- Illinois House of Representatives Legislative District where the victimization occurred, using the boundaries in place as of 11/1/2021, represented by members elected to the Illinois House of Representatives.
"homicide_victim_last_name", -- (For homicide victims only) Victim's last name.
"homicide_victim_first_name", -- (For homicide victims only) Victim's first name.
"victimization_primary", -- Text description of the IUCR Code that describes the major crime category it falls into. VICTIMIZATION_PRIMARY represents only the crime that this specific victim within the incident experienced. In rare instances when we were not able to establish whether a non-fatal shooting victimization was an aggravated battery, robbery, or criminal sexual assault, we set this field equal to “NON-FATAL.”
"age", -- Age of the victims grouped by decade.
"unique_id" -- ID unique to each victimization.
FROM
"cityofchicago/violence-reduction-victims-of-homicides-and-gumc-mgzr:latest"."violence_reduction_victims_of_homicides_and"
LIMIT 100;Connecting to the DDN is easy. All you need is an existing SQL client that can connect to Postgres. As long as you have a SQL client ready, you'll be able to query cityofchicago/violence-reduction-victims-of-homicides-and-gumc-mgzr with SQL in under 60 seconds.
Query Your Local Engine
bash -c "$(curl -sL https://github.com/splitgraph/splitgraph/releases/latest/download/install.sh)"Read the installation docs.
Splitgraph Cloud is built around Splitgraph Core (GitHub), which includes a local Splitgraph Engine packaged as a Docker image. Splitgraph Cloud is basically a scaled-up version of that local Engine. When you query the Data Delivery Network or the REST API, we mount the relevant datasets in an Engine on our servers and execute your query on it.
It's possible to run this engine locally. You'll need a Mac, Windows or Linux system to install sgr, and a Docker installation to run the engine. You don't need to know how to actually use Docker; sgrcan manage the image, container and volume for you.
There are a few ways to ingest data into the local engine.
For external repositories, the Splitgraph Engine can "mount" upstream data sources by using sgr mount. This feature is built around Postgres Foreign Data Wrappers (FDW). You can write custom "mount handlers" for any upstream data source. For an example, we blogged about making a custom mount handler for HackerNews stories.
For hosted datasets (like this repository), where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.
Cloning Data
Because cityofchicago/violence-reduction-victims-of-homicides-and-gumc-mgzr:latest is a Splitgraph Image, you can clone the data from Spltgraph Cloud to your local engine, where you can query it like any other Postgres database, using any of your existing tools.
First, install Splitgraph if you haven't already.
Clone the metadata with sgr clone
This will be quick, and does not download the actual data.
sgr clone cityofchicago/violence-reduction-victims-of-homicides-and-gumc-mgzrCheckout the data
Once you've cloned the data, you need to "checkout" the tag that you want. For example, to checkout the latest tag:
sgr checkout cityofchicago/violence-reduction-victims-of-homicides-and-gumc-mgzr:latestThis will download all the objects for the latest tag of cityofchicago/violence-reduction-victims-of-homicides-and-gumc-mgzr and load them into the Splitgraph Engine. Depending on your connection speed and the size of the data, you will need to wait for the checkout to complete. Once it's complete, you will be able to query the data like you would any other Postgres database.
Alternatively, use "layered checkout" to avoid downloading all the data
The data in cityofchicago/violence-reduction-victims-of-homicides-and-gumc-mgzr:latest is 0 bytes. If this is too big to download all at once, or perhaps you only need to query a subset of it, you can use a layered checkout.:
sgr checkout --layered cityofchicago/violence-reduction-victims-of-homicides-and-gumc-mgzr:latestThis will not download all the data, but it will create a schema comprised of foreign tables, that you can query as you would any other data. Splitgraph will lazily download the required objects as you query the data. In some cases, this might be faster or more efficient than a regular checkout.
Read the layered querying documentation to learn about when and why you might want to use layered queries.
Query the data with your existing tools
Once you've loaded the data into your local Splitgraph Engine, you can query it with any of your existing tools. As far as they're concerned, cityofchicago/violence-reduction-victims-of-homicides-and-gumc-mgzr is just another Postgres schema.