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 uniform_code_annual_report_submissions_beginning table in this repository, by referencing it like:
"ny-gov/uniform-code-annual-report-submissions-beginning-kpkx-452i:latest"."uniform_code_annual_report_submissions_beginning"or in a full query, like:
SELECT
":id", -- Socrata column ID
"operating_permits_high", -- Permits for buildings containing one or more areas of public assembly with an occupant load of 100 or more persons.
"muniname", -- Name of municipality
"countyname", -- Name of county which municipality belongs
"personnelhoursperweek", -- Total of weekly hours of work reported for people listed in report as performing code enforcement or administration functions. Not applicable before 2015.
"complaints_fire_received", -- Uniform Code complaints received
"compliance_new_commercial", -- Average number of construction inspections conducted for Uniform Code compliance for each new commercial building. Before 2016, options were 0 to 4 or more. Not applicable before 2008.
"special_inspections_permit", -- 1 if jurisdiction require a statement of special inspections required as part of a condition for permit issuance. 0 otherwise. Not applicable before 2009.
"personnelfulltime", -- Number of full-time (>20 hours per week) people listed in report as performing code enforcement or administration functions. Blank means not specified. Not applicable after 2015.
"complaints_estimate", -- 1 if any complaints numbers reported are estimates. 0 otherwise. Not applicable prior to 2016.
"inspections_estimate", -- 1 if any of the reported numbers of inspections are estimated. 0 otherwise. Not applicable prior to 2016.
"special_inspections_reports", -- 1 if jurisdiction retains special inspection reports. 0 otherwise. Not applicable before 2009.
"compliance_new_residential", -- Average number of construction inspections conducted for Uniform Code compliance for each new residential building. Before 2016, options were 0 to 4 or more. Not applicable before 2008.
"special_inspections_required", -- 1 if special inspections are required in the jurisdiction. 0 otherwise. Not applicable before 2009.
"nonresidential_uninspected", -- Nonresidential buildings not inspected in the last 36 months as of reporting period end. Not applicable before 2018.
"nonresidential_inspections", -- Nonresidential buildings inspected during reporting period
"nonresidential_occupancies", -- Nonresidential buildings (all others)
"dormitory_uninspected", -- Dormitories not inspected within last 12 months as of end of reporting period. Not applicable before 2018.
"dormitory_inspections", -- Dormitories inspected in reporting period. Not applicable before 2018.
"multiple_dwelling_inspections", -- Buildings containing 3 or more dwelling units inspected in reporting period
"public_assembly_occupancies_1", -- Buildings containing one or more areas of public assembly inspected in reporting period
"complianceuniformenforce_2", -- 1 if “software reports or printouts” are among procedures used by the local government to ensure compliance with the Uniform Code. 0 otherwise. Not applicable before 2008.
"complianceuniformenforce", -- 1 if “field inspections” are among procedures used by the local government to ensure compliance with the Uniform Code. 0 otherwise. Not applicable before 2008.
"unsafe_structures_notification", -- 1 if procedures been established for identifying and addressing unsafe structures and equipment. 0 otherwise.
"stop_work_orders_issued", -- Stop work orders issued.
"operating_permits_other_total", -- Other operating permits issued.
"operating_permits_parking", -- Permits for parking garages and structures. Not applicable before 2018.
"operating_permits_pyro", -- Permits for use of pyrotechnic devices in assembly occupancies.
"operating_permits_hazardous_1", -- Permits for hazardous processes and activities, including but not limited to, commercial and industrial operations which produce combustible dust as a by product, fruit and crop ripening, and waste handling.
"fire_notification_procedures", -- 1 if procedures been established for notification by the chief of the fire department(s) regarding fire or explosion involving any structural damage, fuel burning appliance, chimney, or gas vent. 0 otherwise.
"operating_permits_hazardous_2", -- Permits for buildings whose use or occupancy classification may pose a substantial potential hazard to public safety, as determined by the government or agency charged with or accountable for administration and enforcement of the Uniform Code.
"building_permits", -- Nonresidential buildings, new construction.
"building_permits_small_1", -- One-family dwellings, two-family dwellings, and townhouses. Repairs, alterations, etc. of existing. Not applicable before 2008.
"compliance_more_restrictive_1", -- 1 if each such local law or ordinance is approved or pending approval by the State Fire Prevention and Building Code Council pursuant to Executive Law Article 18, Section 379. 0 if no. -1 if unknown. Blank if not applicable.
"operating_permits_hazardous", -- Permits for manufacturing, storing, or handling hazardous materials in quantities exceeding those listed in Tables 5003.1.1(1-4) of the 2015 International Fire Code (see 19 NYCRR Part 1225).
"building_permits_certificates", -- Certificates of occupancy or compliance issued for all occupancies
"fire_safety_inspector", -- Who is responsible for fire safety inspections. Blank if fire department.
"period_start_eff", -- When reporting period starts. Generally January 1 of the reporting year. Not applicable before 2018.
"shared_per_gml_art_5g", -- 1 if local government entered into an agreement pursuant to General Municipal Law, Article 5-G, with one or more other local governments to jointly administer the Uniform or Energy Code. 0 otherwise. Not applicable before 2018.
"munitype", -- Type of municipality (City, County, Town, Village)
"reportyear", -- Reporting year for municipality's annual submission
"complaints_fire_action", -- Uniform Code complaints acted upon
"building_permits_others", -- All other permits (pools, sheds, decks, plumbing, HVAC, etc.)
"building_permits_1", -- Nonresidential buildings, repairs, alterations, etc. of existing. Not applicable before 2008.
"personnelcount", -- People listed in report as performing code enforcement or administration functions
"special_inspections", -- 1 if jurisdiction require special inspection reports to be submitted prior to issuance of certificates of compliance or completion. 0 otherwise. Not applicable before 2009.
"dormitory_buildings", -- Dormitory buildings. Not applicable before 2018.
"building_permits_multi", -- Other residential occupancies, new construction.
"compliance_more_restrictive_2", -- Year and number of the local law(s) or ordinance(s) that imposes construction standards that are more stringent, less stringent, or otherwise different from the requirements imposed by the Uniform Code. Blank if inapplicable.
"authorizing_legislation", -- Local law(s), ordinance(s) or other legislation that provides for the administration and enforcement of the Uniform Code and/or the Energy Code in the local government. Blank if responsibility transferred to another local government.
"multiple_dwelling_uninspected", -- Buildings containing 3 or more dwelling units not inspected in last 36 months as of last day in reporting period. Not applicable before 2018.
"contractor_meets_educational", -- 1 if the third party meets the education requirements of 1203.2(e)(1). 0 if no. -1 if unknown. Blank if not answered (i.e. no contracted services are used).
"period_end_eff", -- When reporting period ends. Generally December 31 of the reporting year. Not applicable before 2018.
"multiple_dwelling_units", -- Dwelling units in buildings containing 3 or more dwelling units. Not applicable before 2009.
"uniform_code_municode", -- Municode identifier for municipality that enforces uniform code. Blank if completed by submitter.
"swiskey", -- Unique identifier for municipality
"multiple_dwelling_buildings", -- Buildings containing 3 or more dwelling units
"public_assembly_occupancies_2", -- Buildings Containing One Or More Areas of Public Assembly not inspected in last 12 months. Not applicable before 2018.
"public_assembly_occupancies", -- Buildings containing one or more areas of public assembly
"complianceuniformenforce_4", -- 1 if any other procedures are used by the local government to ensure compliance with the Uniform Code. 0 otherwise. Not applicable before 2008.
"record_keeping_system", -- 1 if a system of records of the features and activities specified in 19 NYCRR 1203.3(a-j) has been established and maintained. 0 otherwise.
"compliance_more_restrictive", -- 1 if municipality has in effect any local law or ordinance that imposes construction standards that are more stringent, less stringent, or otherwise different from the requirements imposed by the Uniform Code. 0 otherwise. Not applicable before 2008.
"complianceuniformenforce_3", -- 1 if “compliance checklists” are among procedures used by the local government to ensure compliance with the Uniform Code. 0 otherwise. Not applicable before 2008.
"includes_fire_inspections", -- 1 if submission includes data related to fire safety inspections. 0 otherwise. Only applicable before 2018.
"includes_personnel", -- 1 if submitter is employee of the local government. 0 otherwise. Not applicable before 2018 or if municipality has not transferred enforcement to another local government's responsibility.
"contracted_services_used", -- 1 if third-party contracted services used to meet the requirements of 19 NYCRR Part 1203. 0 otherwise.
"opt_out_per_exc_381_2", -- 1 if local government adopted a local law providing it will **not** enforce the Uniform Code and Energy Code that was in effect during the reporting period. 0 otherwise. Not applicable before 2018.
"includes_building_permits", -- 1 if submission includes data related to building permitting. 0 otherwise. Only applicable before 2018.
"municode", -- OSC issued unique identifier for municipality
"fire_department_fire_safety", -- 1 if the fire department performs fire safety inspections. 0 if no. Not applicable before 2008.
"building_permits_small", -- One-family dwellings, two-family dwellings, and townhouses, new construction.
"building_permits_multi_1", -- Other residential occupancies, repairs, alterations, etc. of existing.
"operating_permits_required", -- 1 if operating permits are required in the jurisdiction. 0 otherwise. Not applicable before 2009.
"complianceuniformenforce_1" -- 1 if “plan reviews” are among procedures used by the local government to ensure compliance with the Uniform Code. 0 otherwise. Not applicable before 2008.
FROM
"ny-gov/uniform-code-annual-report-submissions-beginning-kpkx-452i:latest"."uniform_code_annual_report_submissions_beginning"
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 ny-gov/uniform-code-annual-report-submissions-beginning-kpkx-452i 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 ny-gov/uniform-code-annual-report-submissions-beginning-kpkx-452i: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 ny-gov/uniform-code-annual-report-submissions-beginning-kpkx-452iCheckout 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 ny-gov/uniform-code-annual-report-submissions-beginning-kpkx-452i:latestThis will download all the objects for the latest tag of ny-gov/uniform-code-annual-report-submissions-beginning-kpkx-452i 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 ny-gov/uniform-code-annual-report-submissions-beginning-kpkx-452i: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 ny-gov/uniform-code-annual-report-submissions-beginning-kpkx-452i: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, ny-gov/uniform-code-annual-report-submissions-beginning-kpkx-452i is just another Postgres schema.