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 metro_resident_survey_responses table in this repository, by referencing it like:
"nashville-gov/metro-resident-survey-responses-nux7-cwab:latest"."metro_resident_survey_responses"or in a full query, like:
SELECT
":id", -- Socrata column ID
"_911_services", -- How satisfied are you with emergency 9-1-1 service?
"outdoor_athletic_fields", -- How satisfied are you with Metro outdoor athletic fields?
"swimming_pools", -- How satisfied are you with Metro swimming pools?
"senior_recreation_programs", -- How satisfied are you with Metro senior recreation programs?
"parks_personnel", -- How satisfied are you with the professionalism of Parks personnel?
"park_safety", -- How satisfied are you with your feeling of safety at Metro parks and community centers?
"burial_assistance_program", -- How satisfied are you with the burial assistance program?
"family_support_services", -- How satisfied are you with family support services (life management, counseling)?
"head_start_early_head_start", -- How satisfied are you with Head Start/Early Head Start?
"tap_water", -- How satisfied are you with drinking water straight from the tap?
"water_service_reliability", -- How satisfied are you with the reliability of [water] service in relation to outages?
"water_emergency_response", -- How satisfied are you with water, sewer and stormwater emergency response?
"water_services_value", -- How satisfied are you with the value of services provided by MWS?
"water_services_communication", -- How satisfied are you with MWS communications (e.g. bill, website, letters, door hangers)?
"water_services_information", -- How satisfied are you with the availability of information regarding [water] services and programs?
"water_services_social_media", -- How satisfied are you with MWS's social media engagement?
"water_services_employee", -- How satisfied are you with MWS employee interaction with the community?
"mnpd_officer_contact", -- Have you or anyone in your household had contact with a MNPD police officer in the last year?
"park_community_center_usage", -- Have you or anyone in your household visited a Metro park or community center in the last year?
"monthly_housing_cost_over", -- Does your monthly housing costs (rent/mortgage + utilities) exceed 30% of your monthly income?
"rideshare_bikeshare_usage", -- Have you or anyone in your household used rideshare, bikeshare, or a related service in the last year?
"commute_by_walking_biking", -- Have you or anyone in your household commuted to work by biking, walking, or transit in the last year?
"affordable_housing_preference", -- There should be more affordable housing in my neighborhood.
"opioid_addiction", -- Have you known someone addicted to opioids or other prescription painkillers?
"period", -- Calendar quarter in which the survey response was received
"service_district", -- Service district in which the respondent resides
"neighborhood_arts_and_culture", -- How satisfied are you with availability of arts/cultural activities in your neighborhood?
"nashville_as_place_to_start", -- Please rate Davidson County as a place to start/grow a business.
"nashville_as_place_to_raise", -- Please rate Davidson County as a place to raise children.
"flood_prevention", -- How satisfied are you with countywide flood prevention efforts?
"stormwater_fund_use", -- How satisfied are you with the use of stormwater funds?
"low_income_home_energy", -- How satisfied are you with the Low Income Home Energy Assistance Program?
"homeless_services", -- How satisfied are you with homeless services?
"nutrition_programs", -- How satisfied are you with nutrition programs for seniors and disabled persons?
"violent_crime_victim_services", -- How satisfied are you with the services provided to victims of violent crime?
"youth_recreation_programs", -- How satisfied are you with Metro youth recreation programs?
"community_centers", -- How satisfied are you with Metro community centers?
"nashville_gov", -- How satisfied are you with the Metro website (Nashville.gov)?
"metro_video_programming", -- How satisfied are you with Metro video programming (TV channel, web streaming)?
"hubnashville_311", -- How satisfied are you with 311 service (hubNashville)?
"program_information", -- How satisfied are you with the availability of information about Metro programs and services?
"metro_social_media", -- How satisfied are you with Metro use of social media?
"short_term_rental_enforcement", -- How satisfied are you with enforcing short-term rental property (STRP) regulations?
"strp_complaint_hotline", -- How satisfied are you with the STRP complaint hotline (Host Compliance/435-787-4357)?
"lawn_and_brush_enforcement", -- How satisfied are you with enforcing the mowing and cutting of weeds on private property?
"non_emergency_number", -- How satisfied are you with non-emergency (615-862-8600) service?
"no_children_in_davidson_county", -- [Respondent has no children under the age of 18 living in Davidson County]
"children_ages_0_5", -- Do you have any children ages 0-5 who live in Davidson County?
"other_school_definition", -- [Respondent-reported definition of an “Other” school]
"emergency_medical_services", -- How satisfied are you with the quality of local emergency medical services?
"codes_personnel", -- How satisfied are you with the professionalism of Codes personnel?
"mta_bus_service", -- How satisfied are you with MTA bus service?
"mta_personnel_professionalism", -- How satisfied are you with the professionalism of MTA personnel?
"fire_services", -- How satisfied are you with the quality of local fire services?
"special_event_security", -- How satisfied are you with security at special events?
"ems_response_time", -- How satisfied are you with how quickly emergency medical personnel respond to emergencies?
"street_lighting", -- How satisfied are you with the adequacy of street lighting?
"public_works_personnel", -- How satisfied are you with the professionalism of Public Works personnel?
"school_crossing_guards", -- How satisfied are you with the school crossing guard program?
"school_resource_officers", -- How satisfied are you with school resource officers?
"pedestrian_infrastructure", -- How satisfied are you with pedestrian infrastructure (e.g. sidewalks, crosswalks)?
"curb_space_management", -- How satisfied are you with curb space management (e.g. on-street parking, rideshare)?
"city_street_maintenance", -- How satisfied are you with maintenance of city streets (not including interstates like I-440)?
"curbside_recycling", -- How satisfied are you with curbside recycling service?
"historic_preservation_overall", -- How satisfied are you with the overall quality of historic preservation by Metro?
"_1st_priority_service", -- Which FIVE of the city services listed above do you think should receive the MOST EMPHASIS from Metro over the next TWO years? Please list in order of priority (1st = Highest Priority)
"metro_provided_arts_and", -- How satisfied are you with the overall quality of Metro-provided arts amenities, cultural activities, and public art?
"affordable_housing_overall", -- How satisfied are you with the overall quality of affordable housing?
"public_health_overall", -- How satisfied are you with the overall quality public health (including General Hospital)?
"police_overall", -- How satisfied are you with the overall quality of police?
"decision_input_opportunity", -- How satisfied are you with the opportunity to provide input into decisions made by Metro?
"trash_and_recycling_overall", -- How satisfied are you with the overall quality of solid waste services (trash/recycling)?
"fire_and_ems_overall", -- How satisfied are you with the overall quality of fire and emergency medical services (EMS)?
"elected_official_leadership", -- How satisfied are you with the overall quality of leadership from Metro's elected officials?
"elected_official", -- How satisfied are you with the responsiveness of Metro's elected officials?
"metro_services_overall", -- How satisfied are you with the overall quality of services provided by Metro?
"value_received_from_taxes", -- How satisfied are you with the overall value you receive for your local tax dollars and fees?
"metro_budgeting_and", -- How satisfied are you with Metro's budgeting and stewardship of taxpayer dollars?
"customer_service", -- How satisfied are you with the customer service you receive from Metro employees?
"elected_official_accessibility", -- How satisfied are you with the accessibility of Metro's elected officials?
"neighborhood_appearance", -- How satisfied are you with the physical appearance of your neighborhood?
"neighborhood_safety", -- How satisfied are you with how safe you feel in your neighborhood?
"city_appearance", -- How satisfied are you with the overall physical appearance of Davidson County?
"nashville_as_retirement_place", -- Please rate Davidson County as a place to retire/grow older.
"quality_of_life", -- How satisfied are you with the overall quality of life in Davidson County?
"overall_feeling_of_safety", -- How satisfied are you with the overall feeling of safety in Davidson County?
"nashville_as_residence", -- Please rate Davidson County as a place to live.
"public_meeting_attendance", -- Have you or anyone in your household attended a public meeting about Metro business in the last year?
"nashville_as_workplace", -- Please rate Davidson County as a place to work.
"education_attained", -- What is your educational attainment?
"age", -- What is your age?
"total_annual_household_income", -- What would you say your total annual household income is?
"race_ethnicity_native_american", -- [Respondent indicated race/ethnicity of “American Indian or Alaskan Native”]
"race_ethnicity_asian_or", -- [Respondent indicated race/ethnicity of “Asian or Pacific Islander”]
"race_ethnicity_hispanic", -- [Respondent indicated race/ethnicity of “Hispanic”]
"race_ethnicity_black", -- [Respondent indicated race/ethnicity of “Black (Not of Hispanic origin)”]
"race_ethnicity_white", -- [Respondent indicated race/ethnicity of “White (Not of Hispanic origin)”]
"years_lived_in_davidson_county", -- Approximately how many years have you lived in Davidson County?
"own_or_rent", -- Do you own or rent your current residence?
"other_school_attendance", -- [Respondent has children attending another type of school not listed]
"children_agest_13_17", -- Do you have any children ages 14-17 who live in Davidson County?
"emergency_and_natural_disaster", -- How satisfied are you with Metro's ability to respond effectively to emergencies or natural disasters?
"affordable_housing_access", -- How satisfied are you with access to affordable housing?
"farmers_market", -- How satisfied are you with the Nashville Farmers' Market?
"zip_code", -- ZIP code of the respondent
"race_ethnicity_other", -- [Respondent indicated race/ethnicity of “Other”]
"race_ethnicity_other_1", -- [Respondent-reported definition of “Other” race/ethnicity]
"gender_identity", -- What is your gender identity?
"business_owner_operator", -- Do you own or operate your own business?
"public_school_attendance", -- [Respondent has children attending a public school]
"children_agest_6_13", -- Do you have any children ages 6-13 who live in Davidson County?
"school_quality", -- How would you grade the quality of the school(s) your children attend?
"general_hospital", -- How satisfied are you with Nashville General Hospital?
"diversity_acceptance", -- How satisfied are you with openness and acceptance of the community towards people of diverse backgrounds?
"small_business_and_freelance", -- How satisfied are you with availability of affordable live/work space for small biz/freelancers?
"job_access", -- How satisfied are you with access to jobs that pay a living or family supporting wage?
"disability_access", -- How satisfied are you with accessibility of Metro facilities/services for people with disabilities?
"diversity_support_efforts", -- How satisfied are you with Metro's efforts to support diversity by serving people equally regardless of religion, race/ethnicity, age, or ability?
"mobile_phone_internet_access", -- Do you have access to the Internet through a phone?
"home_internet_access", -- Does your household own a device (computer, tablet), other than a phone, to access the Internet?
"natural_disaster_preparedness", -- Do you feel confident that you know what to do during natural disasters, such as floods or tornados?
"_500_emergency_expense", -- Could you afford an emergency expense of $500 or more (e.g., hospital visit, major car/house repair)?
"neighborhood_meeting", -- Have you or anyone in your household attended a neighborhood group meeting in the last year?
"metro_elected_official_contact", -- Have you or anyone in your household contacted a Metro elected official in the last year?
"nashville_gov_usage", -- Have you or anyone in your household visited Metro's website (Nashville.gov) in the last year?
"_311_hubnashville_usage", -- Have you or anyone in your household used 311/hubNashville in the last year?
"arts_cultural_event_attendance", -- Have you or anyone in your household attended an arts/cultural event in Davidson County in the last year?
"public_library_usage", -- Have you or anyone in your household visited a Metro library in the last year?
"mta_bus_service_usage", -- Have you or anyone in your household used MTA bus service in the last year?
"bicycle_usage", -- Have you or anyone in your household ridden a bicycle on city streets or trails in the last year?
"end_date", -- Ending date of the period in which the survey response was received
"start_date", -- Beginning date of the period in which the survey response was received
"crime_victim", -- Were you or anyone in your household the victim of a crime in Davidson County in the last year?
"walking_and_biking_trails", -- How satisfied are you with Metro walking/biking trails?
"trash_service", -- How satisfied are you with trash service?
"private_school_attendance", -- [Respondent has children attending a private school]
"charter_school_attendance", -- [Respondent has children attending a charter school]
"healthy_food_access", -- How satisfied are you with access to affordable healthy food?
"childcare_access", -- How satisfied are you with access to affordable quality childcare?
"pre_k_access", -- How satisfied are you with access to affordable quality Pre-K?
"public_transit_access", -- How satisfied are you with access to public transportation?
"park_and_green_space_access", -- How satisfied are you with your access to a park or green space?
"neighborhood_tree_canopy", -- Is there adequate shade provided by trees in your neighborhood?
"internet_use_for_information", -- When you need information, do you feel you know how to use the Internet to help you?
"healthcare_access", -- How satisfied are you with access to affordable quality health care?
"metro_transparency_and_ethics", -- How satisfied are you with how ethically/transparently Metro conducts business?
"metro_communication", -- How satisfied are you with the overall quality of Metro communication with the public?
"crime_prevention", -- How satisfied are you with Metro's overall efforts to prevent crime?
"streets_and_sidewalks_overall", -- How satisfied are you with the overall quality of maintenance of city streets, sidewalks, and infrastructure?
"public_education", -- How satisfied are you with the overall quality of public education?
"public_library", -- How satisfied are you with the overall quality of the library?
"social_services_overall", -- How satisfied are you with the overall quality of social services (including homeless services)?
"economic_development_overall", -- How satisfied are you with the overall quality of economic development services?
"parks_and_recreation_overall", -- How satisfied are you with the overall quality of parks and recreation?
"code_enforcement_overall", -- How satisfied are you with the overall quality of code enforcement (e.g. property standards, short-term rentals)?
"public_transit_overall", -- How satisfied are you with the overall quality of public transportation?
"water_services_overall", -- How satisfied are you with the overall quality of water/stormwater services?
"_2nd_priority_service", -- Which FIVE of the city services listed above do you think should receive the MOST EMPHASIS from Metro over the next TWO years? Please list in order of priority (1st = Highest Priority)
"_3rd_priority_service", -- Which FIVE of the city services listed above do you think should receive the MOST EMPHASIS from Metro over the next TWO years? Please list in order of priority (1st = Highest Priority)
"_4th_priority_service", -- Which FIVE of the city services listed above do you think should receive the MOST EMPHASIS from Metro over the next TWO years? Please list in order of priority (1st = Highest Priority)
"_5th_priority_service", -- Which FIVE of the city services listed above do you think should receive the MOST EMPHASIS from Metro over the next TWO years? Please list in order of priority (1st = Highest Priority)
"street_signs_and_traffic", -- How satisfied are you with maintenance of street signs and traffic signals?
"bicycle_infrastructure", -- How satisfied are you with on-street bicycle infrastructure (e.g. bike lanes, signage)?
"drop_off_recycling", -- How satisfied are you with drop-off recycling centers?
"police_visibility", -- How satisfied are you with the visibility of police in neighborhoods?
"traffic_enforcement", -- How satisfied are you with enforcement of local traffic laws?
"police_professionalism", -- How satisfied are you with the professionalism of police officers?
"police_response_time", -- How satisfied are you with how quickly police respond to emergencies?
"youth_violence_programs", -- How satisfied are you with programs to address youth violence?
"fire_response_time", -- How satisfied are you with how quickly fire personnel respond to emergencies?
"fire_professionalism", -- How satisfied are you with the professionalism of fire personnel?
"ems_professionalism", -- How satisfied are you with the professionalism of emergency medical personnel?
"music_city_circuit", -- How satisfied are you with Music City Circuit?
"accessride", -- How satisfied are you with AccessRide (door-to-door paratransit)?
"music_city_star", -- How satisfied are you with Music City Star?
"park_and_ride", -- How satisfied are you with Park and Ride locations?
"bus_shelters", -- How satisfied are you with bus shelters?
"trash_and_debris_enforcement", -- How satisfied are you with enforcing the clean-up of trash and debris on private property?
"exterior_maintenance", -- How satisfied are you with enforcing the exterior maintenance of residential buildings?
"codes_responsiveness", -- How satisfied are you with the responsiveness of issues reported to Codes?
"parks_maintenance" -- How satisfied are you with maintenance of Metro parks?
FROM
"nashville-gov/metro-resident-survey-responses-nux7-cwab:latest"."metro_resident_survey_responses"
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 nashville-gov/metro-resident-survey-responses-nux7-cwab 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 nashville-gov/metro-resident-survey-responses-nux7-cwab: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 nashville-gov/metro-resident-survey-responses-nux7-cwabCheckout 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 nashville-gov/metro-resident-survey-responses-nux7-cwab:latestThis will download all the objects for the latest tag of nashville-gov/metro-resident-survey-responses-nux7-cwab 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 nashville-gov/metro-resident-survey-responses-nux7-cwab: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 nashville-gov/metro-resident-survey-responses-nux7-cwab: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, nashville-gov/metro-resident-survey-responses-nux7-cwab is just another Postgres schema.