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
"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
"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
"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
"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",
"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]
"browsertype",
"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
"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
"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
"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
"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
"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
"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_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
"invite_to_join",
"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_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_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
"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
"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
"d1_male", -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: Male
"d1_two_spirit", -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: Two-Spirit
"d1_non_binary", -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: Non-binary
"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
"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]
"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
"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
"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.
"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
"devicetype",
"ostype",
"plugintype_flash",
"pointsearned",
"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
"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
"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_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
"respondentlocale",
"version",
"activityid",
"completiontime",
"browserexperience_javascript_enabled",
"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
"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]"
"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
"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_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.
"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
"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]
"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.
"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
"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]
"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_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
"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
"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
"browserexperience_user_agent",
"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]
"plugintype_no_detectable_plugin",
"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_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
"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
"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
"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
"respondentsource_icm_power_surveys",
"responsestatus",
"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
"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
"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
"d1_transgender", -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: Transgender
"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
"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
"index",
"invitationdate",
"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
"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_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_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
"responsedate",
"starteddate",
"completeddate",
"q1", -- Respondents were asked, "Have you used public transit in Edmonton in the past three months?". Possible answers were: Yes, No
"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
"d1_female" -- All respondents were asked, "What gender do you identify as?" Choosing all that apply: Female
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.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.edmonton.ca. When you queryedmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7:latest on the DDN, we "mount" the repository using the socrata mount handler. The mount handler proxies your SQL query to the upstream data source, translating it from SQL to the relevant language (in this case SoQL).
We also cache query responses on the DDN, but we run the DDN on multiple nodes so a CACHE_HIT is only guaranteed for subsequent queries that land on the same node.
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 (like this repository), 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, where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.
Mounting Data
This repository is an external repository. It's not hosted by Splitgraph. It is hosted by data.edmonton.ca, and Splitgraph indexes it. This means it is not an actual Splitgraph image, so you cannot use sgr clone to get the data. Instead, you can use the socrata adapter with the sgr mount command. Then, if you want, you can import the data and turn it into a Splitgraph image that others can clone.
First, install Splitgraph if you haven't already.
Mount the table with sgr mount
sgr mount socrata \
"edmonton-ca/transit-ridership-and-public-health-measures-um86-qcr7" \
--handler-options '{
"domain": "data.edmonton.ca",
"tables": {
"transit_ridership_and_public_health_measures": "um86-qcr7"
}
}'That's it! Now you can query the data in the mounted table like any other Postgres table.
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.