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
"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_length",
"ordinance_size_of_building_height",
"existing_setbacks_right_side", -- Distance from property line to closest portion of building on the given side of the structure.
"requested_lot_area_dwelling_unit", -- Lot area divided by proposed number of dwelling units.
"ordinance_gross_floor_area_ratio", -- As per Article 5 in Cambridge Zoning Ordinance.
"zoning_ordinance_cited_section_field6", -- Relevant Section of the Board of Zoning Appeal Ordinance
"desc_of_relief_req", -- Relief petitioner is asking the Board to grant.
"applied_online", -- 1 means yes
"variance_board_of_appeals", -- Variance: Violating the requirements of the ordinance.
"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_article_field3", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"special_permit_decision_deadline", -- Date the decision must be filed with City Clerk.
"zoning_use_classification", -- Residential/Commercial/Use
"reason_for_petition_other", -- Reason for petition
"board_of_appeals_special_permit", -- Special Permit: Process where applicant is not violating, but is restricted.
"zoning_district", -- What the property is zoned per Cambridge zoning map.
"variance_hearing_deadline", -- Date by which the Board must hear the case.
"zoning_ordinance_cited_article_field2", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"zoning_ordinance_cited_section_field2", -- Relevant Section of the Board of Zoning Appeal Ordinance
"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.
"location_state",
"location_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.
"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",
"ordinance_no_of_parking_spaces", -- Per Article 5, Cambridge Zoning Ordinance,
"existing_no_of_parking_spaces",
"requested_no_of_dwelling_units", -- Number of existing dwelling units plus additional requested units.
"existing_no_of_dwelling_units", -- Number of existing dwelling units on property.
"existing_usablek_open_space_ratio", -- Ratio of all areas except building foot print, parking & driveway to total lot area.
"existing_size_of_building_height",
"requested_setbacks_left_side", -- Distance from property line to closest portion of building on the given side of the structure.
"ordinance_setbacks_rear", -- Distance from property line to closest portion of building on the given side of the structure.
"requested_setbacks_front", -- 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_section_field7", -- Relevant Section of the Board of Zoning Appeal Ordinance
"existing_size_of_building_width",
"existing_size_of_building_length",
"ordinance_size_of_lot_width", -- As per Article 5, Cambridge Zoning Ordinance
"proposed_use", -- How petitioner is proposing to use the property
"ordinance_size_of_building_length",
"ordinance_lot_area", -- As per Article 5, Cambridge Zoning Ordinance.
"exisiting_lot_area", -- Square footage of lot.
"location_zip",
"requested_setbacks_right_side", -- Distance from property line to closest portion of building on the given side of the structure.
"total_gross_floor_area", -- Total gross floor area before addition.
"location", -- Property in question
"requested_no_of_parking_spaces", -- Existing parking spaces plus additional parking spaces requested.
"variance_decision_deadline", -- Date by which the board must decide on the case
"type", -- Special Permit, Variance, Appeal
"reason_for_petition",
"zoning_ordinance_cited_section_field1", -- Relevant Section of the Board of Zoning Appeal Ordinance
"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",
"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_section_field3", -- 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_field5", -- Relevant Section of the Board of Zoning Appeal Ordinance
"requested_total_gross_flr_area", -- Total gross floor area after addition.
"ordinance_total_gross_flr_area", -- As per article 5 in Cambridge Zoning Ordinance.
"ordinance_setbacks_front", -- 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_setbacks_right_side", -- Distance from property line to closest portion of building on the given side of the structure.
"requested_size_of_building_width",
"ordinance_no_of_dwelling_units",
"decisiondate", -- The date the BZA made the decision.
"ordinance_no_of_loading_areas", -- Per Article 5, Cambridge Zoning Ordinance.
"requested_size_of_lot_depth",
"ordinance_size_of_building_width",
"requested_distance_to_nearest_bldg",
"description", -- Description of requested change to property.
"existing_setbacks_left_side", -- Distance from property line to closest portion of building on the given side of the structure.
"ordinance_setbacks_left_side", -- Distance from property line to closest portion of building on the given side of the structure.
"ordinance_useable_open_space_ratio", -- Per Article 5, Cambridge Zoning Ordinanace
"requested_gross_floor_area_ratio", -- Ratio of gross floor area plus additional gross floor area to lot area
"existing_lot_area_dwelling_unit", -- Lot area divided by existing number of dwelling units.
"ordinance_size_of_lot_depth", -- As per Article 5, Cambridge Zoning Ordinance
"requested_setbacks_back", -- Distance from property line to closest portion of building on the given side of the structure.
"special_permit_hearing_deadline", -- Date by which the Board must hear the case.
"zoning_ordinance_cited_article_field7", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"existing_setbacks_front", -- Distance from property line to closest portion of building on the given side of the structure.
"requested_size_of_building_height",
"petitioner_state",
"current_use", -- How property is being used
"zoning_ordinance_cited_article_field1", -- Board of Zoning Appeal Ordinance
"zoning_ordinance_cited_article_field6", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"existing_size_of_lot_depth",
"literal_enforcement", -- Reason for substantial hardship.
"summary_for_publication", -- Description of request for newspaper publication.
"existing_distance_to_nearest_bldg",
"description_of_other_occupancies", -- Buildings on same lot.
"plan_number", -- Number given at time of application
"applicationdate", -- Date application was filed with the City.
"status", -- Pending Payment, Under Review, Approved, Withdrawn
"location_address",
"petitioner_city",
"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.
"requirements_of_ordinance", -- Requirements of the Ordinance can or will be met.
"requested_no_of_loading_areas", -- Number of existing loading areas plus additional requested loading areas.
"existing_no_of_loading_areas", -- Number of existing loading areas on property.
"ordinance_lot_area_dwelling_unit", -- As per Article 5 in Cambridge Zoning Ordinance.
"existing_gross_floor_area_ratio", -- Ratio of gross floor area of building to lot area.
"requested_lot_area", -- Area of lot after the requested variance
"zoning_ordinance_cited_article_field5" -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
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.