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 2024_primary_election_mail_ballot_requests table in this repository, by referencing it like:
"pa-gov/2024-primary-election-mail-ballot-requests-8yp7-qf6j:latest"."2024_primary_election_mail_ballot_requests"or in a full query, like:
SELECT
":id", -- Socrata column ID
":@computed_region_3x3q_vpda", -- This column was automatically created in order to record in what polygon from the dataset 'US House Districts for PA 2019' (3x3q-vpda) the point in column 'georeferenced_latitude_longitude' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
":@computed_region_75dh_jrw3", -- This column was automatically created in order to record in what polygon from the dataset 'PA State Senate Districts 2016 Health' (75dh-jrw3) the point in column 'georeferenced_latitude_longitude' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
":@computed_region_gbji_5m4q", -- This column was automatically created in order to record in what polygon from the dataset 'US House Districts for PA 2019 Health' (gbji-5m4q) the point in column 'georeferenced_latitude_longitude' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
":@computed_region_4fjn_fq7k", -- This column was automatically created in order to record in what polygon from the dataset 'PA County Boundaries Spatial Data Current Transportation' (4fjn-fq7k) the point in column 'georeferenced_latitude_longitude' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
":@computed_region_its3_bt6a", -- This column was automatically created in order to record in what polygon from the dataset 'PA State House Districts 2016 Health' (its3-bt6a) the point in column 'georeferenced_latitude_longitude' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
"disposition_reason", -- Reason for the Disposition of the Ballot Application. Counties can only choose one reason, even if there could be multiple reasons to reject an application. The specific rejection reason a county chooses to enter first depends on county practices.<br>• APPR – ID NOT VERIFIED<br>• APPR – ID VERIFICATION PEND<br>• APPR – ID VERIFIED<br>• APPR - VALID APPLICATION-MAIL<br>• DECL - AFTER DEADLINE<br>• DECL - DECEASED<br>• DECL – DUPLICATE APPLICATION<br>• DECL - INCOMPLETE ADDRESS<br>• DECL - INVALID REASON<br>• DECL - NO SIGNATURE<br>• DECL - NOT REGISTERED<br>• DECL - SIGNATURE MISMATCH<br>• DECL - UNAFFILIATED (NO NON-PARTISAN ISSUES)
"senate", -- The applicant’s state senate district.
"ballot_status", -- Status of the ballot<br>• Pending: The county needs additional information or action by the voter before the ballot can be counted. This may be because the voter has not yet returned it, or the voter should take some additional action to ensure the ballot envelopes accord with statutory requirements (such as the requirement that a voter sign the envelope).<br>• Received: Received by county election authority.<br>• Cancelled: The county election authority cancelled the ballot. This may be because the voter has received a replacement ballot, or because their ballot could not be counted.
"ballot_status_reason", -- Reason for the status of the ballot. Counties can only choose one reason, even if there could be multiple reasons for the same disposition. The specific cancellation reason a county chooses to enter first depends on county practices. The following are optional values for this field:<br>• CANC – EMAIL BALLOT UND BATCH: Ballot cannot be delivered to the email address provided by the voter.<br>• CANC – EMAIL BALLOT UNDELIVERABLE: The email sent with the ballot was returned as undeliverable.<br>• CANC – INCORRECT DATE: Voter provided the incorrect date on the ballot return envelope, which may include forgetting a portion of the date (month, day, or year), or including a date that is not between when the ballot was mailed and election day (e.g., a birthday).<br>• CANC – LABEL CANCELLED: County elections office cancelled the ballot due to an error on the label.<br>• CANC – NO DATE: Voter did not provide a date on the ballot return envelope.<br>• CANC – NO ID: Voter has not provided valid I.D. to verify their ballot.<br>• CANC – NO SECRECY ENVELOPE: Voter forgot to include the secrecy envelope.<br>• CANC – NO SIGNATURE: Voter forgot to sign the return envelope.<br>• CANC – REPLACED: Voter was issued a replacement ballot.<br>• CANC – RETURNED AFTER DEADLINE: Ballot was received after deadline.<br>• CANC – UNDELIVERABLE: Ballot was returned to the county because the address it was mailed to was incorrect or does not exist.<br>• CANC – VOTE CANCELLED: The original code counties used to cancel a ballot before the above – more detailed codes – were created. Now exists as a generic category that a county may use to cancel a ballot if the reason does not fit in the more specific categories.<br>• CANC – VOTE CHALLENGED: Ballot was not counted because of a successful challenge.<br>• PEND – NOT YET RETURNED: Ballot that has been mailed by the county to the voter but has not been returned by the voter to the county.<br>• PEND – INCORRECT DATE: A code that counties may use to alert a voter that their ballot return envelope has an incorrect date, so that the voter may fix their error and have their ballot counted. The code is changed to ‘CANC - INCORRECT DATE’ if the error is not corrected.<br>• PEND – NO DATE: A code that counties may use to alert a voter that their ballot return envelope is not dated, so that the voter may fix their error and have their ballot be counted. The code is changed to ‘CANC - NO DATE’ if the error is not corrected.<br>• PEND – NO ID: A code that counties may use to alert a voter that their ballot will be cancelled if they do not verify their identification within six days of election day.<br>• PEND – NO SIGNATUE: A code that counties may use to alert a voter that their ballot return envelope is not signed, so that the voter may fix their error and have their ballot counted. The code is changed to ‘CANC - NO SIGNATURE’ if the error is not corrected.<br>• PEND – NO SECRECY ENVELOPE: A code that counties may use to alert a voter that their ballot is missing a secrecy envelope, so that the voter may fix their error and have their ballot counted. The code is changed to ‘CANC - NO SECRECY ENVELOPE’ if the error is not corrected.<br>• RECORD – BALLOT RETURNED: Ballot has been returned to the county without errors and will be counted on election day.
"mailapplicationtype", -- The mail ballot application type. The following are the possible values for this field.<br>• Alt - Alternative ballot (voters who are disabled or at least 65 years of age where the polling place may not be fully accessible)<br>• BV - Bedridden Veteran<br>• BVRI - Bedridden Veteran - Remote/Isolated<br>• C - Emergency Absentee ballot application<br>• CIV - Civilian absentee submitted via paper<br>• CRI - Overseas voter - Remote/Isolated<br>• CVO - Overseas voter<br>• F - Federal voter (voter is eligible to vote in federal races only)<br>• M - Military<br>• MAILIN - Mail-in submitted via paper<br>• MRI - Military - Remote/Isolated<br>• OLMAILV - Mail-in submitted online<br>• OLMAILNV - Mail-In submitted online where ID verification did not occur or was not successful at the time of submission<br>• OLREGV - Absentee submitted online<br>• OLREGNV - Absentee submitted online where ID verification did not occur or was not successful at the time of submission.<br>• PER - Absentee where applicant has requested permanent status<br>• PMI - Mail-in where applicant has requested permanent status<br>• REG - Absentee submitted via paper<br>• V - Veteran<br><br><i>Note: An application is designated as NV (not verified) if the county was unable to verify the application via the ID verification process prior to approving the application. <b><u>The voter will still be required to present proof of identification before their ballot may be counted.</u></b> However, even if the voter subsequently verifies their ID, the application will still be marked as NV in the SURE system. For example, a ballot application would be marked as NV if it is a new application and the provided ID was not able to be immediately verified. Or, for another example, a ballot application would be marked as NV if it is from a voter on the permanent mail-in or absentee list who requested a mail ballot for both the primary and general elections, and their ID needs to be verified again before the general election.</i>
"party", -- The applicant’s party as indicated on their application. The following are the possible values of this field:<br>• DEM: Democrat<br>• REP: Republican<br>• LIB: Libertarian<br>• GRN: Green<br>• OTH: Other/Independent
"perm_indicator", -- True/False – Indicates whether applicant has requested to be on the permanent absentee list or permanent mail-in list (True) or not (False).
"longitude", -- A generic Longitude point within the county.
"legislative", -- The applicant’s state house district.
"latitude", -- A generic Latitude point within the county.
"id_verification_status", -- True/False – This field indicates whether voter’s ID has been verified (True) or not (False).
"georeferenced_latitude_longitude", -- A georeferenced latitude and longitude based on the county of residence that can be used to create maps.
"dateofbirth", -- The applicant’s month and year of birth.
"countyname", -- The applicant’s county as indicated on their application.
"congressional", -- The applicant’s congressional district.
"ballotsentdate", -- The date the application was approved by the county, which queues a ballot label to mail the ballot materials to the voter. This column is updated when the county indicates they have mailed the ballot. If the county has not mailed the ballot or does not indicate the ballot was mailed, the date in which the ballot label was queued will remain.
"ballotreturneddate", -- The date the county marked the ballot as received.
"appreturndate", -- The date the application was approved by the county.
"appissuedate", -- The date the application was made.
"ballot_application_disposition" -- Disposition of the ballot application – Approved/Declined
FROM
"pa-gov/2024-primary-election-mail-ballot-requests-8yp7-qf6j:latest"."2024_primary_election_mail_ballot_requests"
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 pa-gov/2024-primary-election-mail-ballot-requests-8yp7-qf6j 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 pa-gov/2024-primary-election-mail-ballot-requests-8yp7-qf6j: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 pa-gov/2024-primary-election-mail-ballot-requests-8yp7-qf6jCheckout 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 pa-gov/2024-primary-election-mail-ballot-requests-8yp7-qf6j:latestThis will download all the objects for the latest tag of pa-gov/2024-primary-election-mail-ballot-requests-8yp7-qf6j 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 pa-gov/2024-primary-election-mail-ballot-requests-8yp7-qf6j: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 pa-gov/2024-primary-election-mail-ballot-requests-8yp7-qf6j: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, pa-gov/2024-primary-election-mail-ballot-requests-8yp7-qf6j is just another Postgres schema.