wa-gov/independent-campaign-expenditures-and-67cp-h962
Icon for Socrata external plugin

Query the Data Delivery Network

Query the DDN

The 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 independent_campaign_expenditures_and table in this repository, by referencing it like:

"wa-gov/independent-campaign-expenditures-and-67cp-h962:latest"."independent_campaign_expenditures_and"

or in a full query, like:

SELECT
    ":id", -- Socrata column ID
    "filer_id", -- This column is an alias for candidate_filer_id and may be removed in a future version of the dataset.
    "funders_location", -- The geographic coordinates for the address of any source of funding used for this electioneering communication as provided in section 5 of the C6 report. The accuracy of the coordinates is determined by the quality of the address provided and in some cases may be loosely based on the zip code alone. In cases where the address can not be converted to coordinates, this field will be empty.
    "vendor_location", -- The geographic coordinates of the vendor's address as provided on the C6 report for an expenditure. The accuracy of the coordinates is determined by the quality of the address provided and in some cases may be loosely based on the zip code alone. In cases where the address can not be converted to coordinates, this field will be empty. 
    "sponsor_location", -- The geographic coordinates of the sponsor's address as provided on the C6 report. The accuracy of the coordinates is determined by the quality of the address provided and in some cases may be loosely based on the zip code alone. In cases where the address can not be converted to coordinates, this field will be empty. 
    "funders_employer_state", -- If a sponsor received funds from an individual in excess of $250 for an Electioneering Communication the source employer’s state must be disclosed.
    "funders_employer_city", -- If a sponsor received funds from an individual in excess of $250 for an Electioneering Communication the source employer’s city must be disclosed.
    "funders_employer", -- If a sponsor received funds from an individual in excess of $250 for an Electioneering Communication the source’s employer must be disclosed.
    "funders_occupation", -- If a sponsor received funds from an individual in excess of $250 for an Electioneering Communication the source’s zip code must be disclosed.
    "funders_zipcode", -- If a sponsor received funds from any source in excess of $250 for an Electioneering Communication the source’s zip code must be disclosed.
    "funders_state", -- If a sponsor received funds from any source in excess of $250 for an Electioneering Communication the source’s state must be disclosed.
    "funders_city", -- If a sponsor received funds from any source in excess of $250 for an Electioneering Communication the source’s city must be disclosed.
    "funders_address", -- If a sponsor received funds from any source in excess of $250 for an Electioneering Communication the source’s street address must be disclosed.
    "amount", -- If a sponsor received funds from any source in excess of $250 for an Electioneering Communication the amount must be reported.
    "date_received", -- If a sponsor received funds from any source in excess of $250 for an Electioneering Communication the date received must be reported.
    "funders_middle_initial", -- If a sponsor received funds from an individual in excess of $250 for an Electioneering Communication the source’s middle initial is disclosed if known.
    "funders_first_name", -- If a sponsor received funds from an individual in excess of $250 for an Electioneering Communication the sources first name must be disclosed.
    "funders_name", -- If a sponsor received funds from any source in excess of $250 for an Electioneering Communication the sources name must be disclosed. If the source is an individual this is their last name. If the source is an organization, this is the organization’s name.
    "for_or_against", -- Whether the advertising supports (for) or opposes (against)  the candidate or ballot proposition.
    "portion_of_amount", -- The portion of the itemized expenditure amount attributable to the candidate or ballot proposition.
    "ballot_type", -- This is the type of ballot initiative being supported or opposed; either Statewide or Local.
    "ballot_number", -- This is the initiative number issued by the Secretary of State for a Statewide ballot initiative. Local ballot initiatives also have numbers and may or may not be provided by the sponsor.
    "ballot_name", -- The name of the ballot initiative identified in the advertising. The sponsor determines what goes into this block. It can be an initiative number or a brief description (e.g. if the sponsor is supporting or opposing a statewide initiative they could input: I-7777, Initiative 7777, Traffic Congestion Initiative, etc.).
    "candidate_office_type", -- The type of office the identified candidate is seeking. Either Statewide, Legislative, Local, or Judicial.
    "candidate_party", -- The party affiliation of an identified candidate for a partisan office.
    "candidate_jurisdiction", -- The jurisdiction of the office being sought.
    "candidate_office", -- The office sought for any candidate identified in the advertising.
    "candidate_first_name", -- The first name of a candidate identified in the advertising.
    "candidate_last_name", -- The last name of a candidate identified in the advertising.
    "candidate_name", -- The name of a candidate identified in the advertising.
    "candidate_filer_id", -- The filer_id of a candidate identified in the advertising. The filer_id is a unique id assigned to a candidate. The filer id is consistent across election years with the exception that an individual running for a second office in the same election year will receive a second filer id. There is no correlation between the two. The combination of filer_id and election_year uniquely identifies a candidate campaign.
    "vendor_zipcode", -- The zip code of the vendor or recipient who created the advertising.
    "vendor_state", -- The state of the vendor or recipient who created the advertising.
    "vendor_city", -- The city of the vendor or recipient who created the advertising.
    "vendor_address", -- The street address of the vendor or recipient who created the advertising.
    "vendor_name", -- The name of the vendor or recipient who created the advertising.
    "date_advertising_presented", -- The date the advertising was first presented or mailed.
    "date_expense_obligated", -- The date the advertising expense was first obligated.
    "expenditure_description", -- The description of the expenditure (e.g. direct mail, or newspaper, TV, or radio ad).
    "expenditure_amount", -- The amount or value of an itemized expenditure.  If no reasonable estimate can be made of value the sponsor must describe the activity, services, property or right furnished precisely and attach a copy of the item produced or distributed. That attachment is available with the C6 image.
    "total_this_report", -- The sum of all itemized expenditures of more than $100 and the total_unitemized on this C6 report.
    "total_cycle", -- Total Independent Expenditures and Electioneering Communications made during this election campaign.  This amount includes the amounts shown on this C6 report and previously submitted C6 reports.
    "total_unitemized", -- Only expenditures of more than $100 must be itemized on the C6. But the aggregate of payments of $100 or less must be provided on this line.
    "sponsor_phone", -- The telephone number of the entity submitting the C6.
    "sponsor_email", -- The email address of the entity submitting the C6.
    "sponsor_zip", -- The zip code of the entity submitting the C6.
    "sponsor_state", -- The state of the entity submitting the C6.
    "sponsor_city", -- The city of the entity submitting the C6.
    "sponsor_address", -- The street address of the entity submitting the C6.
    "election_year", -- The election year in the case of a C6 is the reporting year.
    "report_date", -- The date the C6 is filed with the PDC.
    "report_type", -- There are three report types that can be reported on a C6: a) Independent Expenditures (occurring at any time) -- $100 or more; b) Independent Expenditure Ads (appearing within 21 days of an election) -- $1,000 or more; c) Electioneering Communications (appearing within 60 days of an election) -- $1,000 or more. Please see https://www.pdc.wa.gov/learn/forms for a list of forms and instructions.
    "sponsor_description", -- If reporting an Electioneering Communication, it is necessary to disclose a description of the source of funding for the communication. The sponsor can be one of these descriptions:      a) An individual using only personal funds;       b) An individual using personal funds and/or funds received from others;       c) A business, union, group, association, organization, or other person using              only general treasury funds;       d) A business, union, group, association, organization, or other person using              general treasury funds and/or funds received from others;       e) A political committee filing C-3 and C-4 reports (RCW 42.17A.205 - .240);       f) A political committee filing C-5 reports (RCW 42.17A.250);      g) Other.
    "sponsor_name", -- The name of the individual, organization, or person who made the Independent Expenditure or Electioneering Communication – this is the person who sponsored the advertising as reported on the C6 in block 1.
    "sponsor_id", -- The unique id assigned to the sponsor of an Independent Expenditure. The sponsor id is consistent across election years and C6 reports. 
    "origin", -- The form, schedule or section where the record was reported. Please see https://www.pdc.wa.gov/learn/forms for a list of forms and instructions.  C6.2 – Itemized Expenditures:  represents itemized expenditures of more than $100 as reported in block 2 on the C6.  C6.3 – Identified Entity:  represents the Candidates and Ballot Propositions identified in the advertising as reported in block 3 on the C6..  C6.5 – Funding Source:  represents the funding sources who gave in excess of $250 for an Electioneering Communication as reported in block 5 on the C6.   
    "report_number", -- PDC identifier used for tracking the individual form C6 . Multiple entries will have the same report number when they were reported to the PDC at the same time. The report number is unique to the report it represents. When a report is amended, a new report number is assigned that supersedes the original version and the original report records are not included in this dataset.
    "id", -- PDC internal identifier that corresponds to a single contribution or correction record. When combined with the origin value, this number uniquely identifies a single row. 
    "url" -- A link to a PDF version of the original report as it was filed to the PDC.
