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 special_purpose_district_entities table in this repository, by referencing it like:
"texas-gov/special-purpose-district-entities-vtc6-p2xa:latest"."special_purpose_district_entities"or in a full query, like:
SELECT
":id", -- Socrata column ID
"totl_rate_pc", -- The property tax rate adopted by the governing body of a taxing unit. Sum of I&S Tax Rate and M&O Tax Rate equals the total property tax rate. The tax rate is per $100 valuation.
"mant_oper_rate_pc", -- The component of the adopted tax rate of a taxing unit that will impose the amount of taxes needed to fund maintenance and operation expenditures of the unit for the following year. The tax rate is per $100 valuation.
"avt_rlbk_rate_pc", -- The highest tax rate a tax rate a taxing unit may adopt before requiring voter approval through an election. The tax rate is per $100 valuation.
"totl_secr_p_i_am", -- Total amount borrowed of all property tax-secured obligations plus the cost of interest.
"totl_secr_debt_am", -- Total debt obligations secured by a pledge of property taxes.
"totl_prnc_ostd_am", -- Total amount borrowed of all obligations that have yet to be paid.
"etry_ts", -- Date and time report was submitted.
"othr_ent_ty_tx", -- When the type of Special Purpose District selected is "other", the type is specified.
"ent_ty_tx", -- The general type of Special Purpose District.
"cnty_cd", -- County code where the Special Purpose District is located.
"spd_ent_rpt_id", -- Unique ID number for each report.
"int_skng_rate_pc", -- The tax rate levied by districts to pay for any bond dent that may have been issued to fund the construction of facilities. The tax rate is per $100 valuation.
"avt_eff_rate_pc", -- The tax rate that would generate the same amount of revenue in the current tax year as was generated by a taxing unit's adopted tax rate in the preceding tax year from property that is taxable in both the current tax year and the preceding tax year. The tax rate is per $100 valuation.
"wbst_url_tx", -- Website that the Special Purpose District maintains.
"spd_publ_id", -- Unique ID number for each Special Purpose District.
"eff_mnt_op_rate_pc", -- The tax rate that would generate the same amount of revenue for the maintenance and operations in the current tax year as was generated by a taxing unit's maintenance and operations rate in the preceding tax year from property that is taxable in both the current tax year and the preceding tax year. The tax rate is per $100 valuation.
"totl_secr_prnc_am", -- Total amount borrowed of obligations secured by a pledge of property taxes that have yet to be repaid.
"avt_rate_pc", -- Property tax rate that is levied on a real or personal property by taxing districts for the reporting year. The tax rate is per $100 valuation.
"sale_tax_rate_pc", -- Districts can impose a Sales and Use Tax Rate of up to 2 percent on all retail sales, leases and rentals of most goods and taxable services.
"totl_prnc_int_am", -- Total amount borrowed that has yet to be repaid plus the cost of interest.
"city_nm", -- City where the Special Purpose District is located.
"tp_id", -- 11-Digit Texas Taxpayer ID assigned by the Comptroller.
"gros_rcpt_cd", -- Gross receipts refer to the total amount the entity received in exchange for property or services sold, leased or rented during a given period before deducting costs or expenses.
"totl_auth_debt_am", -- 1. Debt obligations are defined in the bill as issued public securities which are instruments, including bonds, certificates, notes, or other types of obligations authorized to be issued by an issuer under a statute, a municipal home-rule charter, or the constitution of this state (Government Code section 1201.002 (2)). 2. Issuance is the process of authorizing, selling and delivering public debt. 3. Public security authorization means a resolution , order, or ordinance that is approved or adopted, or any other action taken in a proceeding, by the governing body of an issuer in authorizing the issuance of a public security (Government Code section 1201.002 (3)).
"rptg_meth_cd", -- Debt reporting option selected - Local Annual Debt Report, direct web address or online form.
"ostd_bond_cd", -- Bonds authorized by the voters of the special purpose district that are payable wholly or partly from ad valorem taxes, excluding refunding bonds if refunding bonds were separately authorized and excluding contract revenue bonds, that have yet to be repaid.
"ent_dis_nm", -- Name of Special Purpose District.
"ent_sta_cd", -- Whether the entity is still active or not.
"wtr_dstc_cd", -- A local government entity that provides limited services to its customers and residents and is defined by Water Code Section 49.001. Examples of water districts include municipal utility districts, water control and improvement districts, special utility districts, and river authorities.
"no_crit_cd", -- Does not meet reporting requirements of outstanding bonds, gross receipts from operations, loans, taxes and cash and temporary investments in excess of $250,000.
"cash_temp_nvst_cd", -- Short-term, highly liquid investments that are both readily convertible to known amounts of cash and near maturity.
"rpt_yr" -- Year in which the Special Purpose District reported the entity's information.
FROM
"texas-gov/special-purpose-district-entities-vtc6-p2xa:latest"."special_purpose_district_entities"
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 texas-gov/special-purpose-district-entities-vtc6-p2xa 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 texas-gov/special-purpose-district-entities-vtc6-p2xa: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 texas-gov/special-purpose-district-entities-vtc6-p2xaCheckout 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 texas-gov/special-purpose-district-entities-vtc6-p2xa:latestThis will download all the objects for the latest tag of texas-gov/special-purpose-district-entities-vtc6-p2xa 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 texas-gov/special-purpose-district-entities-vtc6-p2xa: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 texas-gov/special-purpose-district-entities-vtc6-p2xa: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, texas-gov/special-purpose-district-entities-vtc6-p2xa is just another Postgres schema.