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 transit_ridership_and_public_health_measures table in this repository, by referencing it like:
"edmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7:latest"."transit_ridership_and_public_health_measures"or in a full query, like:
SELECT
":id", -- Socrata column ID
"browserexperience_user_agent",
"plugintype_no_detectable_plugin",
"respondentsource_icm_power_surveys",
"invite_to_join",
"d6", -- All respondents were asked, "Which best describes your age?" Chosing one response, possible answers were: Less than 18 years, 18 - 24 years, 25 - 34 years, 35 - 44 years, 45 - 54 years, 55 - 64 years, 65 years or older, Other (please specify), Prefer not to answer. NB: Open text responses are not reported.
"d5", -- All respondents were asked, "Are you currently attending school?" Selecting one response, possible answers were: Not currently a student, Yes, High school student; Yes, Full time postsecondary student; Yes, Part time postsecondary student; Yes, Student in vocational college; Prefer not to answer
"d4", -- All respondents were asked, "In terms of employment (including self-employment), how would you describe yourself?". Selecting one response, possible answers were: Employed full time (30 hours or more per week), Employed part time (less than 30 hours per week), Not employed, Unable to work, Stay at home parent, Retired, Prefer not to answer
"d2_prefer_not_to_answer", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: Prefer not to answer [exclusive]
"d2_my_only_means_of_transportation", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: My only means of transportation
"d1_non_binary", -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: Non-binary
"d1_two_spirit", -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: Two-Spirit
"d1_male", -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: Male
"q13", -- Excluded from this question were those who answered "No" to Q9 and "None, I don't have any concerns" in Q10, all others were then asked, "If masks continue to be required, but ALL other public health measures such as isolation and quarantine requirements are lifted, which of the following statements best describes your anticipated transit usage. My transit usage would ..." Selecting one response, possible answers were: I would continue to start using public transit, I would continue to start using public transit but I would use it less frequently, I would not start using public transit, Other (Please specify). NB: Open text responses are not reported
"q11", -- Excluded from this question were those who answered "No" to Q9 and "None, I don't have any concerns" in Q10, all others were then asked, "If masks are no longer required on transit vehicles (buses and trains) and other health measures such as isolation and quarantine are no longer required, which of the following statements best describes your perception of safety on transit as it relates to COVID-19?" Selecting one choice, possible answers were: I would feel safe, I would feel safe if I am wearing a mask, I would feel safe when I am able to remain physically distanced from others, I would feel safe if I am wearing a mask and am able to remain physically distanced from others, I would not feel safe, Unsure
"q10_others_displaying_physical_symptoms_of_sickness", -- For those who answered "No" in Q1, respondents were then asked, "When you think about your future use of transit, what do you think would impact your sense of safety on transit as it relates to COVID-19?" Choosing all that apply: Others displaying physical symptoms of sickness
"q10_lack_of_communication_about_covid_19_protocols_and_transit", -- For those who answered "No" in Q1, respondents were then asked, "When you think about your future use of transit, what do you think would impact your sense of safety on transit as it relates to COVID-19?" Choosing all that apply: Lack of communication about COVID-19 protocols and transit
"q10_lack_of_ability_to_remain_physically_distanced_from_others", -- For those who answered "No" in Q1, respondents were then asked, "When you think about your future use of transit, what do you think would impact your sense of safety on transit as it relates to COVID-19?" Choosing all that apply: Lack of ability to remain physically distanced from others
"q10_i_feel_that_others_will_not_keep_their_physical_distance", -- For those who answered "No" in Q1, respondents were then asked, "When you think about your future use of transit, what do you think would impact your sense of safety on transit as it relates to COVID-19?" Choosing all that apply: I feel that others will not keep their physical distance
"q10_drivers_not_wearing_masks", -- For those who answered "No" in Q1, respondents were then asked, "When you think about your future use of transit, what do you think would impact your sense of safety on transit as it relates to COVID-19?" Choosing all that apply: Drivers not wearing masks
"q10_lack_of_enforcement_of_mask_bylaw", -- For those who answered "No" in Q1, respondents were then asked, "When you think about your future use of transit, what do you think would impact your sense of safety on transit as it relates to COVID-19?" Choosing all that apply: Lack of enforcement of mask bylaw
"q6", -- For those who answered "Yes" in Q1, respondents were asked, "If masks are no longer required on transit vehicles (buses and trains), would you personally still continue to wear a mask for the foreseeable future?" Selecting one option, possible answers were: Yes, No, Unsure
"q5", -- For those who answered "Yes" in Q1, respondents were asked, "If all public health measures listed in the previous question (isolation, quarantine and mask requirements) are lifted, which of the following statements best describes your anticipated transit usage. My transit usage would ..." Selecting one option, possible answers were: Increase, Stay the same, Decrease, I would no longer use transit
"q4", -- For those who answered "Yes" in Q1, respondents were asked, "If masks are no longer required on transit vehicles (buses and trains) and other health measures such as isolation and quarantine are no longer required, which of the following statements best describes your perception of safety on transit as it relates to COVID-19?" Seleting one option, possible answers were: I would feel safe, I would feel safe if I am wearing a mask, I would feel safe when I am able to remain physically distanced from others, I would feel safe if I am wearing a mask and am able to remain physically distanced from others, I would not feel safe, Unsure
"q3_concerns_about_cleanliness_sanitization_of_vehicles_stops_an", -- For those who answered "Yes" in Q1, respondents were asked, "Which of the following, if any, impact your feelings of safety during your ETS trip when considering COVID-19?" Selecting all that apply: Concerns about cleanliness/sanitization of vehicles, stops, and/or stations
"q3_i_felt_that_others_were_not_keeping_their_physical_distance", -- For those who answered "Yes" in Q1, respondents were asked, "Which of the following, if any, impact your feelings of safety during your ETS trip when considering COVID-19?" Selecting all that apply: I felt that others were not keeping their physical distance
"q3_lack_of_ability_to_remain_physically_distanced_from_others", -- For those who answered "Yes" in Q1, respondents were asked, "Which of the following, if any, impact your feelings of safety during your ETS trip when considering COVID-19?" Selecting all that apply: Lack of ability to remain physically distanced from others
"q2_yes_waiting_to_board_my_first_transit_vehicle", -- For those who answered "Yes" in Q1, respondents were then asked, "Thinking about your most recent trip with ETS, did you feel concerned about safety from COVID-19 at any time?" Selecting all that apply: Yes, waiting to board my first transit vehicle
"q2_yes_travelling_to_my_final_destination", -- For those who answered "Yes" in Q1, respondents were then asked, "Thinking about your most recent trip with ETS, did you feel concerned about safety from COVID-19 at any time?" Selecting all that apply: Yes, travelling to my final destination
"q1", -- Respondents were asked, "Have you used public transit in Edmonton in the past three months?". Possible answers were: Yes, No
"completeddate",
"starteddate",
"responsedate",
"invitationdate",
"index",
"d3", -- All respondents were asked, "Indicate which of the following represents your total household income per year before taxes." Selecting one response, possible answers were: Under $20,000, $20,000 to under $29,999, $30,000 to under $39,999, $40,000 to under $49,999, $50,000 to under $59,999, $60,000 to under $74,999, $75,000 to under $99,999, $100,000 to under $149,999, $150,000 or more, Prefer not to answer
"d2_eliminates_the_need_to_pay_for_parking", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: Eliminates the need to pay for parking
"d1_transgender", -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: Transgender
"q10_others_not_wearing_a_face_covering_or_mask", -- For those who answered "No" in Q1, respondents were then asked, "When you think about your future use of transit, what do you think would impact your sense of safety on transit as it relates to COVID-19?" Choosing all that apply: Others not wearing a face covering or mask
"q2_yes_when_exiting_the_transit_vehicle", -- For those who answered "Yes" in Q1, respondents were then asked, "Thinking about your most recent trip with ETS, did you feel concerned about safety from COVID-19 at any time?" Selecting all that apply: Yes, when exiting the transit vehicle
"q2_yes_while_on_board", -- For those who answered "Yes" in Q1, respondents were then asked, "Thinking about your most recent trip with ETS, did you feel concerned about safety from COVID-19 at any time?" Selecting all that apply: Yes, while on-board
"responsestatus",
"q9", -- For those who answered "No" in Q1, respondents were then asked, "Do you intend to start using transit between now and the end of 2021?" Possible answers were: Yes, No, Unsure
"q8", -- For those who answered "Yes" in Q1, respondents were then asked, "Would you feel safer if masks were required on transit until at least the end of 2021?" Selecting one option, possible answers were: Yes, No, Unsure
"q7", -- For those who answered "Yes" in Q1, respondents were then asked, "If masks continue to be required, but ALL other public health measures such as isolation and quarantine requirements are lifted, which of the following statements best describes your anticipated transit usage. My transit usage would ..." Selecting one option, possible answers were: Increase, Stay the same, Decrease, I would no longer use transit
"q3_others_displaying_physical_symptoms_of_sickness", -- For those who answered "Yes" in Q1, respondents were asked, "Which of the following, if any, impact your feelings of safety during your ETS trip when considering COVID-19?" Selecting all that apply: Others displaying physical symptoms of sickness
"q3_other_please_specify", -- For those who answered "Yes" in Q1, respondents were asked, "Which of the following, if any, impact your feelings of safety during your ETS trip when considering COVID-19?" Selecting all that apply: Other (Please specify) NB: Open text responses are not reported.
"q3_none", -- For those who answered "Yes" in Q1, respondents were asked, "Which of the following, if any, impact your feelings of safety during your ETS trip when considering COVID-19?" Selecting all that apply: None [exclusive]
"q3_lack_of_enforcement_of_mask_bylaw", -- For those who answered "Yes" in Q1, respondents were asked, "Which of the following, if any, impact your feelings of safety during your ETS trip when considering COVID-19?" Selecting all that apply: Lack of enforcement of mask bylaw
"q3_lack_of_communication_about_covid_19_protocols_and_transit", -- For those who answered "Yes" in Q1, respondents were asked, "Which of the following, if any, impact your feelings of safety during your ETS trip when considering COVID-19?" Selecting all that apply: Lack of communication about COVID-19 protocols and transit
"q2_yes_travelling_to_my_first_stop_station", -- For those who answered "Yes" in Q1, respondents were then asked, "Thinking about your most recent trip with ETS, did you feel concerned about safety from COVID-19 at any time?" Selecting all that apply: Yes, travelling to my first stop/station
"q2_yes_during_a_transfer_between_two_different_transit_vehicles", -- For those who answered "Yes" in Q1, respondents were then asked, "Thinking about your most recent trip with ETS, did you feel concerned about safety from COVID-19 at any time?" Selecting all that apply: Yes, during a transfer between two different transit vehicles
"q2_no_i_did_not_feel_concerned_about_covid_19_during_any_part_o", -- For those who answered "Yes" in Q1, respondents were then asked, "Thinking about your most recent trip with ETS, did you feel concerned about safety from COVID-19 at any time?" Selecting all that apply: No, I did not feel concerned about COVID-19 during any part of the trip [exclusive]
"q14", -- Excluded from this question were those who answered "No" to Q9 and "None, I don't have any concerns" in Q10, all others were then asked, "Would you feel safer if masks were required on transit until at least the end of 2021?" Selecting one response, possible answers were: Yes, No, Unsure
"q12", -- Excluded from this question were those who answered "No" to Q9 and "None, I don't have any concerns" in Q10, all others were then asked, "If all public health measures listed in the previous question (isolation, quarantine and mask requirements) are lifted, which of the following statements best describes your anticipated transit usage." Selecting one choice, possible answers were: I would continue with my plan to start using public transit, I would continue with my plan to start using public transit but I would use it less frequently, I would not start using public transit, Other (Please specify). NB: Open text responses are not reported.
"q10_none_i_don_t_have_any_concerns", -- For those who answered "No" in Q1, respondents were then asked, "When you think about your future use of transit, what do you think would impact your sense of safety on transit as it relates to COVID-19?" Choosing all that apply: None, I don’t have any concerns [exclusive]
"q10_crowded_buses_lrt_vehicles", -- For those who answered "No" in Q1, respondents were then asked, "When you think about your future use of transit, what do you think would impact your sense of safety on transit as it relates to COVID-19?" Choosing all that apply: Crowded buses/LRT vehicles
"d2_other_please_specify", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: Other (Please specify) NB: Open text responses are not reported.
"d2_i_do_not_use_ets", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: I do not use ETS [exclusive]
"d2_allows_me_to_be_productive_while_commuting", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: Allows me to be productive while commuting
"d1_i_prefer_not_to_answer", -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: I prefer not to answer [exclusive]"
"d1_another_gender_not_listed_above", -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: Another gender not listed above
"browserexperience_javascript_enabled",
"completiontime",
"activityid",
"version",
"respondentlocale",
"q3_others_not_wearing_a_face_covering_or_mask", -- For those who answered "Yes" in Q1, respondents were asked, "Which of the following, if any, impact your feelings of safety during your ETS trip when considering COVID-19?" Selecting all that apply: Others not wearing a face covering or mask
"q3_drivers_not_wearing_masks", -- For those who answered "Yes" in Q1, respondents were asked, "Which of the following, if any, impact your feelings of safety during your ETS trip when considering COVID-19?" Selecting all that apply: Drivers not wearing masks
"q3_crowded_buses_lrt_vehicles", -- For those who answered "Yes" in Q1, respondents were asked, "Which of the following, if any, impact your feelings of safety during your ETS trip when considering COVID-19?" Selecting all that apply: Crowded buses/LRT vehicles
"q10_concerns_about_cleanliness_sanitization_of_vehicles_stops_a", -- For those who answered "No" in Q1, respondents were then asked, "When you think about your future use of transit, what do you think would impact your sense of safety on transit as it relates to COVID-19?" Choosing all that apply: Concerns about cleanliness/sanitization of vehicles, stops, and/or stations
"pointsearned",
"plugintype_flash",
"ostype",
"devicetype",
"d2_good_for_the_environment", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: Good for the environment
"d2_affordable", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: Affordable
"browserversion",
"browsertype",
"q2_don_t_know_not_sure", -- For those who answered "Yes" in Q1, respondents were then asked, "Thinking about your most recent trip with ETS, did you feel concerned about safety from COVID-19 at any time?" Selecting all that apply: Don’t know/not sure [exclusive]
"d2_reliable", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: Reliable
"d1_female", -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: Female
"d2_safer_than_driving", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: Safer than driving
"d2_convenient", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: Convenient
"d2_a_comfortable_means_to_get_to_my_destination", -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: A comfortable means to get to my destination
"d2_quicker_than_driving" -- All respondents were asked, "What were the main reason(s) you chose or would choose ETS for your trips? ETS is..." Choosing all that apply: Quicker than driving
FROM
"edmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7:latest"."transit_ridership_and_public_health_measures"
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 edmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7 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 edmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7: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 edmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7Checkout 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 edmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7:latestThis will download all the objects for the latest tag of edmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7 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 edmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7: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 edmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7: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, edmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7 is just another Postgres schema.