FROM
    "wa-gov/independent-campaign-expenditures-and-67cp-h962:latest"."independent_campaign_expenditures_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 wa-gov/independent-campaign-expenditures-and-67cp-h962 with SQL in under 60 seconds.

This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.wa.gov. When you querywa-gov/independent-campaign-expenditures-and-67cp-h962:latest on the DDN, we "mount" the repository using the socrata mount handler. The mount handler proxies your SQL query to the upstream data source, translating it from SQL to the relevant language (in this case SoQL).

We also cache query responses on the DDN, but we run the DDN on multiple nodes so a CACHE_HIT is only guaranteed for subsequent queries that land on the same node.

Query Your Local Engine

Install Splitgraph Locally
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 (like this repository), 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, where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.

Mounting Data

This repository is an external repository. It's not hosted by Splitgraph. It is hosted by data.wa.gov, and Splitgraph indexes it. This means it is not an actual Splitgraph image, so you cannot use sgr clone to get the data. Instead, you can use the socrata adapter with the sgr mount command. Then, if you want, you can import the data and turn it into a Splitgraph image that others can clone.

First, install Splitgraph if you haven't already.

Mount the table with sgr mount

sgr mount socrata \
  "wa-gov/independent-campaign-expenditures-and-67cp-h962" \
  --handler-options '{
    "domain": "data.wa.gov",
    "tables": {
        "independent_campaign_expenditures_and": "67cp-h962"
    }
}'

That's it! Now you can query the data in the mounted table like any other Postgres table.

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, wa-gov/independent-campaign-expenditures-and-67cp-h962 is just another Postgres schema.