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 board_of_zoning_appeal_requests table in this repository, by referencing it like:
"cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws:latest"."board_of_zoning_appeal_requests"or in a full query, like:
SELECT
":id", -- Socrata column ID
"existing_size_of_building_length",
"ordinance_setbacks_front", -- Distance from property line to closest portion of building on the given side of the structure.
"ordinance_size_of_lot_width", -- As per Article 5, Cambridge Zoning Ordinance
"ordinance_lot_area", -- As per Article 5, Cambridge Zoning Ordinance.
"description", -- Description of requested change to property.
"existing_no_of_parking_spaces",
"existing_usablek_open_space_ratio", -- Ratio of all areas except building foot print, parking & driveway to total lot area.
"land_topography_hardship", -- The hardship is owing to the following circumstances relating to the soil, conditins, shape or topography of such land or structures and especially affecting such land or structures but not affecting generally the zoning district in which it is located.
"zoning_ordinance_cited_article_field2", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"variance_board_of_appeals", -- Variance: Violating the requirements of the ordinance.
"requirements_of_ordinance", -- Requirements of the Ordinance can or will be met.
"requested_distance_to_nearest_bldg",
"existing_no_of_loading_areas", -- Number of existing loading areas on property.
"requested_no_of_dwelling_units", -- Number of existing dwelling units plus additional requested units.
"requested_no_of_loading_areas", -- Number of existing loading areas plus additional requested loading areas.
"other_reasons", -- The proposed use would not impair the integrity of the district or adjoining district or otherwise derogate from the intent or purpose of this ordinance.
"plan_number", -- Number given at time of application
"zoning_ordinance_cited_section_field7", -- Relevant Section of the Board of Zoning Appeal Ordinance
"ordinance_gross_floor_area_ratio", -- As per Article 5 in Cambridge Zoning Ordinance.
"existing_size_of_building_height",
"applied_online", -- 1 means yes
"ordinance_setbacks_rear", -- Distance from property line to closest portion of building on the given side of the structure.
"proposed_use", -- How petitioner is proposing to use the property
"zoning_ordinance_cited_article_field4", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"zoning_ordinance_cited_section_field6", -- Relevant Section of the Board of Zoning Appeal Ordinance
"zoning_ordinance_cited_article_field7", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"total_gross_floor_area", -- Total gross floor area before addition.
"requested_lot_area", -- Area of lot after the requested variance
"existing_lot_area_dwelling_unit", -- Lot area divided by existing number of dwelling units.
"requested_size_of_lot_depth",
"ordinance_useable_open_space_ratio", -- Per Article 5, Cambridge Zoning Ordinanace
"requested_usable_open_space_ratio", -- Ratio of all areas except building foot print, parking & driveway including proposed addition to total lot area.
"requested_size_of_building_width",
"ordinance_size_of_building_length",
"requested_size_of_building_height",
"ordinance_setbacks_left_side", -- Distance from property line to closest portion of building on the given side of the structure.
"existing_size_of_lot_width",
"requested_size_of_lot_width",
"zoning_ordinance_cited_article_field5", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"zoning_district", -- What the property is zoned per Cambridge zoning map.
"location", -- Property in question
"nuisance_or_hazard", -- Nuisance or hazard would not be created to the detriment of the health, safety and/or welfare of the occupant of the proposed use or the citizens of the City.
"continued_operation", -- The continued operation of or the development of adjacent uses as permitted in the Zoning Ordinance would not be adversely affected by the nature of the proposed use.
"existing_distance_to_nearest_bldg",
"ordinance_no_of_loading_areas", -- Per Article 5, Cambridge Zoning Ordinance.
"requested_no_of_parking_spaces", -- Existing parking spaces plus additional parking spaces requested.
"existing_size_of_building_width",
"ordinance_size_of_building_height",
"requested_setbacks_back", -- Distance from property line to closest portion of building on the given side of the structure.
"existing_setbacks_back", -- Distance from property line to closest portion of building on the given side of the structure.
"ordinance_lot_area_dwelling_unit", -- As per Article 5 in Cambridge Zoning Ordinance.
"requested_lot_area_dwelling_unit", -- Lot area divided by proposed number of dwelling units.
"literal_enforcement", -- Reason for substantial hardship.
"description_of_other_occupancies", -- Buildings on same lot.
"requested_size_of_building_length",
"ordinance_size_of_lot_depth", -- As per Article 5, Cambridge Zoning Ordinance
"requested_gross_floor_area_ratio", -- Ratio of gross floor area plus additional gross floor area to lot area
"ordinance_total_gross_flr_area", -- As per article 5 in Cambridge Zoning Ordinance.
"zoning_ordinance_cited_article_field3", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"board_of_appeals_special_permit", -- Special Permit: Process where applicant is not violating, but is restricted.
"requested_setbacks_front", -- Distance from property line to closest portion of building on the given side of the structure.
"location_zip",
"zoning_ordinance_cited_article_field6", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"exisiting_lot_area", -- Square footage of lot.
"status", -- Pending Payment, Under Review, Approved, Withdrawn
"ordinance_setbacks_right_side", -- Distance from property line to closest portion of building on the given side of the structure.
"desirable_relief_public_good", -- Substantial detriment to the public good for the following reason.
"desirable_relief_nullifying", -- Relief may be granted without nullifying or substantially derogating from the intent or purpose of this ordinance.
":@computed_region_e4yd_rwk4", -- This column was automatically created in order to record in what polygon from the dataset 'Census Blocks 2010' (e4yd-rwk4) the point in column 'coordinates' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
":@computed_region_swkg_bavi",
":@computed_region_rffn_qbt6",
":@computed_region_v7jj_366k",
":@computed_region_guic_hr4a",
":@computed_region_rcj3_ccgu",
"desc_of_relief_req", -- Relief petitioner is asking the Board to grant.
"location_state",
"location_city",
"ordinance_no_of_dwelling_units",
"ordinance_no_of_parking_spaces", -- Per Article 5, Cambridge Zoning Ordinance,
"existing_no_of_dwelling_units", -- Number of existing dwelling units on property.
"ordinance_size_of_building_width",
"requested_setbacks_right_side", -- Distance from property line to closest portion of building on the given side of the structure.
"existing_setbacks_right_side", -- Distance from property line to closest portion of building on the given side of the structure.
"requested_setbacks_left_side", -- Distance from property line to closest portion of building on the given side of the structure.
"existing_setbacks_left_side", -- Distance from property line to closest portion of building on the given side of the structure.
"existing_setbacks_front", -- Distance from property line to closest portion of building on the given side of the structure.
"existing_size_of_lot_depth",
"existing_gross_floor_area_ratio", -- Ratio of gross floor area of building to lot area.
"requested_total_gross_flr_area", -- Total gross floor area after addition.
"summary_for_publication", -- Description of request for newspaper publication.
"zoning_ordinance_cited_section_field5", -- Relevant Section of the Board of Zoning Appeal Ordinance
"zoning_ordinance_cited_section_field4", -- Relevant Section of the Board of Zoning Appeal Ordinance
"zoning_ordinance_cited_section_field3", -- Relevant Section of the Board of Zoning Appeal Ordinance
"zoning_ordinance_cited_section_field2", -- Relevant Section of the Board of Zoning Appeal Ordinance
"zoning_ordinance_cited_section_field1", -- Relevant Section of the Board of Zoning Appeal Ordinance
"zoning_ordinance_cited_article_field1", -- Board of Zoning Appeal Ordinance
"variance_decision_deadline", -- Date by which the board must decide on the case
"variance_hearing_deadline", -- Date by which the Board must hear the case.
"special_permit_decision_deadline", -- Date the decision must be filed with City Clerk.
"special_permit_hearing_deadline", -- Date by which the Board must hear the case.
"current_use", -- How property is being used
"zoning_use_classification", -- Residential/Commercial/Use
"reason_for_petition_other", -- Reason for petition
"reason_for_petition",
"location_address",
"type", -- Special Permit, Variance, Appeal
"applicationdate", -- Date application was filed with the City.
"petitioner_state",
"petitioner_city",
"decisiondate", -- The date the BZA made the decision.
"trafficpatterns", -- Traffic generated or patterns of access or egress would not cause congestion hazard, or substantial change in established neighborhood character.
"ordinance_distance_to_nearest_building"
FROM
"cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws:latest"."board_of_zoning_appeal_requests"
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 cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.cambridgema.gov. When you querycambridgema-gov/board-of-zoning-appeal-requests-urfm-usws: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
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.cambridgema.gov, 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 \
"cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws" \
--handler-options '{
"domain": "data.cambridgema.gov",
"tables": {
"board_of_zoning_appeal_requests": "urfm-usws"
}
}'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, cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws is just another Postgres schema.