cityofnewyork-us/internet-master-plan-adoption-and-infrastructure-fg5j-q5nk
Icon for Socrata external plugin

Query the Data Delivery Network

Query the DDN

The 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 procotol. 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 internet_master_plan_adoption_and_infrastructure table in this repository, by referencing it like:

"cityofnewyork-us/internet-master-plan-adoption-and-infrastructure-fg5j-q5nk:latest"."internet_master_plan_adoption_and_infrastructure"

or in a full query, like:

SELECT 
    ":id", -- Socrata column ID
    "city_buildings_with_high", -- Buildings that are owned, operated, or otherwise controlled by the City, or available for City use, that are higher than 6 floors in the NTA.
    "population_projections_2050", -- 2030-2050 Socioeconomic and Demographic (SED) Population Forecasts.
    "number_of_nyc_h_h_buildings", -- Number of Health and Hospitals Corporation facilities in the NTA.
    "number_of_dcas_buildings", -- Number of the buildings managed by the Department of Citywide Administrative Services in the NTA.
    "number_of_public_schools", -- Number of the public school buildings managed by the New York City Department of Education in the NTA.
    "residential_broadband_choice", -- The average number of residential broadband internet service providers reported to be available per census block by neighborhood.
    "mobile_dependent_households_1", -- Percentage of Households with a cellular data plan and no other internet subscription, categorized as either above ('High') or below ('Low') the mean.
    "number_of_nycha_buildings", -- Number of the public housing buildings managed by the New York City Housing Authority in the NTA.
    "presence_of_ibzs", -- Whether or not an Industrial Business Zone is present in the NTA.
    "low_income_housing_nycha", -- Number of individuals in New York City Housing Authority (NYCHA) apartments.
    "supportive_housing_nycdhs", -- Number of individuals for each shelter facility type, at the community district level.
    "reviewed_for_aerial_fiber", -- Field survey considered deployment of this broadband technology.
    "reviewed_for_millimeter_wave", -- Field survey considered deployment of this broadband technology.
    "households_receiving_benefits", -- Public assistance income provides cash payments to poor families or individuals and includes Temporary Assistance to Needy Families (TANF) and General Assistance (GA).
    "mobile_dependent_households", -- Percentage of Households with a cellular data plan and no other internet subscription.
    "mobile_broadband_adoption_1", -- Percentage of Households with a cellular data plan, categorized by quartiles (High, Medium-High, Medium-Low, Low).
    "percentage_of_blocks_without", -- The percentage of census blocks reported to have no commercial fiber internet service providers available by neighborhood.
    "street_furniture_fdny_call", -- Number of FDNY Call Boxes in the NTA.
    "street_furniture_parking", -- Number of parking pay stations in the NTA.
    "percentage_of_blocks_without_1", -- The average number of commercial fiber internet service providers reported to be available per census block by neighborhood and categorized by quartiles (High, Medium-High, Medium-Low, Low).
    "percentage_of_blocks_with", -- The percentage of census blocks in the specified neighborhood which have been reported as having a maximum advertised downstream speed/bandwidth offered by the provider in the block for consumer service of equal to or greater than 25 megabits per second.
    "percentage_of_blocks_with_1", -- The percentage of census blocks in the specified neighborhood which have been reported as having a maximum advertised downstream speed/bandwidth offered by the provider in the block for consumer service of equal to or greater than 900 megabits per second.
    "reviewed_for_underground", -- Field survey considered deployment of this broadband technology.
    "sample_of_nta_field_surveyed", -- Field surveys involved walking a portion of an NTA and recording observations based on how certain broadband technologies could be deployed in those areas.
    "estimated_number_of_radio_1", -- The number of sites needed to provide mmWave coverage within the portion of the NTA projected to be feasible for mmWave-FWA deployment.
    "estimated_number_of_radio", -- The number of sites needed to provide CBRS coverage within the NTA.
    "percentage_optimal_for", -- The percentage of the NTA area assessed as potentially optimal for Unlicensed Millimeter Wave Fixed Wireless Access (mmWave-FWA) as a Residential Broadband Solution
    "optimal_for_unlicensed", -- Whether or not there are areas within the NTA assessed as potentially optimal for Unlicensed Millimeter Wave Fixed Wireless Access (mmWave-FWA) as a Residential Broadband Solution
    "street_furniture_bus_stop", -- Number of bus stop shelters in the NTA.
    "indoor_cabling_for_multiple", -- Estimated relative cost of connecting households within apartment buildings in the neighborhood. Classified as "High", "Medium", and "Low".
    "fiber_drops_estimated_relative", -- Estimated relative cost of connecting premises for fiber-to-the-premises broadband deployment. Classified as "High", "Medium", and "Low".
    "nyc_internet_master_plan_1", -- Infrastructure cost comparison for NYC Internet Master Plan by NTA and grouped into quartiles.
    "nyc_internet_master_plan", -- Infrastructure cost comparison for NYC Internet Master Plan by NTA
    "mobile_broadband_adoption", -- Percentage of Households in the neighborhood with a cellular data plan internet service subscription.
    "utility_pole_make_ready_1", -- Estimated cost Utility Pole Make-Ready. Classified as "High", "Medium", and "Low"
    "utility_pole_make_ready", -- Moving of existing telecom utilities to create space on utility poles and the placement of strand placed between utility poles
    "estimated_underground_utility", -- Estimated volume of  Underground Utility Conflicts. Classified as "High", "Medium", and "Low". 
    "geographic_variations_in", -- The relative delivery mode of fiber, above ground and underground, throughout NYC by NTA.
    "estimated_underground_plant", -- Estimated portion of NTA currently with underground fiber delivery mode.
    "estimated_aerial_plant", -- Estimated portion of NTA currently with aerial fiber delivery mode.
    "street_furniture_bike_shelters", -- Number of bicycle parking shelters in the NTA.
    "street_furniture_city_benches", -- Number of City benches in the NTA. 
    "linknyc_kiosks", -- Locations where a LinkNYC Kiosk has been installed or (planned installations available on LinkNYC website).
    "poles_reserved_by_mobile", -- Poles that the City has reserved for use for the Mobile Telecommunications Franchise, without equipment installed.
    "pole_with_equipment_installed", -- Poles in use for the Mobile Telecom Franchise, with equipment installed.
    "density_of_poles_reserved", -- Total poles, reserved by Mobile Telecom Franchisee with or without  equipment installed, per street mile in the NTA. 
    "home_broadband_adoption_by", -- Percentage of Households with a broadband internet service subscription, categorized by quartiles (High, Medium-High, Medium-Low, Low). The Federal Communications Commission (FCC) sets a standard for “broadband” as an internet service with a download speed of at least 25 megabits per second (Mbps) and an upload speed of at least 3 Mbps. Quartile grouping is as follows:  High, Medium-High, Medium-Low, Low. Data is sourced from the 2017 5 year American Community Survey estimates.
    "home_broadband_adoption", -- Percentage of Households with a broadband internet service subscription by neighborhood. The Federal Communications Commission (FCC) sets a standard for “broadband” as an internet service with a download speed of at least 25 megabits per second (Mbps) and an upload speed of at least 3 Mbps.
    "street_mileage", -- Total length of publicly accessible streets in the NTA presented in miles.
    "area_sq_mi", -- Square miles of the NTA.
    "total_number_of_households", -- The total number of households per NTAs.
    "population_density_per_sq", -- A ratio of the total number of persons per square mile.
    "total_population", -- The total number of persons based on 5 year American Community Survey estimates.
    "borough_name", -- The Borough of New York City where the attribute or asset is located in or referencing.
    "neighborhood_tabulation_area_1", -- Neighborhood name identifiers to differentiate NYC's five boroughs.
    "mobile_telecom_franchise", -- Total poles, reserved by Mobile Telecom Franchisee with or without  equipment installed, per street mile in the NTA. Classified as "Low", "Medium", or "High".
    "empire_city_subway_coverage", -- Total street coverage of conduits by NTA. Described as the ratio of ECS total mileage and the total street mileage in each NTA. Multiple conduits along the same route are not double counted in total mileage.
    "estimated_lifeline_eligible", -- Estimate of number of households eligible for the Lifeline Program, a federal program that lowers the monthly cost of phone and internet. 
    "percentage_of_households", -- The percentage of households in the NTA in census blocks that are shown as having fewer than three ISPs available with service options that meet the federal broadband standard.
    "neighborhood_tabulation_area", -- Four (4) digit code assigned to neighborhoods to differentiate NYC's five boroughs.
    "field_survey_findings", -- The key findings from field survey.
    "number_of_fixed_wireless", -- Number of Fixed Wireless providers, as reported to be operating in a given census block (which are aggregated to give NTA numbers)
    "empire_city_subway_coverage_1", -- ECS coverage in quartiles except the areas with no ECS routing.
    "commercial_fiber_max_isp_1", -- The maximum number of commercial fiber internet service providers reported to be available per census block by neighborhood and categorized by quartiles (High, Medium-High, Medium-Low, Low).
    "commercial_fiber_max_isp", -- The maximum number of commercial fiber internet service providers reported to be available per census block by neighborhood.
    "commercial_fiber_choice", -- The average number of commercial fiber internet service providers reported to be available per census block by neighborhood and categorized by quartiles (High, Medium-High, Medium-Low, Low).
    "commercial_fiber_isp_choice", -- The average number of commercial fiber internet service providers reported to be available per census block by neighborhood.
    "residential_broadband_choice_1", -- The average number of residential broadband internet service providers reported to be available per census block by neighborhood and categorized by quartile groups (High, Medium-High, Medium-Low, Low).
    "number_of_public_computer", -- Indicates the number of public computer centers that offer public Wi-Fi  within the specified NTA.
    "public_wi_fi_in_nyc_count", -- Indicates the number of public Wi-Fi access points within the specified NTA.
    "number_of_free_public_wi", -- Indicates the number of public Wi-Fi access points in a pedestrian corridor within the specified NTA.
    "available_free_public_wi", -- Indicates if a public Wi-Fi access point or points are available in a pedestrian corridor within the specified NTA.
    "estimated_number_of_businesses", -- Total Number of Businesses.
    "number_of_households", -- Total Number of Households.
    "total_accounts", -- Total Number of Business and Residential Accounts.
    "account_density", -- Total Accounts per unit of Street Mileage.
    "account_density_quartile" -- NTAs categorized based on the account density quartiles.
FROM
    "cityofnewyork-us/internet-master-plan-adoption-and-infrastructure-fg5j-q5nk:latest"."internet_master_plan_adoption_and_infrastructure"
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 cityofnewyork-us/internet-master-plan-adoption-and-infrastructure-fg5j-q5nk with SQL in under 60 seconds.

This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.cityofnewyork.us. When you querycityofnewyork-us/internet-master-plan-adoption-and-infrastructure-fg5j-q5nk: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)"
 

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.cityofnewyork.us, 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 \
  "cityofnewyork-us/internet-master-plan-adoption-and-infrastructure-fg5j-q5nk" \
  --handler-options '{
    "domain": "data.cityofnewyork.us",
    "tables": {
        "internet_master_plan_adoption_and_infrastructure": "fg5j-q5nk"
    }
}'

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, cityofnewyork-us/internet-master-plan-adoption-and-infrastructure-fg5j-q5nk is just another Postgres schema.

Related Documentation: