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 rsbs_mom_multifamily_onsite_survey_measure_level table in this repository, by referencing it like:
"ny-gov/rsbs-mom-multifamily-onsite-survey-measure-level-cif3-ww3e:latest"."rsbs_mom_multifamily_onsite_survey_measure_level"or in a full query, like:
SELECT
":id", -- Socrata column ID
"appliance_manufacturer", -- Name of appliance manufacturer [e.g., Alliance-Woods]
"appliance_manufacture_year_known_or_estimated", -- Year of appliance known or estimated? [e.g., Estimated-Known]
"year_of_appliance_manufacture", -- Appliance manufacture year [e.g., 1966-2014]
"number_of_units", -- Number of a specific type of appliance identified during on-site [e.g., 1-97]
"other_large_appliances", -- Other large appliances listed during onsite visit [e.g., Air Cleaner-Water Cooler]
"appliance_category_id", -- Identification code associated with each appliance category
"gas_provider", -- Gas utility provider for the multifamily building(s) [e.g., ConEd-UGI]
"number_of_lighting_controls", -- Number of installed lighting controls [e.g., 1-332]
"type_of_lighting_controls", -- Type of installed lighting controls [e.g., 24hr-Timer]
"no_efficient_light_bulbs", -- Number of efficient light bulbs [e.g., 0-1350]
"no_halogen_light_bulbs", -- Number of halogen light bulbs [e.g., 0-55]
"no_other_light_bulbs", -- Number of other light bulbs [e.g., 0-136]
"type_of_other_light_bulbs", -- Type of other light bulbs [e.g., 50w sodium-SH]
"location_of_lighting_unit", -- Where the lighting unit is installed on site [e.g., 2nd floor entry/sitting area-Stairways]
"dhw_fuel_type", -- Type of fuel used for DHW unit [e.g., Electricity-Oil #4]
"electric_provider", -- Electric utility [e.g., Central Hudson-RG&E]
"construction_type", -- Flag indicating construction type [e.g., Existing-New]
"county", -- County in New York where the multifamily building is located [e.g., Albany-Yates]
"multifamily_owner_manager_mom_survey_id", -- Anonymous unique identifier that links the on-site inspection data to the telephone survey with multifamily owners and managers. [0-400]
"site_id", -- Anonymous identifier applied to the multifamily site for the on-site multifamily inspection
"measure_id", -- Unique identifier for each measure reviewed during the on-site multifamily inspection
"equipment_category", -- Data collection was performed through database software; as a result there were individual datasets for each major equipment type. This column shows the dataset that is related to the specific measure.
"hvac_cooling_efficiency_data_source", -- Data source for HVAC cooling efficiency rating [e.g., AHRI Directory-Similar Equipment]
"is_there_an_hvac_heating_filter_slot_cover", -- Does the HVAC Heating unit have a filter slot cover? [e.g., No-Yes]
"does_the_hvac_heating_furnace_have_an_electronically_commutated", -- Presence of a electronically commutated motor on the HVAC heating unit [e.g., No-Yes]
"area_served", -- Type of area served by the measure [e.g. Common Area-Whole Building]
"appliance_category", -- Categorization of examined appliances [e.g., Clothes Dryer-Wine Cooler]
"heated_pool_hot_tub", -- Presence of heated pool or hot tub on site, including building and unit [e.g., Not Applicable]
"dhw_type", -- Type of DHW system [e.g., Central Heating Plant-Wall Hung Space Heater Boiler]
"no_of_disconnected_air_ducts", -- Count of air ducts which are disconnected [e.g., 0]
"hvac_ventilation_type", -- Room served by HVAC system [e.g., Bath-Whole House]
"is_hvac_heating_energy_star", -- Is the HVAC heating unit Energy Star rated? [e.g., No-Yes]
"no_fax_machines_plugged_into_smart_strips_or_outlets", -- Number of fax machines plugged into power outlets or smart strips during on-site visit [e.g., 0-20]
"dhw_insulation_thickness_inches", -- Thickness of DHW insulation, measured in inches [e.g., 0-3.5]
"no_vcr_s_plugged_into_smart_strips_or_outlets", -- Number of VCR's plugged into power outlets or smart strips during on-site visit [e.g., 0-20]
"no_tier_1_plug_loads_smart_strips_and_outlets", -- Number of Tier 1 plug loads, smart strips and outlets [e.g., 0-20]
"no_home_theater_systems_plugged_into_smart_strips_or_outlets", -- Number of home theater systems plugged into power outlets or smart strips during on-site visit [e.g., 0-20]
"no_stereo_systems_plugged_into_smart_strips_or_outlets", -- Number of stereo systems plugged into power outlets or smart strips during on-site visit [e.g., 0-20]
"construction_material_of_envelope", -- Construction material of building envelope [e.g., 2x10-Wood Frame]
"exterior_or_continuous_insulation_thickness_inches", -- Thickness of exterior or continuous insulation measured in inches [e.g., 0-5]
"no_gaming_consoles_plugged_into_smart_strips_or_outlets", -- Number of gaming consoles plugged into power outlets or smart strips during on-site visit [e.g., 0-20]
"exterior_or_continuous_insulation_type", -- Type of exterior or continuous insulation [e.g., EPS-XPS]
"no_printers_and_copiers_plugged_into_smart_strips_or_outlets", -- Number of printers and copiers plugged into power outlets or smart strips during on-site visit [e.g., 0-20]
"percentage_of_total_exterior_and_interior_surfaces_that_are_ins", -- Percentage of total exterior and interior surfaces that are insulated [e.g.,0-100]
"number_of_windows", -- Number of windows identified during on-site visit [e.g.,1-10912]
"door_material", -- Material the door is made of [e.g., Foamed Filled Steel-Wood w/Glazing]
"u_factor_of_window_insulation", -- U factor rating of window insulation [e.g., .1-1]
"year_hvac_cooling_unit_was_manufactured", -- Year HVAC cooling system was installed [e.g., 1970-2014]
"hvac_ventilation_space_served", -- Type of site served by HVAC ventilation system [e.g., MF Building-MF Unit]
"status_of_thermostat_at_time_of_inspection", -- Setting of thermostat when inspector visited site [e.g., Auto-Off]
"no_ac_units", -- Number of AC units identified during on-site visit [e.g., 1-20]
"what_unit_does_the_hvac_cooling_system_serve", -- Type of unit that the HVAC cooling system serves [e.g., MF Building-MF Unit]
"programmable_thermostats", -- Are the HVAC controlled thermostats programmable? [e.g., Non-Programmable-Programmable but not Programmed]
"when_was_hvac_cooling_system_last_serviced", -- Year HVAC cooling system was last serviced by an HVAC professional [e.g., 1990-2014]
"cooling_filter_slot_cover", -- Presence of an HVAC cooling filter slot cover [e.g., No-Yes]
"unit_in_conditioned_or_unconditioned_part_of_house", -- United located in conditioned or unconditioned part of house [e.g., Conditioned-Unconditioned]
"hvac_heating_model", -- HVAC heating unit model number
"hvac_heating_venting_type", -- Type of venting used by the heating unit [e.g., Atmospheric-Sealed Combustion]
"does_the_hvac_heating_system_have_thermostatic_radiator_valves", -- Does the HVAC Heating System have Thermostatic Radiator valves? [e.g., No-Yes]
"no_t12_light_bulbs", -- Number of T12 light bulbs [e.g., 0-588]
"window_size_sqft", -- Size of the window [e.g., 2-80]
"set_point_temperature_in_heating_season", -- Temperature heating unit is set to during heating (degrees in Fahrenheit) season [e.g., 50-78]
"what_percentage_of_conditioned_space_does_the_hvac_heating_unit", -- What Percentage of Conditioned Space Does the HVAC Heating Unit Serve? [e.g., 10-100]
"what_unit_does_the_hvac_heating_system_serve", -- What type of space does the heating unit serve? [e.g., MF Building-SF Home]
"air_duct_supply_insulation_thickness_inches", -- Thickness of the insulation around the supply air duct in inches [e.g., 0-1]
"climate_zone", -- IECC climate Zone [e.g., Climate Zone 4-6]; see https://energycode.pnl.gov/EnergyCodeReqs/?state=New%20York for a map and details for each zone
"no_space_heaters_plugged_into_smart_strips_or_outlets", -- Number of space heaters plugged into power outlets or smart strips during on-site visit [e.g., 0-20]
"condition_of_hvac_cooling_furnace_air_filter", -- Condition of HVAC cooling furnace air filter [e.g., Fair-Poor]
"hvac_systems_served", -- Which units are using HVAC controls? [e.g., All Systems-Secondary]
"depth_below_grade_feet", -- Building depth below grade measured in feet [e.g., <1-13]
"gas_filled_window", -- Presence of a gas filled window [e.g., No-Yes]
"year_hvac_heating_unit_was_manufactured", -- Year HVAC Heating unit was manufactured [e.g., 1895-2014]
"observed_visual_condition_of_hvac_heating_system", -- Condition of heating system based on a visual inspection [e.g., Not Well Maintained-Well Maintained]
"no_dvd_players_plugged_into_smart_strips_or_outlets", -- Number of DVD Players plugged into power outlets or smart strips during on-site visit [e.g., 0-20]
"no_cable_boxes_plugged_into_smart_strips_or_outlets", -- Number of cable boxes plugged into power outlets or smart strips during on-site visit [e.g., 0-20]
"no_tier_2_plug_loads_smart_strips_and_outlets", -- Number of Tier 2 plug loads, smart strips and outlets [e.g., 0-20]
"dhw_manufacture_year", -- Year that present DHW unit was manufactured [e.g., 1950-2014]
"cooling_setback_hours", -- Number of hours cooling setback runs for [e.g., <1-8]
"hvac_cooling_manufacturer", -- Manufacturer of HVAC cooling system [e.g., AIRE-York]
"hvac_cooling_capacity_btu_hr", -- Measured HVAC cooling capacity [e.g., 5000-205000]
"rated_efficiency_of_hvac_cooling_system", -- Rated efficiency of HVAC cooling system based on on-site visit [e.g., 7-23]
"hvac_heating_capacity_btu_hr", -- End range of the heating unit, measured in BTU [e.g., 1,500-12,500,000]
"rated_efficiency_of_hvac_heating_system", -- The heating fuel usage efficiency rating of the heating unit [e.g., 1-100]
"no_incandescent_light_bulbs", -- Number of incandescent light bulbs [e.g., 0-291]
"unit_id", -- Anonymous identifier applied to the apartment served by the individual measure for the on-site multifamily inspection
"common_space_id", -- Anonymous identifier applied to the common space served by the individual measure for the on-site multifamily inspection
"region", -- Region in New York [e.g., Capital District-Western New York]
"type_style_of_appliance", -- Type and style of appliance [e.g., Bottom Freezer-Upright]
"unit_manufacturer", -- Manufacturer of DHW unit [e.g., A.O Smith-Williamson]
"unit_energy_factor", -- Energy Factor efficiency rating of DHW unit [e.g., 0.5-2.75]
"hvac_cooling_model", -- Model of HVAC cooling system
"hvac_heating_system_type", -- Type of heating system [e.g., Air Source Heat Pump-Wall Furnace]
"description_of_other_plug_loads", -- What type of plug load is being described [e.g., Breadmaker- Wheelchair Charger (on 24/7)]
"no_other_plug_loads", -- Number of appliances plugged into power outlets during on-site visit that don't fit pre-existing categories [e.g., 1-3]
"dhw_manufacture_year_known_or_estimated", -- Is the year estimated or known? [e.g., Estimated-Known]
"unit_model", -- DHW unit model number
"efficiency_data_source", -- Source of efficiency data collected for DHW system [e.g., AHRI Directory-Similar Equipment]
"water_temperature_setting_dhw", -- Water temperature setting [e.g., 116-150]
"air_duct_insulation_type_in_unconditioned_space", -- Type of insulation used in unconditioned sections of site [e.g., Bubble wrap-rigid duct board]
"television_screen_size_inches", -- Size of television screen measured in inches [e.g., 4-60]
"refrigerator_with_an_indoor_water_dispenser", -- Presence of refrigerator with in-door water dispenser [e.g., No-Yes]
"size_of_refrigerator_freezer_cubic_ft", -- Refrigerator or freezer size, measured in cubic feet [e.g., 4-30]
"location_of_refrigerator", -- Where is the refrigerator located [e.g., Conditioned-Unconditioned]
"clothes_washer_usage_pattern_loads_week", -- Number of loads the clothes washer or dryer use per week [e.g., 0-200+]
"clothes_washing_machine_temperature", -- Typical washing machine temperature setting used [e.g., All of the Above-Medium]
"humidifier_dehumidifier_usage", -- Was humidifier or dehumidifier used seasonally or all year round [e.g., Not applicable-Winter]
"fan_usage", -- Presence of fans used seasonally or all year round [e.g., Summer-Year Round]
"dhw_venting", -- Type of DHW venting found in the building or unit [e.g., Atmospheric-Sealed Combustion]
"dhw_unit_heat_recovery", -- Presence of DHW unit heat recovery system [e.g., No-Yes]
"efficiency_rating_of_dhw_heat_recovery", -- Efficiency rating of DHW heat recovery system [e.g., 9-100]
"presence_of_orphan_natural_draft_dhw", -- Presence of an orphan natural draft DHW [e.g., No]
"storage_volume_of_the_unit_gallons", -- Size of storage tank in gallons [e.g., 0-500]
"thickness_of_dhw_pipe_insulation_inches", -- DHW pipe insulation thickness measured in inches [e.g., 0-3]
"surface_type", -- Type of surface inspected [e.g., Ceiling-Wall]
"construction_material_of_envelope_categorical", -- Categorization of examined Construction Materials of the site Envelope [e.g., Brick-Wood Frame]
"ceiling_type", -- Type of Ceiling [e.g., Cathedral-Vented Attic]
"attic_access_insulation_level", -- Quality of insulation found by the attic access [e.g., None-Good]
"attic_access_square_feet", -- Square feet of attic access [e.g., 3-42]
"attic_access_weather_stripping", -- Presence of weather stripping around attic access [e.g., None-Good]
"data_source_for_the_on_site_inspector_determining_exterior_or_c", -- Data source that the on-site inspector used to determine the exterior or continuous insulation characteristics [e.g., Educated Guess-Reported]
"exterior_or_continuous_insulation_quality_grade_i_iii", -- Quality of exterior or continuous insulation [e.g., Grade I-Grade III]
"interior_or_cavity_insulation_type", -- Type of interior or cavity insulation [e.g., 8" blown cellulose covered with a layer of 6"blown fiber glass.-XPS]
"data_source_for_the_on_site_inspector_determining_interior_or_c", -- Data source the on-site inspector used to determine the interior or cavity insulation characteristics [e.g., Educated Guess-Reported]
"interior_or_cavity_insulation_quality_grade_i_iii", -- Quality of interior or cavity insulation [e.g., Grade I - Grade III ]
"interior_or_cavity_insulation_thickness_inches", -- Thickness of interior or cavity insulation measured in inches [e.g., 1-30]
"does_the_home_have_perimeter_insulation", -- Does the home have perimeter insulation [e.g., No-Yes]
"window_or_door", -- Is the measure observed a window or door? [e.g., door-window]
"number_of_doors", -- Number of doors identified during on-site visit [e.g.,1-225]
"door_weather_stripping", -- Presence and quality of door weather stripping [e.g., Fair-Poor]
"overall_window_condition", -- Overall condition of window [e.g., Poor-Good]
"window_frame_type", -- Type of window frame [e.g., Metal-Wood]
"window_glazing", -- Type of window glazing [e.g., Double-Single w/Storm Windows]
"presence_of_bay_windows", -- Presence of bay windows [e.g., No-Yes]
"storm_windows", -- Presence and/or location of storm windows [e.g., Exterior-None]
"window_treatments", -- Window treatments identified during on-site visit [e.g., None-Shading]
"hvac_controls_thermostat_type", -- Type of Thermostat [e.g., Analog-Whole Building OAT]
"cooling_setback_temperature", -- Temperature cooling unit is set to during setback (degrees in Fahrenheit) [e.g., 68-75]
"heating_setback_hours", -- Number of hours heating setback runs for [e.g., <1-15]
"heating_setback_temperature", -- Temperature heating unit is set to during setback (degrees in Fahrenheit) [e.g., 55-68]
"set_point_temperature_in_cooling_season", -- Temperature cooling unit is set to during cooling (degrees in Fahrenheit) season [e.g., 68-78]
"what_percentage_of_conditioned_space_does_the_hvac_cooling_unit", -- Total percentage of conditioned space that HVAC cooling unit serves [e.g., 10-100]
"is_hvac_cooling_primary_or_secondary_source", -- Is the cooling unit in question the primary source of cooling? [e.g., Primary-Secondary, Simultaneous]
"hvac_cooling_system_type", -- Type of HVAC cooling system examined during on-site [e.g., Air Source Heat Pump-Room Window AC]
"hvac_cooling_manufacture_year_known_or_estimated", -- Is the installation year of the HVAC cooling system known or estimated? [e.g., Estimated-Known]
"hvac_cooling_efficiency_type", -- Specific type of HVAC cooling efficiency [e.g., EER-SEER]
"observed_visual_condition_of_hvac_cooling_system", -- Observed visual condition of HVAC cooling system during on-site visit [e.g., Not Well Maintained-Well Maintained]
"is_hvac_cooling_energy_star", -- Presence of an Energy Star rated HVAC Cooling system [e.g., No-Yes]
"location_of_hvac_cooling_system", -- Location of HVAC cooling system is conditioned or not conditioned [e.g., Conditioned-Unconditioned]
"is_heating_primary_or_secondary_source", -- Is the heating unit the primary or secondary source of heating? [e.g., Primary-Secondary, Simultaneous]
"hvac_heating_manufacture_year_estimated_or_known", -- Is the year of manufacture of the heating unit known or estimated? [e.g., Estimated-Known]
"hvac_heating_manufacturer", -- Name of manufacturer of heating unit [e.g., A.L. Eastmond and Sons-York]
"hvac_system_fuel_type", -- Type of fuel used by the heating unit [e.g., Electricity-Steam]
"hvac_heating_efficiency_type", -- Unit that the heating efficiency of the heating unit is measured in [e.g., AFUE %-unknown]
"number_of_hvac_heating_systems", -- Number of heating systems in the unit or building [e.g., 1-100]
"hvac_heating_efficiency_data_source", -- What was the efficiency data for the heating unit derived from? [e.g., Educated Guess-testing papers by NYC located in basement]
"condition_of_hvac_heating_furnace_air_filter", -- The condition of the HVAC heating furnace air filter [e.g., Fair-Poor]
"location_of_hvac_heating", -- Type of location Heating unit is located in [e.g., Conditioned-Unconditioned]
"does_the_hvac_heating_system_have_an_outdoor_reset", -- Does the HVAC Heating System Have an Outdoor Reset? [e.g., No-Yes]
"when_was_hvac_heating_system_last_serviced", -- When was the heating unit last serviced by an HVAC professional? [e.g., 1979-2014]
"hvac_ventilation_system_configuration", -- Type of air flow created by HVAC ventilation system [e.g., Balanced-Supply]
"number_of_hvac_ventilation_operating_hours_per_day", -- Number of hours per day HVAC ventilation system is run [e.g., <1-24]
"dishwasher_usage_pattern_cycles_week", -- Frequency that dishwasher is used, measured in cycles per week [e.g., 1 - 30]
"dishwasher_efficiency_energy_factor", -- Efficiency rating of dishwasher, measured by its Energy Factor [e.g., 0.46-0.95]
"computer_monitor_type", -- Type of computer monitor present during on-site [e.g., CRT-LED]
"energy_star_certified", -- Is the appliance Energy Star certified [e.g., No-Yes]
"appliance_fuel", -- Type of fuel used by individual appliance type [e.g., Electricity-Natural Gas]
"wattage_of_appliance", -- Wattage of appliance identified [e.g., 50-9321]
"model_number", -- Model number of appliance
"air_duct_type_in_unconditioned_space", -- Type of air duct in unconditioned sections of site [e.g., Flexible-Metal]
"visual_inspection_of_air_duct_seal", -- Observed quality of air duct sealing [e.g., Connections Sealed-Some Leaks]
"air_duct_distribution_that_is_insulated_in_unconditioned_space", -- Percent of air duct which is insulated in an unconditioned section of the site [e.g., 0-30]
"air_duct_distribution_in_unconditioned", -- Percent of air duct which is located in an unconditioned section of the site[e.g., 0-100]
"thickness_of_insulation_around_the_return_air_duct_inches", -- Thickness of the insulation around the return air duct in inches [e.g., 0-1]
"type_of_air_duct_system", -- Type of air duct system inspected [e.g., Heating- No Distribution System Present]
"type_of_building_served_by_dhw", -- Type of building that is served by a DHW system [e.g., MF Building-MF Unit]
"no_of_water_heaters" -- Number of water heaters in unit or common space [e.g., 0-100]
FROM
"ny-gov/rsbs-mom-multifamily-onsite-survey-measure-level-cif3-ww3e:latest"."rsbs_mom_multifamily_onsite_survey_measure_level"
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/rsbs-mom-multifamily-onsite-survey-measure-level-cif3-ww3e with SQL in under 60 seconds.
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, 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/rsbs-mom-multifamily-onsite-survey-measure-level-cif3-ww3e: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/rsbs-mom-multifamily-onsite-survey-measure-level-cif3-ww3eCheckout 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/rsbs-mom-multifamily-onsite-survey-measure-level-cif3-ww3e:latestThis will download all the objects for the latest tag of ny-gov/rsbs-mom-multifamily-onsite-survey-measure-level-cif3-ww3e 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/rsbs-mom-multifamily-onsite-survey-measure-level-cif3-ww3e: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/rsbs-mom-multifamily-onsite-survey-measure-level-cif3-ww3e: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/rsbs-mom-multifamily-onsite-survey-measure-level-cif3-ww3e is just another Postgres schema.